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.