This article is about the major new feature in Oracle 23c with the goal of alleviating locking problems. Basically, there is a queue table and the operations are processed sequentially, without locking. To demonstrate, let’s create our table and populate it:
SQL> create table test_tab (
2 c1 integer default on null test_seq.nextval primary key,
3 c2 varchar2(20),
4* c3 integer);
Table TEST_TAB created.
SQL> insert into test_tab(c1,c2,c3) values (null,'qwerty',1);
1 row inserted.
Elapsed: 00:00:00.014
SQL> commit;
Commit complete.
So, now we have a table with 1 row in it. The table is completely classic, no special features. Now, let’s update the table without committing and open another session, which will try to get the lock:
Session 1:
SQL> update test_tab set c3=2 where c1=1539;
1 row updated.
Elapsed: 00:00:00.012
SQL>
Session 2:
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> select c3 from test_tab where c1=1539 for update of c3 nowait;
select c3 from test_tab where c1=1539 for update of c3 nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
That is completely normal, that is what is expected of any relational database. Now, let’s play a little trick:
SQL> alter table test_tab modify c3 reservable; Table TEST_TAB altered. Elapsed: 00:00:00.082
That will change the behavior drastically:
Session 1:
SQL> update test_tab set c3=c3+1 where c1=1539;
1 row updated.
Session 2:
SQL> select c3 from test_tab where c1=1539 for update of c3 nowait; C3 ---------- 2 Elapsed: 00:00:00.00
So, it appears that there is no lock on the row created by the UPDATE statement? Let’s check locks:
SQL> select sid from v$session where username='SCOTT'; SID _______ 4 1017 Elapsed: 00:00:00.004 SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (4,1017); SID ID1 ID2 TYPE LMODE REQUEST _______ _________ _____________ _______ ________ __________ 1017 524301 693 TX 6 0 4 85826 0 TM 3 0 4 85824 0 TM 3 0 1017 85824 0 TM 3 0 4 393220 651 TX 6 0 4 138 1744658490 AE 4 0 1017 138 1744658490 AE 4 0 7 rows selected. Elapsed: 00:00:00.113
So, let’s first check the “TM” locks. Those are table locks and will tell us what is going on under the hood:
SQL> select object_name,object_type,object_id from dba_objects where object_id in (85824,85826) and owner='SCOTT';
OBJECT_NAME OBJECT_TYPE OBJECT_ID
_______________________ ______________ ____________
TEST_TAB TABLE 85824
SYS_RESERVJRNL_85824 TABLE 85826
So, there are two tables involved here? I was updating just TEST_TAB, where does this 2nd table come from? Let’s see:
SQL> desc SYS_RESERVJRNL_85824 Name Null? Type ----------------------------------------- -------- ---------------------------- ORA_SAGA_ID$ RAW(16) ORA_TXN_ID$ RAW(8) ORA_STATUS$ CHAR(12) ORA_STMT_TYPE$ CHAR(16) C1 NOT NULL NUMBER(38) C3_OP CHAR(7) C3_RESERVED NUMBER(38)
So, this is a journal table which contains the information about the transaction, its status and operation. Here is the contents of the table:
QL> select * from SYS_RESERVJRNL_85824; ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ -------------------------------- ---------------- ------------ ---------------- C1 C3_OP C3_RESERVED ---------- ------- ----------- 08000D00B5020000 ACTIVE UPDATE 1539 + 1
Basically, the operation is written to the journal and applied by the background processes. I was not yet able to figure out which process is responsible for that. And that is the new feature: a way to bypass the locking contention. However, there are serious limitations Here are some:
- It is only possible to make NUMBER, INTEGER or FLOAT columns reservable
- It is not possible to drop the table with reservable columns. The column should be made NOT RESERVABLE before dropping the table.
- The only possible assignment is COL=COL <op> CONSTANT like C3 = C3 + 1. It is not possible to use constant value in the assignment.
Update limiteation:
update test_tab set c3=1024 where c1=1539 Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-55746: Reservable column update statement only supports + or - operations on a reservable column. 55746. 00000 - "Reservable column update statement only supports + or - operations on a reservable column." *Document: YES *Cause: An attempt is being made to update a reservable column with something other than a + or -. An amount should be added or subtracted from the reservable column. Direct assignments to reservable columns are not supported. *Action: Change the update statement to add or subtract from the same reservable column. The set clause should be of the form reservable_col1 = reservable_col1 + (<expression>) or reservable_col1 = reservable_col1 - (<expression>)." where the expression in parenthesis evaluates to the amount to be added or subtracted from the same reservable column, reservable_col1.
Dropping the table:
QL> drop table test_tab; Error starting at line : 1 in command - drop table test_tab Error report - ORA-55764: Cannot DROP or MOVE tables with reservable columns. First run "ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE)" and then DROP or MOVE the table. 55764. 00000 - "Cannot DROP or MOVE tables with reservable columns. First run \"ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE)\" and then DROP or MOVE the table." *Document: YES *Cause: An attempt is being made to DROP or MOVE a table with reservable columns. *Action: Run ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE) before issuing a DROP or MOVE of the table. Elapsed: 00:00:00.066
Datatype limitation:
SQL> alter table test_tab modify c2 reservable; Error starting at line : 1 in command - alter table test_tab modify c2 reservable Error report - ORA-55748: Reservable column property specified on column "C2" is supported only on columns of data types Oracle NUMBER, INTEGER, and FLOAT. 55748. 00000 - "Reservable column property specified on column \"%s\" is supported only on columns of data types Oracle NUMBER, INTEGER, and FLOAT." *Document: YES *Cause: An attempt is being made to specify a reservable column with a data type other than Oracle NUMBER, INTEGER, or FLOAT. *Action: Use Oracle NUMBER, INTEGER, or FLOAT data types for reservable columns. Elapsed: 00:00:00.019
This feature looks promising. However, this is very narrow feature of limited usability right now. I am sure that Oracle will build on it in the next release.