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.
dbwhisperer 