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.

Non blocking updates - Part I

The database uses locks to ensure that, at most, one transaction is modifying a given piece of data at any given time. Basically, locks are the mechanism that allows for concurrency. Blocking occurs when one session holds a lock on a resource that another session is requesting. As a result, the requesting session will be blocked—it will hang until the holding session gives up the locked resource.
 
In this blog post series we will see how that blocking feature got changed in Oracle 23c, and what kind of business application/needs can benefit from this change.
 
From an Oracle 21c database, I create a table and executed dml like this
 
demo@PDB1> create table t( x int primary key, y number );
demo@PDB1> insert into t(x,y) values(1,1);
demo@PDB1> commit;
demo@PDB1> update t set y = y+1 where x =1;
 
1 row updated.
 
The above transaction was not committed yet.
 
demo@PDB1> select k.*, u.object_name, l.xidusn,l.xidslot,l.xidsqn
  2  from v$lock k,
  3      user_objects u , v$locked_object l
  4  where k.id1 = u.object_id (+)
  5  and u.object_id = l.object_id (+)
  6  and k.sid = l.session_id (+)
  7  and k.sid = 620 ;
 
SID TY     ID1        ID2  LMODE REQUEST   BLOCK CON_ID OBJECT_NAM  XIDUSN    XIDSLOT     XIDSQN
--- -- ------- ---------- ------ ------- ------- ------ ---------- ------- ---------- ----------
620 TM  102210          0      3       0       0      3 T                5         21       7879
620 AE     134 3319336658      4       0       0      0
620 UL       1          0      4       0       0      3
620 TX  327701       7879      6       0       0      3
 
So we ended up with a Transaction locks (TX) – a TX lock is acquired when a transaction initiates its first change. That lock is held until the transaction perform a commit/rollback, and DML Enqueue (TM) are used to ensure that the structure of a table is not altered while you are modifying its contents. we get only one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DML-locked object, so it is easy to find the object on which the lock is being held.
 
AE is an edition lock, available in Oracle 11g and above and it is part of Edition based redefinition feature.
 
Now running this update from second session – updating the same row from same table - got blocked like this
 
demo@PDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             14
 
demo@PDB1> update t set y = y+1 where x =1;
 
A quick query on v$lock show this
 
demo@PDB1> select * from v$lock where sid in (14,620) order by sid;
 
  SID TY      ID1        ID2   LMODE  REQUEST    BLOCK  CON_ID
----- -- -------- ---------- ------- -------- -------- -------
   14 UL        1          0       4        0        0       3
   14 AE      134 3319336658       4        0        0       0
   14 TX   327701       7879       0        6        0       3
   14 TM   102210          0       3        0        0       3
  620 AE      134 3319336658       4        0        0       0
  620 UL        1          0       4        0        0       3
  620 TM   102210          0       3        0        0       3
  620 TX   327701       7879       6        0        1       3
 
8 rows selected.
 
We can see that the second session (session id = 14) request the Transaction lock, which was held by first session (session id 620) and that is blocking the other session.
 
However, running the same script to Oracle database 23c goes like this
 
demo@FREEPDB1> /* From session-1 did this */
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1            295
 
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> select * from v$lock where sid = 295 ;
 
   SID TY        ID1        ID2      LMODE    REQUEST   BLOCK     CON_ID
------ -- ---------- ---------- ---------- ---------- ------- ----------
   295 TX     262163       1360          6          0       0          3
   295 TM     117538          0          3          0       0          3
   295 TM     117537          0          3          0       0          3
   295 AE        138 1423580300          4          0       0          0
   295 UL          1          0          4          0       0          3
  
 
demo@FREEPDB1> /* From session-2 did this */
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             60
 
demo@FREEPDB1> update t set y = y+1 where x =1;
 
1 row updated.
 
demo@FREEPDB1>
demo@FREEPDB1> select * from v$lock where sid = 60;
 
 SID TY        ID1        ID2  LMODE    REQUEST  BLOCK CON_ID
---- -- ---------- ---------- ------ ---------- ------ ------
  60 TX     524317       1560      6          0      0      3
  60 TM     117538          0      3          0      0      3
  60 TM     117537          0      3          0      0      3
  60 AE        138 1423580300      4          0      0      0
  60 UL          1          0      4          0      0      3 
 
The update from the second session was not blocked – even though it was updating the same row as it got updated from the first session – how this was even remotely possible? This seems to break the whole concept of database locking.
 
Now let us do commit from second session and go back to first session
 
demo@FREEPDB1>  --
demo@FREEPDB1>  -- Session-1
demo@FREEPDB1>  --
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
Even though we have an ongoing lock in first session, this session can see now the second session (session id = 60) committed changes – which is really good – if that doesn’t happen/notified then this session will still assume the value of y = 1 then the expression y = y+1  upon execution will set y=2, and results into a lost update issues (that is changes from second session, will get overwritten)
 
Now when we commit from first session, it goes like this
 
demo@FREEPDB1>  --
demo@FREEPDB1>  -- Session-1
demo@FREEPDB1>  --
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          3
 
demo@FREEPDB1>
 
and it worked well, even though the same row/value got updated from two different session – which was impossible with prior version of Oracle database.
 
So what is happening with Oracle database 23c and how the database has become efficient with handling these non-blocking updates? we will see in the next blogpost.