How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

Question

I have the following table:

InStores
+--------+---------+-----+
| ProdID | StoreID | qty |
+--------+---------+-----+
| p1     | s1      |  25 |
| p1     | s2      |  70 |
+--------+---------+-----+

and two transactions, T1 and T2.

First comes T1:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

T1 sees the sum of 95 and substracts 30 from p1, s2 (but doesn't commit yet).

T2 makes exactly the same:

declare s number(10);

begin
 SELECT SUM(qty) into s
 FROM instores
 WHERE prodID = 'p1';

 if s > 90 then
    update instores
    set qty = qty - 30
    where prodid = 'p1'
    and storeid = 's2';
 end if;
end;

which also sees the sum of 95, as T1 hasn't commited yet and also doesn't update yet, as there is a Write Lock from T1.

Now T1 commits and T2 is now able to continue and substracts the value of 30 from p1, s2.

We end up having the value 10 for p1, s2 instead of the expected value of 40, as T2 should have seen the sum of 65 (because of the first update (substraction) from T1) and shouldn't have made that second subtraction.

I know that Oracle SQL works with Snapshot Isolation, which forces all transactions to work with the latest commited values, but how can this problem be fixed? I guess that the solution would be that when T1 is active it should prevent all other transactions from starting, but I don't exactly know how I can achieve that. What can I do?

Answer 1

You're not quite right in what you expect.

When T2 issues its UPDATE, it has the sum, 95, in its local variable 's'. That is, the T2 query is complete and the T2 'if' statement happens before T1 commits and because the sum is greater than 90 it proceeds to the update.

There is no reason why the local variable 's' would be updated to track the T1 commit.

I think that you should experiment with using SELECT FOR UPDATE NOWAIT in a loop, e.g.

<<do_tr3quart1sta_thing>> declare
   totqty number;
   lock_nowait_exception exception;
   pragma exception_init (lock_nowait_exception, -54);
begin

   <<try_for_lock>> loop
       begin
           select sum (qty) into totqty
           from instores where prodid = 'p1'
           FOR UPDATE OF qty;
           exit try_for_lock if sql%rowcount > 0;
       exception
           when lock_nowait_exception then
               exit try_for_lock;
       end;
       dbms_lock.sleep (2); -- for heaven's sake please don't run these in a tight spin!
   end loop try_for_lock;

   if totqty > 90
   then
       update instores
       set qty = qty - 30
       where prodid = 'p1'
         and storeid = 's2';
   end if;
   commit; -- release the lock!
end do_tr3quart1sta_thing;
/

Don't have experimentation time - I think the NOWAIT will raise exception -54 if it's locked, but in my usual belt-and-suspenders way I'm allowing for the possibility that the SELECT FOR UPDATE will just return zero rows.

Also note that this will let any other exception raised bubble through and blast out.

How in the world can you get T2 to block in this case? You could run this thing hundreds of thousands of times a second!