Oracle 23ai: Lock Free Reservations Capability

Overview:

• In previous database releases when a column value of a row is updated by adding or subtracting from it, all other updates to that row are blocked until the transaction is committed or rolled back.
• Oracle Database 23ai introduces a new feature called “Lock Free Reservations” that allows multiple concurrent updates on a numeric column value to proceed without being blocked by uncommitted updates when adding or subtracting from the column value.
• Used with applications that operate on numeric aggregate data. Such as data involve subtraction or addition of the values rather than assigning a value.
• The Lock Free Reservation feature is enabled by default. A Lock-Free Reservation parameter, named “lockfree_reservation“, is provided at the PDB level.
• To use Lock Free Reservation, use the RESERVABLE keyword to declare a RESERVABLE numeric column when you CREATE or ALTER a table.

Benefits: 

• Reserve values without locking.
• Value locked on commit.
• Short-held locks.
• Improved concurrency.
• Reduced bottlenecks. 

Use Cases:

• Bank account balance (debt and credit transactions).
• Inventory and supply chain control.
• Ticketing 

Restrictions:

• Only numeric data type.
•  The table must have a primary key.
• A Reservable column cannot be part of a foreign key constraint. 
• A Reservable column cannot be a primary key, virtual column, or identity column.
• Indexes are not supported on a Reservable column.
• No updates on multiple Reservable columns in a table. 
• No mixing non-Reservable and Reservable columns updates.

Demo:

In this demo, I’ll create a table “ACCOUNTS”. The table will have a Reservable numeric column “Balance”. I’ll reduce the balance in one session without committing the transaction, then I’ll try to reduce the balance of the same account from another session where I’ll get an error notifying me there is not enough value to reduce the balance.

1. Create a table (HR.ACCOUNTS) with a Reservable numeric column (BALANCE). The column will have a CHECK constraint to enforce a minimum balance value.

Note: a CHECK constraint is NOT mandatory with a Reservable column.

create table accounts (
acc_id number primary key,
acc_name varchar2(10),
balance number reservable constraint accounts_bal_ck check (balance >= 50)
);

2. Select from USER_TAB_COLUMNS and USER_TABLES views to see information about the lock-free table and reservable column.

select table_name,column_name,reservable_column 
from user_tab_columns where table_name='ACCOUNTS';

select table_name,has_reservable_column
from user_tables where table_name='ACCOUNTS';

3. Find the name of the journal table created when the accounts tables were created with a reservable column.

select table_name,tablespace_name
from user_tables where table_name like 'SYS_RESER%';

4. Perform a description and select from the journal table.

desc SYS_RESERVJRNL_77307
select * from SYS_RESERVJRNL_77307;

5. Insert and commit a row into the ACCOUNTS table and run select.

insert into accounts values (100,'SCOTT',89);
commit;
select * from accounts;

6. Update the row without a commit and select from the journal table. The journal table will have a record of the uncommitted updated row executed in that session.

Note: A select from the accounts table will show the balance value unchanged because the update has not been committed. We are just reserving the balance.

7. Open a new database session and run the same update statement. 

Even if we see the balance is 89, the update will get an error because of the CHECK constraint violation. The first transaction in the first session reserved 25 making only 64 available until the transaction commits or rollbacks, the transaction in session 2 would violate the check constraint.

8. Go back to the first first session and perform a rollback.

9. Go back to the second session and run the update again and commit.

10. Go back to the first session and select the row from the table. With the update and commit in session 2 the balance is now updated. 

11. Query the journal table contents. With no pending truncations there are no rows in the table.