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
Lock a table row while reading it
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
Re: Lock a table row while reading it
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
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
In theory there's no difference between theory and practice. In practice there is.