What is lock-free Reservation in 23ai and how can it be used? In this posting we will illustrate first steps in using lock-free reservation. 
First let’s describe a possible use case. Transaction processing usually involves transactions with data updates that replace old values with new. For example, an application handles multiple transactions at various states sucha as when a user puts an item into the cart, the item becomes unavailable to other buyers and yet is unsold. If there are multiple concurrent transactions adding items to the cart and checking out or abandoning the cart, the field must be locked for a transaction before a commit or rollback can change the quantity.

Locking data for long periods prevents other concurrent transactions from modifying the item until the lock is released. If you allow concurrent transactions to access data, you must control the transactions to preserve application correctness. However serialization will block other concurrent transactions from modifying the row until the transaction that initiated the update is completed.
How can we solve this?
The new capability in 23ai to assign a priority low or medium to a transaction (see blog posting Priority Transactions with high, medium and low priority) does not help here because it can only terminate the low priority sessions after a certain wait time.

But what about lock-free reservation in 23ai? Lock-free reservation provides an in-database capability for transactions and operates on so-called reservable columns. It enables concurrent transactions to proceed without being blocked on updates made to reservable columns.

In general, you can find more information in 

How does it work? Lock-free reservation allows transactions to concurrently add or subtract from the same row’s reservable column WITHOUT blocking each other by specifying the conditions for which the updates may proceed. This is accomplished by specifying that the numeric column is a RESERVABLE column and creating a CHECK constraint for the column. Reservable column updates do not lock the rows and hence do not block another transaction from updating non-reservable columns of the same row concurrently.

So, let’s illustrate the feature with a simple example to get an idea how it works.
 
First let’s create a table INVENTORY with the reservable column QTY_ON_HAND. Keep in mind that only numeric data types are supported and reservable column property can only be specified for a column on a table that has a primary key. 

create table inventory
( item_id            NUMBER          CONSTRAINT inv_pk PRIMARY KEY,
  item_display_name  VARCHAR2(100)   NOT NULL,
  item_desc          VARCHAR2(2000),
  qty_on_hand        NUMBER          RESERVABLE 
                                     CONSTRAINT qty_ck 
                                     CHECK (qty_on_hand >= 0) NOT NULL,
  shelf_capacity     NUMBER          NOT NULL,
    CONSTRAINT shelf_ck CHECK (qty_on_hand <= shelf_capacity)
)

A reservable column may be also added to a table using the ALTER TABLE command and a non-reservable column may be converted to a reservable column using the ALTER TABLE command and vice versa. In addition, the usage of a constraint is optional. The transaction, based on the constraints placed on the reservable column, decides whether the quantity is sufficient to make the update. In our example we want to make sure that the value in QTY_ON_HAND is always positive.

With and ALTER TABLE command you can change the column to NOT RESERVABLE again.

SQL> alter table inventory modify (qty_on_hand NOT RESERVABLE);
Table altered.

SQL> drop table inventory;
Table dropped.

You can get an overview concerning the restrictions in Database Development Guide 29.5 Guidelines and Restrictions for Lock-Free Reservation.

How can we monitor the creation and usage of reservable columns? Let’s assume we created the table INVENTORY again as shown above.
Let’s query the data dictionary view USER_TABLES and USER_TAB_COLS and investigate the new columns for this feature.

SQL> col table_name format a30
SQL> col has_reservable_column format a30
SQL> col reservable_column format a30

SQL> select table_name, has_reservable_column
     from user_tables
     where table_name = 'INVENTORY';

TABLE_NAME                     HAS_RESERVABLE_COLUMN
------------------------------ ---------------------
INVENTORY                      YES
SQL> select column_name, reservable_column
     from user_tab_cols
     where table_name = 'INVENTORY' and reservable_column = 'YES';

COLUMN_NAME               RESERVABLE_COLUMN
------------------------- -----------------
QTY_ON_HAND               YES

As usual more information about the constraints can be found in USER_CONSTRAINTS:

SQL> col search_condition format a40
SQL> col constraint_name format a20
SQL> select constraint_name, search_condition
     from user_constraints 
     where table_name='INVENTORY';

CONSTRAINT_NAME      SEARCH_CONDITION
-------------------- ----------------------------------------
SYS_C008386          "ITEM_DISPLAY_NAME" IS NOT NULL
SYS_C008387          "QTY_ON_HAND" IS NOT NULL
SYS_C008388          "SHELF_CAPACITY" IS NOT NULL
QTY_CK               qty_on_hand >= 0
SHELF_CK             qty_on_hand <= shelf_capacity
INV_PK
6 rows selected.

The creation of the table INVENTORY creates in addition an associated reservation journal table. In our case it’s called SYS_RESERVJRNL_100639 (where 100639 is the object id of table INVENTORY). The reservation journal table is created under the same user schema and in the same tablespace as the user table.
 
Let’s query the table USER_OBJECTS to list the objects and to verify the creation of the two tables – INVENTORY and the journal table SYS_RESERVJRNL_100639.

SQL> select object_name, object_type, created
     from user_objects order by 3 desc;

OBJECT_NAME                              OBJECT_TYPE             CREATED
---------------------------------------- ----------------------- ---------
INV_PK                                   INDEX                   23-MAY-23
SYS_RESERVJRNL_100639                    TABLE                   23-MAY-23
INVENTORY                                TABLE                   23-MAY-23
...

With the help of this journal table, lock-free reservation enables tracking the reservable UPDATEs within the database during the execution of transactions. A transaction can read its own lock-free reservations by selecting from the reservation journal tables (here SYS_RESERVJRNL_100639) on which the transaction has issued a reservable update.
Please note: Reservations made by other transactions are not visible.

