Oracle Database Discussions

Is it possible to acquire a row-level share lock, which would do all of the following?
1. Prevent others from updating that row.
2. Allow others to read that row.
3. Allow others to update other rows in the same table.


I have the following scenario where two transactions need to lock eachother out:

Set-up:
Insert into TABLE_A(value_a) values ('ok');
Insert into TABLE_B(value_b) values ('ok');

Transaction A:
Select value_b from TABLE_B
If value_b = 'ok', update TABLE_A set value_a = 'not ok'

Transaction B:
Select value_a from TABLE_A
If value_a = 'ok', update TABLE_B set value_b = 'not ok'

If transaction A runs first then the end result is "not ok" only in TABLE_A.
If transaction B runs first then the end result is "not ok" only in TABLE_B.
If the two transactions run concurrently, it is possible to get "not ok" in both tables. This is what I would like to prevent.


One way to get what I want is to use "select for update":

Transaction A:
Select value_a from TABLE_A for update
Select value_b from TABLE_B for update
If value_b = 'ok', update TABLE_A set value_a = 'not ok'

Transaction B:
Select value_a from TABLE_A for update
Select value_b from TABLE_B for update
If value_b = 'ok', update TABLE_B set value_a = 'not ok'

This way both transactions won't perform their update unless they know that the result if their select will still be the same after they commit. However, by using "select for update" Transaction A has gained an exclusive lock on the TABLE_B row. If a Transaction C with the same contents as Transaction A happens concurrently, then the two will block eachother even though all they both want is to read data from the same table.

Another way is to use "lock table", however using that would block out not only writes to a specific row, but writes to all rows in the table. (In my example there is only one row, but obviously that's just a simplified example.)

I have looked at the "serializable" isolation level, but that doesn't seem to help because the queries and updates involve more than one table.


I know that "reads don't block writes" is a fundamental part of the Oracle design that makes Oracle what it is, but is there any way I can explicitly make it happen anyway? Or can anyone see some other solution to what I'm trying to achieve?