Oracle Lock Free Reservations

The Oracle 23c new feature Lock Free Reservations allows to break with the traditional locking mechanism.

Now multiple concurrent sessions can update the same row without be blocked !!! Let’s see how it works.

In the traditional locking mechanism, when a first session modifies a row, oracle will hold 2 locks :

• Row Exclusive Lock to prevent other transactions which try to update the same row
• Table Share Lock that prevent DDL operation on the row being modified

SQL> update products set price = price+10 where product= 'A1';

1 row updated.

If a second session tries to update the same row, oracle will request :

• Row Exclusive Lock

and hold:

• Table Share Lock

The session 2 will wait until the resource locked by session 1 is released.

SQL> update products set price = price+20 where product= 'A1';
--session 2 is waiting...

---

select a.sid,a.type,a.id1,b.object_name,a.lmode,a.request,a.ctime,a.block from v$lock a, dba_objects b where a.id1 = b.object_id(+) and sid in ( 67, 237) and type in ('TX','TM');

SID TYPE  ID1  OBJECT_NAME    LMODE    REQUEST      CTIME  BLOCK
-----------------------------------------------------------------------------
67  TM      81133    PRODUCTS 3               0       7          0
237 TM     81133     PRODUCTS 3               0       2           0
237 TX      262157            0               6       2           0
67  TX      262157            6               0       7            1

The session 1 hold a ROW EXCLUSIVE LOCK on the resource 262157.

The session 2 requested a ROW EXCLUSIVE LOCK on the resource 262157 and wait.

With Lock Free Reservations features, this behaviour changes and you can have concurrent transactions locked the same resource!!!

First, you have to modify your column in RESERVABLE, new columns indicating if column is reservable or if table contains reservable columns has been added into the oracle catalog :

SQL> alter table if exists products modify (price reservable);
Table altered.
SQL> select has_reservable_column from user_tables where table_name = 'PRODUCTS';
HAS_RESERVABLE_COLUM
----------------------------
YES

SQL> select column_name,reservable_column from user_tab_cols where table_name = 'PRODUCTS' and column_name = 'PRICE';
COLUMN_NAME RESERVABLE_COLUMN
----------------------------------
PRICE        YES

Now 2 concurrent updates are possible :

Session 1 :

SQL> SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
-----------------------------------------------------------------------------
271

SQL> update products set price = price+10 where product= 'A1';

1 row updated.

Session 2 :

SQL> SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
-----------------------------------------------------------------------------
44

SQL> update products set price = price+20 where product= 'A1';

1 row updated.
--Session 2 is no more waiting...

If we have a look the locks in v$lock, we see that the Table Share Lock is always hold by the both sessions and also the Row Exclusive Lock but now on difference resource.

Moreover a new Table Share Lock has been hold on new system table SYS_RESERVJRNL_81133 created by Oracle when we have altered the column in RESERVABLE:

select a.sid,a.type,a.id1,b.object_name,a.lmode,a.request,a.ctime,a.block from v$lock a, dba_objects b where a.id1 = b.object_id(+) and sid in (	271,	    44) and type in ('TX','TM')

SID TYPE ID1 OBJECT_NAME LMODE	            REQUEST      CTIME	BLOCK
---------- -- ---------- ----------------------------------------------------
44  TM   81133 PRODUCTS               3	    0           1201	    0
271 TM	 81133 PRODUCTS               3	    0           1205	    0
44  TM	 87804 SYS_RESERVJRNL_81133   3	    0           1201	    0
271 TM	 87804 SYS_RESERVJRNL_81133   3	    0           1205	    0
271 TX	 393235                       6	    0           1205	    0
44  TX	 262176                       6     0           1201	    0

So, how to ensure the consistency of the database ? The answer is into the table SYS_RESERVJRNL_81133 which is persistent only at the session and contains the updated value only for the session:

Session 1:

SQL> select * from SYS_RESERVJRNL_81133;

ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ PRODUCT PRICE_O PRICE_RESERVED
-------------------------------- ---------------- ------------ --------------
060013005B1D0000 ACTIVE       UPDATE		A1	   +	10

SQL>

Session 2:

SQL> select * from SYS_RESERVJRNL_81133;

ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ PRODUCT    PRICE_O PRICE_RESERVED
-------------------------------- ---------------- ------------ --------------
04002000EE190000 ACTIVE       UPDATE		A1	   +		       20

SQL>

After the commit or rollback is done, the target table is updated or rollbacked and the system reservation journal table is empty:

--Session 1
SQL> SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
-----------------------------------------------------------------------------
271
SQL> commit;

Commit complete.

SQL> select * from SYS_RESERVJRNL_81133;

no rows selected

SQL>
--Session 2
SQL> SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
-----------------------------------------------------------------------------
44
SQL> commit;

Commit complete.

SQL> select * from SYS_RESERVJRNL_81133;

no rows selected

SQL>

Very nice feature but with some limitations where the most important are:

• Only numeric columns can be a reservable column
• Reservable columns cannot be indexed
• Update on reservable column must reference the reservable column (no hard value) and must use the operand ‘+’ or ‘-‘.

The guidelines and restrictions for Reservable Columns is here.