How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?
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.

&lt;&lt;do_tr3quart1sta_thing&gt;&gt; declare
   totqty number;
   lock_nowait_exception exception;
   pragma exception_init (lock_nowait_exception, -54);
begin

   &lt;&lt;try_for_lock&gt;&gt; loop
       begin
           select sum (qty) into totqty
           from instores where prodid = 'p1'
           FOR UPDATE OF qty;
           exit try_for_lock if sql%rowcount &gt; 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 &gt; 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!