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.