Let’s check the structure:

SQL> desc SYS_RESERVJRNL_100094
 Name                                                                          Null?    Type
 ----------------------------------------------------------------------------- -------- --------------------------------------------
 ORA_SAGA_ID$                                                                           RAW(16)
 ORA_TXN_ID$                                                                            RAW(8)
 ORA_STATUS$                                                                            VARCHAR2(11)
 ORA_STMT_TYPE$                                                                         VARCHAR2(6)
 ITEM_ID                                                                       NOT NULL NUMBER
 QTY_ON_HAND_OP                                                                         VARCHAR2(1)
 QTY_ON_HAND_RESERVED                                                                   NUMBER


Let’s add some rows to the table and perform a COMMIT:

insert into inventory values (123, 'Milk', 'Lowfat 2%', 100, 120);
insert into inventory values (456, 'Bread', 'Multigrain', 50, 100);
insert into inventory values (789, 'Eggs', 'Organic', 50, 75);
commit;

Let’s try a first UPDATE and violate the rule that direct assignments to a reservable column are allowed.

SQL> update inventory set qty_on_hand=qty_on_hand;   
update inventory set qty_on_hand=qty_on_hand
       *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.

There are more limitations you need to be aware of e.g. mixing reservable and non-reservable column updates in the same update statement is not allowed. If you need to get a complete overview concerning the rules and restrictions please refer to Database Development Guide 29.5 Guidelines and Restrictions for Lock-Free Reservation.

At the moment the table content looks like:

SQL> select item_id, qty_on_hand, shelf_capacity 
     from inventory;

   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123         100            120
       456          50            100
       789          50             75


Now let’s update the INVENTORY table and violate constraints. If the reservation fails due to insufficient capacity in SHELF_CAPACITY, the UPDATE statement will fail with the CHECK constraint violation.

SQL> update inventory set qty_on_hand=qty_on_hand+100 where item_id=123;
update inventory set qty_on_hand=qty_on_hand+100 where item_id=123
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SHELF_CK) violated

Or let’s perform an UPDATE and try to substract 110. In this case the constraint SCOTT.QTY_CK will be checked and the following constraint error occurs.

SQL> update inventory
     set qty_on_hand = qty_on_hand - 110
     where item_id = 123;  2    3
update inventory
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.QTY_CK) violated

In both cases a statement level rollback occurs.

Also, you really need to add or subtract values to the reservable column. You cannot do the following …

SQL>  update inventory set QTY_ON_HAND=10 where item_id=123;
 update inventory set QTY_ON_HAND=10 where item_id=123
        *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.


In our next scenario we will change the value of the reservable column QTY_ON_HAND for ITEM_ID 123 in two transactions.
 
Please keep in mind UPDATEs to reservable columns do not lock the row until the COMMIT of the transaction. Instead, reservable columns provide for lock-free reservations. 

At time T1: transaction 1

We try to perform two UPDATEs in transaction 1. The second UPDATE leads to a constraint error and a statement level rollback occurs.

SQL> update inventory
     set qty_on_hand = qty_on_hand - 50
     where item_id = 123;
     1 row updated.

SQL> update inventory 
     set qty_on_hand = qty_on_hand - 60 
     where item_id = 123;
update inventory set qty_on_hand = qty_on_hand - 60 where item_id = 123
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.QTY_CK) violated

The transaction can see the changes it has made to the reservable columns by inspecting the associated journal table (SYS_RESERVJRNL_100639).
Let’s query the journal table to list the change(s). Only the successful UPDATE is listed.

SQL> set linesize window
SQL> select * from SYS_RESERVJRNL_100639;

ORA_SAGA_ID$                     ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$      ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- --------------------
                                 090003007C3E0000 ACTIVE       UPDATE                  123 -                         50

At time T2: transaction 2

Reservable columns provide lock-free reservations. Therefore we can perform an UPDATE of the same item in another transaction 2.

SQL> update inventory
     set qty_on_hand = qty_on_hand + 20
     where item_id = 123;
1 row updated.

Let’s check again the journal table SYS_RESERVJRNL_100639.

SQL> select * from SYS_RESERVJRNL_100639;

ORA_SAGA_ID$                     ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$      ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- --------------------
                                 0800010044340000 ACTIVE       UPDATE                  123 +                         20

At time T3: transaction 2 

As we can see the UPDATEs of the row are not performed yet because the transactions are not committed.

SQL> select item_id, qty_on_hand, shelf_capacity 
     from inventory where item_id=123;

   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123         100            120

Now let’s perform a COMMIT because lock-free reservations are transformed to the actual updates at COMMIT of the transaction.

SQL> commit; 
Commit complete.

The change of transaction 2 is applied.

 SQL>  select item_id, qty_on_hand, shelf_capacity 
       from inventory where item_id=123;

   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123         120            120

At time T4: transaction 1 
Now let’s perform a COMMIT in transaction 1. Finally, also the change of transaction 1 is applied and visible in both transactions. 

SQL> commit; 
Commit complete.

SQL> select item_id, qty_on_hand, shelf_capacity  
     from inventory where item_id=123;

   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123          70            120

After the update operations are committed the journal table is empty again. You may choose another order of transaction commit order, because the order of commit of the transactions does not matter.
Please note the journal table is for internal usage only. Therefore, user DML and DDL operations are not permitted on a reservation journal table. You cannot create or modify a reservation journal table using DML. You also cannot use SQL to drop, rename, or change the reservation table’s definition. 

This concludes our first test scenario.

Note: Read also the posting Lock-free reservation: scale your apps from Stephane to get insights about the usage scenarios especially concerning scalability. 

Further Reading