anas-dbcloud: 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 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.
Table must have a primary key.
A Reservable column cannot be part of 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 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 a 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 was created with a reservable column.

select table_name,tablespace_name

from user_tables where table_name like 'SYS_RESER%';

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

desc SYS_RESERVJRNL_77307

select * from SYS_RESERVJRNL_77307;

5. Insert and commit a row into 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 for the uncommitted updated row executed in that session.

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

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

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