Oracle | Lock Free Reservation

Oracle Lock Free Reservation Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Reservable columns prevent blocking sessions with frequently updated column data.

The reservable column data type must be NUMBER, INTEGER or FLOAT and the table must have a primary key.

Use Case Reference https://oracle-base.com/articles/23/lock-free-reservations-23

Create Table

Creating a table with an associated Lock Free Reservation table.

It is possible to create the table in a single step using an inline Primary Key but at the Library we prefer to build objects incrementally to make possible the use of all object associated syntax elements. CREATE TABLE [<schema_name>.<table_name> ( <column_name> <column_data_type> RESERVABLE CONSTRAINT [<constraint_name>(<constraint_specification>)], <column_name> <column_data_type>, ...)

conn / as sysdba

      

      CREATE TABLE inventory(

      item_key        VARCHAR2(20),

      items_remaining NUMBER);

      

      Table created.

      

      ALTER TABLE inventory MODIFY (items_remaining RESERVABLE);

      

      ORA-55756: Reservable column property is not supported for columns of a system table.

      

      conn c##uwclass

      

      CREATE TABLE inventory(

      item_key        VARCHAR2(20),

      items_remaining NUMBER RESERVABLE);

      

      ORA-55728: Reservable column property can only be specified for a column on a table 

      that has a primary key.

      

      CREATE TABLE inventory(

      item_key        VARCHAR2(20),

      items_remaining NUMBER);

      

      Table created.

      

      ALTER TABLE inventory

      ADD CONSTRAINT pk_inventory

      PRIMARY KEY (item_key);

      

      Table altered.

      

      ALTER TABLE inventory MODIFY (items_remaining RESERVABLE);

      

      Table altered.

      

      SQL> desc inventory

      

      Name             Null?    Type

      ---------------- -------- -------------

      ITEM_KEY         NOT NULL VARCHAR2(20)

      ITEMS_REMAINING  NOT NULL NUMBER

      

      SELECT column_id, column_name, reservable_column

      FROM dba_tab_cols

      WHERE table_name = 'INVENTORY'

      ORDER BY 1;

      

       COLUMN_ID COLUMN_NAME      RES

      ---------- ---------------- ---

               1 ITEM_KEY         NO

               2 ITEMS_REMAINING  YES

      

      -- identify the reserve journaling table

      -- note that the table_type is NULL

      

      SELECT table_name, table_type

      FROM user_all_tables

      WHERE table_name LIKE 'SYS_RESERVJRNL%';

      

      TABLE_NAME             TABLE_TYPE

      ---------------------- -----------

      SYS_RESERVJRNL_189104

      

      SQL> desc sys_reservjrnl_189104

      

      Name                    Null?     Type

      ------------------------ -------- ------------

      ora_saga_id$                      RAW(16)

      ora_txn_id$                       RAW(8)

      ora_status$                       VARCHAR2(11)

      ora_stmt_type$                    VARCHAR2(6)

      item_key                 NOT NULL VARCHAR2(20)

      items_remaining_op                VARCHAR2(1)

      items_remaining_reserved          NUMBER

      

      -- querying DBA_TAB_COLS found no hidden columns in the reserve table

      -- no dependencies, name or referenced_name, found on the reserve table

      

      SELECT column_id, column_name, reservable_column, hidden_column

      FROM dba_tab_cols

      WHERE table_name = 'SYS_RESERVJRNL_189104'

      ORDER BY 1;

      

       COLUMN_ID COLUMN_NAME                    RES HID

      ---------- ------------------------------ --- ---

               1 ORA_SAGA_ID$                   NO  NO

               2 ORA_TXN_ID$                    NO  NO

               3 ORA_STATUS$                    NO  NO

               4 ORA_STMT_TYPE$                 NO  NO

               5 ITEM_KEY                       NO  NO

               6 ITEMS_REMAINING_OP             NO  NO

               7 ITEMS_REMAINING_RESERVED       NO  NO

Fail Demos

The demo at right demonstrates that neither a Unique Index nor a Unique Constraint can substitute for the table, with a RESERVABLE column, having a Primary Key CREATE TABLE test ( tid VARCHAR2(20), tcount NUMBER); CREATE UNIQUE INDEX ix_test ON test(tid); Index created. ALTER TABLE test MODIFY (tcount RESERVABLE); ORA-55728: Reservable column property can only be specified for a column on a table that has a primary key. DROP INDEX ix_test; ALTER TABLE test ADD CONSTRAINT uc_test UNIQUE (tid); ORA-55728: Reservable column property can only be specified for a column on a table that has a primary key.

Reservable Transaction Table Demo

The demo at right is not intended to show a Reservable Column use case, for that we recommend the Use Case demo link at the top of this page.

This demo seeks to clarify the difference between valid and invalid syntax that is not well documented online as well as the nature of how the journaling table is used. INSERT INTO inventory VALUES (1,1); INSERT INTO inventory VALUES (2,2); INSERT INTO inventory VALUES (3,3); SELECT COUNT(*) FROM sys_reservjrnl_189104; COUNT(*) --------- 0 COMMIT; SELECT COUNT(*) FROM sys_reservjrnl_189104; COUNT(*) --------- 0 UPDATE inventory SET items_remaining = 100 where item_key = 2; update inventory set items_remaining = 100 where item_key = 2 * ERROR at line 1: ORA-55746: Reservable column update statement only supports + or - operations on a reservable column. UPDATE inventory SET items_remaining = items_remaining + 10 WHERE item_key = 2; 1 row updated. SELECT COUNT(*) FROM sys_reservjrnl_189104; COUNT(*) --------- 1 COMMIT; Commit complete. SELECT COUNT(*) FROM sys_reservjrnl_189104; COUNT(*) --------- 0 -- try updating multiple rows UPDATE inventory SET items_remaining = items_remaining + 10; ORA-55732: Reservable column update should specify all the primary key columns in the WHERE clause -- make that MUST, not SHOULD. SELECT * FROM inventory; ITEM_KEY ITEMS_REMAINING --------- ---------------- 1 1 2 12 3 3 SELECT * FROM sys_reservjrnl_189104; no rows selected UPDATE inventory EST items_remaining = items_remaining + 100 WHERE item_key = 3; 1 row updated SELECT * FROM sys_reservjrnl_189104; ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ITEM_KEY ITEMS_REMAINING_OP ------------ ---------------- ----------- -------------- -------- ------------------ 01001A00215D0000 ACTIVE UPDATE 3 + ITEMS_REMAINING_RESERVED ------------------------ 100