Non blocking updates - Part II

In the previous blogpost we saw about non blocking updates introduces in Oracle 23c, now lets explore what’s going on with this feature
 
To get this non-blocking updates to work, we have this new syntax in Oracle 23c to define the column involved in concurrent updates (column Y) as “reservable”
 
alter table t modify y reservable;
 
“reservable” is there to tell the database that rather than locking the rows/data immediately -when the transaction begins- we would like to lock the row/data at some point in time in future.
 
Lets now repeat the demo – first session do this
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> create table t(x int primary key, y number reservable);
demo@FREEPDB1> insert into t(x,y) values(1,1);
demo@FREEPDB1> commit;
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          1
 
I am going to run this update from session #1
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1>
 
from session #2 do a similar update
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             61
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          1
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1>
 
this session also updates the same row as the session #1 and it went fine without any blocking, lets continue and commit changes in session #2 and view the changes
 
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
demo@FREEPDB1>
 
we can see that the value of the column Y got incremented by one – due to the update command – now go back to session #1 and do commit and see the changes.
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          3
 
demo@FREEPDB1>
 
we can see these all can work, so the real question is how this works? Do we have multiple lock on the same row? No not really. If we look into the data dictionary after a column defined as RESERVABLE, an interesting internal Journal table created by the database will be created to keep track of changes to that reservable columns.
 
demo@FREEPDB1> select table_name
  2  from user_tables
  3  where table_name like '%'||(
  4     select object_id
  5     from user_objects
  6     where object_name ='T'
  7     and object_type ='TABLE') ;
 
TABLE_NAME
------------------------------
SYS_RESERVJRNL_118070
 
This journal table is like a Queue table, if any database transactions are there to update the “reservable” column in the table, let’s not update those values instead put all these changes into the queue table and when commit happens, we will reply the changes in order from the queue to make sure we now serialize the changes without locking issues. If we describe this journal table, here is the list of columns it has
 
demo@FREEPDB1> desc SYS_RESERVJRNL_118070
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 ORA_SAGA_ID$                                 RAW(16)
 ORA_TXN_ID$                                  RAW(8)
 ORA_STATUS$                                  CHAR(12)
 ORA_STMT_TYPE$                               CHAR(16)
 X                                   NOT NULL NUMBER(38)
 Y_OP                                         CHAR(7)
 Y_RESERVED                                   NUMBER
 
To see what is going on with these journal table, lets do some transaction and look into the entries of these journal tables.
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1> select * from SYS_RESERVJRNL_118070 ;
 
ORA_SAGA_ID$   ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$    X Y_OP Y_RESERVED
-------------- ---------------- ------------ ---------------- -- ---- ----------
               030020004B060000 ACTIVE       UPDATE            1 +             1
 
demo@FREEPDB1>             
 
we haven’t committed those transaction, but what happens is we didn’t updated those values or even locked them, instead we took those changes and got inserted into Journal tables. So when we commit we go through this Journal table and we roll these changes to the real tables, and that is how it works.
 
So when we say multiple transactions locking the same row in Oracle 23c onwards it means we are really deferring the locks until commit time and roll them in sensible order, so it not really a concurrent locks, instead this feature was a kind of deferrable locks and replay them from a journal table and apply them in correct order.