One of the new features in Oracle Database 23c is Lock-Free Reservations.
In earlier versions, an update to the same row of the same table by two concurrent transactions would impose a row level TX lock – but not any more in Oracle Database 23c!
A new RESERVABLE key word is used to define a property for a column that has a numeric data type.
Transactions making the updates do not lock the row, but indicate their intention to modify the reservable column value by a delta amount.
Oracle GoldenGate 23c also supports all Oracle Database 23c new features including Lock-Free reservations – so let is look at an example using a table with a RESERVABLE column and data replication between two Oracle 23c Pluggable Databases.
Lock-free reservation ensures that the lock is obtained only at the time of commit to update a reservable column.
Let us see an example of Lock-Free Reservations feature with GoldenGate 23c replication.
The example illustrates how in Oracle Database 23c we can enable concurrent transactions to proceed without being blocked on updates made to reservable columns.
The business rule to be enforced is that out of a total of 2000 tickets, only 1800 can be sold to the public and the remaining 200 tickets are not for general sale purposes – but to be given as complimentary passes!
The TICKETS_FOR_SALE_TO_PUBLIC column is defined as RESERVABLE with the check constraint in place enforcing the above business rule.
Create the EVENTS table in source database FREEPDB1.
[oracle@db23c ~]$ sqlplus demo/Oracle_4U@freepdb1 SQL*Plus: Release 23.0.0.0.0 - Production on Thu Nov 2 23:58:13 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Fri Oct 27 2023 01:04:10 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> create table events (event_id number, event_name varchar2(30), tickets_for_sale_to_public number RESERVABLE constraint min_tkt_hold check (tickets_for_sale_to_public >= 200), constraint event_pk primary key (event_id)); Table created. SQL> quit Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09
Create the EVENTS table in target database FREEPDB2.
[oracle@db23c ~]$ sqlplus demo/Oracle_4U@freepdb2 SQL*Plus: Release 23.0.0.0.0 - Production on Fri Nov 3 00:00:46 2023 Version 23.3.0.23.09 Copyright (c) 1982, 2023, Oracle. All rights reserved. Last Successful login time: Thu Oct 26 2023 00:04:09 -04:00 Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> create table events (event_id number, event_name varchar2(30), tickets_for_sale_to_public number RESERVABLE constraint min_tkt_hold check (tickets_for_sale_to_public >= 200), constraint event_pk primary key (event_id)); Table created.
On Source – Insert a row into the EVENTS table.
Note the value 2000 for the TICKETS_FOR_SALE_TO_PUBLIC column. The value of this column should always be equal to or more than 200 based on the check constraint defined on this column.
[oracle@db23c ~]$ sqlplus demo/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Nov 3 00:26:58 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Thu Nov 02 2023 23:58:13 -04:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> insert into events
values
(1001,'COLDPLAY Live',2000);
1 row created.
SQL> commit;
Commit complete.
On Target database FREEPDB2 – GoldenGate replication has applied the row to the target EVENTS table.
[oracle@db23c product]$ sqlplus demo/Oracle_4U@freepdb2
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Nov 3 00:40:39 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Fri Nov 03 2023 00:35:17 -04:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> select count(*) from events;
COUNT(*)
----------
1
We now have THREE concurrent transactions updating the same single row in the EVENTS table.
Note – none of the three transactions are actually locked!
A Reservation Journal table is automatically created and this journal table is session specific keeping track of modifications being made to the column marked as Reservable.
[oracle@db23c product]$ sqlplus demo/Oracle_4U@freepdb1
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Nov 3 00:42:15 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Fri Nov 03 2023 00:40:22 -04:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> select object_id from user_objects where object_name='EVENTS';
OBJECT_ID
----------
85941
SQL> select table_name from user_tables where table_name like '%85941%';
TABLE_NAME
--------------------------------------------------------------------------------
SYS_RESERVJRNL_85941
SQL> desc events
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENT_ID NOT NULL NUMBER
EVENT_NAME VARCHAR2(30)
TICKETS_FOR_SALE_TO_PUBLIC NOT NULL NUMBER
SQL> desc SYS_RESERVJRNL_85941
Name Null? Type
----------------------------------------- -------- ----------------------------
ORA_SAGA_ID$ RAW(16)
ORA_TXN_ID$ RAW(8)
ORA_STATUS$ VARCHAR2(11)
ORA_STMT_TYPE$ VARCHAR2(6)
EVENT_ID NOT NULL NUMBER
TICKETS_FOR_SALE_TO_PUBLIC_OP VARCHAR2(1)
TICKETS_FOR_SALE_TO_PUBLIC_RESERVED NUMBER
SQL> select TICKETS_FOR_SALE_TO_PUBLIC_OP, TICKETS_FOR_SALE_TO_PUBLIC_RESERVED from SYS_RESERVJRNL_85941;
no rows selected
###################################################
SESSION 1
Tickets Wanted =200
Tickets left for sale=1600
###################################################
SQL> update events
set tickets_for_sale_to_public=tickets_for_sale_to_public-200 where event_id=1001;
1 row updated.
SQL> select TICKETS_FOR_SALE_TO_PUBLIC_OP, TICKETS_FOR_SALE_TO_PUBLIC_RESERVED from SYS_RESERVJRNL_85941;
T TICKETS_FOR_SALE_TO_PUBLIC_RESERVED
- -----------------------------------
- 200
SQL> select TICKETS_FOR_SALE_TO_PUBLIC from events;
TICKETS_FOR_SALE_TO_PUBLIC
--------------------------
2000
###################################################
SESSION 2
Tickets Wanted =800
Tickets left for sale=800
###################################################
SQL> select TICKETS_FOR_SALE_TO_PUBLIC_OP, TICKETS_FOR_SALE_TO_PUBLIC_RESERVED from SYS_RESERVJRNL_85941; no rows selected SQL> update events set tickets_for_sale_to_public=tickets_for_sale_to_public-800 where event_id=1001; 1 row updated. SQL> select TICKETS_FOR_SALE_TO_PUBLIC_OP, TICKETS_FOR_SALE_TO_PUBLIC_RESERVED from SYS_RESERVJRNL_85941; T TICKETS_FOR_SALE_TO_PUBLIC_RESERVED - ----------------------------------- - 800
########################################
SESSION 3
Tickets Wanted = 900
Tickets left for sale= 800
#########################################
SQL> update events set tickets_for_sale_to_public=tickets_for_sale_to_public-900 where event_id=1001; update events * ERROR at line 1: ORA-02290: check constraint (DEMO.MIN_TKT_HOLD) violated Help: https://docs.oracle.com/error-help/db/ora-02290/
################################
SESSION 1
COMMIT TRANSACTION
################################
SQL> commit;
Commit complete.
SQL> select TICKETS_FOR_SALE_TO_PUBLIC from events;
TICKETS_FOR_SALE_TO_PUBLIC
--------------------------
1800
SQL> select TICKETS_FOR_SALE_TO_PUBLIC_OP, TICKETS_FOR_SALE_TO_PUBLIC_RESERVED from SYS_RESERVJRNL_85941;
no rows selected
################################
SESSION 2
COMMIT TRANSACTION
################################
SQL> commit;
Commit complete.
SQL> select TICKETS_FOR_SALE_TO_PUBLIC from events;
TICKETS_FOR_SALE_TO_PUBLIC
--------------------------
1000
#############################################
SESSION 3
CANNOT COMMIT TRANSACTION – CONSTRAINT VIOLATED!
#############################################
SQL> update events set tickets_for_sale_to_public=tickets_for_sale_to_public-900 where event_id=1001; update events * ERROR at line 1: ORA-02290: check constraint (DEMO.MIN_TKT_HOLD) violated Help: https://docs.oracle.com/error-help/db/ora-02290/
#############################################
SESSION 3
CAN NOW COMMIT TRANSACTION
#############################################
#######################################
SESSION 3 - CAN NOW COMMIT TRANSACTION
#######################################
SQL> update events
set tickets_for_sale_to_public=tickets_for_sale_to_public-800
where event_id=1001;
1 row updated.
SQL> commit;
Commit complete.
SQL> select TICKETS_FOR_SALE_TO_PUBLIC from events;
TICKETS_FOR_SALE_TO_PUBLIC
--------------------------
200
Verify Updates replicated by GoldenGate to target FREEPDB2 pluggable database.
[oracle@db23c product]$ sqlplus demo/Oracle_4U@freepdb2
SQL*Plus: Release 23.0.0.0.0 - Production on Fri Nov 3 02:06:05 2023
Version 23.3.0.23.09
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Fri Nov 03 2023 00:40:39 -04:00
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> select TICKETS_FOR_SALE_TO_PUBLIC from events;
TICKETS_FOR_SALE_TO_PUBLIC
--------------------------
200
