Lock a table row while reading it

[sumitgulati]

Hi All,

I have an Oracle table A that has only one row.

I have the following requirement:

1) Read table A and get the value of J_ID from it.
2) Increment the value by 1 and update it back to J_ID in table A.

Now, I want the moment I read the J_ID value from the table the row should get locked and no other process should be able to read the value until I update the incremented value back into the table.

Is there any way I can do a row lock OR table lock on table A from DataStage?

Thanks and Regards,
-Sumit

[chulett]

If you are going to take that approach, you would be much better off using a Sequence object instead. Simple to implement and it avoids any concurrency issues.

[sumitgulati]

Thanks Craig, right now I am using DataStage sequence as a work around. I guess I will have to convince the DBA to allow us to create a sequence.

I still would like to know if there is any way to issue a table lock from a DataStage job.

Thanks and Regards,
-Sumit

[ogmios]

For the original question: in Oracle its easy, in DataStage not so.

Oracle uses data versioning. So if you select your row "for update" other processes will still be able to read the value but they will hang if they also try to do a "select for update". The original process could update the row + 1 and commit and the next hanging process could continue, ...

The bad news: in DataStage every stage uses a seperate connection, so if you would do a "select for update" on the input side you would not be able to update the row on output as another connection has the row locked (your own input).

In BCI/BASIC you could get away with it.

Ogmios