Lock-Free Reservations in Oracle AI Database 26ai: One Hot Account, 8 Sessions, Measured

Tested on: Oracle AI Database 26ai (23.26.3) on Oracle Autonomous AI Database | Level: Intermediate

Many sessions updating one row is a classic hot spot in banking systems: a merchant settlement account, a fee account, a branch cash balance. Every debit locks the row until commit, so concurrent payments queue behind each other. Oracle AI Database 26ai offers Lock-Free Reservations for exactly this case. I tested it against a normal column with 8 sessions making 1,600 payments on one account: the load ran 5.4 times faster and row lock wait time fell from 71.68 seconds to 0.18 seconds, with the same correct final balance.

Results at a glance

8 sessions × 200 payments, one account	Normal column	Reservable column
Elapsed time	17.2 s	3.2 s
enq: TX - row lock contention	6,828 waits, 71.68 s	128 waits, 0.18 s
Payments recorded	1,600	1,600
Amount debited	exactly 1,600	exactly 1,600

The problem: one row, many sessions

When a session runs UPDATE accounts SET balance = balance - 1 WHERE account_id = 1, Oracle locks that row until the transaction commits or rolls back. Any other session that wants to update the same row waits on enq: TX - row lock contention, even if the balance easily covers both debits.

In a real payment transaction the row is not held for microseconds. Between the update and the commit there is other work: writing a ledger entry, a fraud check, posting to another system. The longer that work takes, the longer every other session waits, and the hot account becomes a single queue.

How Lock-Free Reservations work

You mark a numeric column as RESERVABLE. An update on that column no longer locks the row. Instead, Oracle records the requested change as a reservation in a journal table it creates automatically. The real row is changed only when the transaction commits. Before accepting a reservation, Oracle checks the column's constraints against the committed value plus all pending reservations, so an overdraft is still impossible.

The rules, all of which I verified in the lab:

• The column must be numeric and the table must have a primary key.
• Updates must be relative: balance = balance + n or balance = balance - n. Setting a fixed value fails with ORA-55746.
• The journal table is named SYS_RESERVJRNL_<object_id>.

Lab setup

Two identical tables, one with a normal balance column and one with a reservable balance column. Both have a check constraint that the balance can never go below zero:

CREATE TABLE lfr_accounts_std (
  account_id NUMBER CONSTRAINT lfr_accounts_std_pk PRIMARY KEY,
  owner_name VARCHAR2(50) NOT NULL,
  balance    NUMBER NOT NULL
             CONSTRAINT lfr_std_balance_ck CHECK (balance >= 0)
);

CREATE TABLE lfr_accounts_rsv (
  account_id NUMBER CONSTRAINT lfr_accounts_rsv_pk PRIMARY KEY,
  owner_name VARCHAR2(50) NOT NULL,
  balance    NUMBER RESERVABLE NOT NULL
             CONSTRAINT lfr_rsv_balance_ck CHECK (balance >= 0)
);

The data dictionary confirms the reservable column and the journal table Oracle created:

SELECT table_name, column_name, reservable_column
FROM   user_tab_cols
WHERE  table_name = 'LFR_ACCOUNTS_RSV' AND column_name = 'BALANCE';

SELECT table_name FROM user_tables WHERE table_name LIKE 'SYS_RESERVJRNL%';

TABLE_NAME       COLUMN_NAME RESERVABLE_COLUMN
---------------- ----------- -----------------
LFR_ACCOUNTS_RSV BALANCE     YES

JOURNAL_TABLE
---------------------
SYS_RESERVJRNL_150287

Test 1: only relative updates are allowed

UPDATE lfr_accounts_rsv SET balance = 500 WHERE account_id = 2;
UPDATE lfr_accounts_rsv SET balance = balance + 0 WHERE account_id = 2;

ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.

1 row updated.

Test 2: two customers, one account

Account 2 starts with a balance of 100. The first customer debits 60 and does not commit. While that transaction is still open, a second customer, running in its own autonomous transaction, tries to debit the same account.

With the normal column, the second customer must wait for the first customer's row lock. In this single-session test Oracle detects that the autonomous transaction is waiting on its own parent and raises a deadlock at once:

First customer: debited 60, NOT committed
Second customer: ORA-00060: deadlock detected while waiting for resource

With the reservable column, nobody waits (journal columns trimmed for width):

First customer: reserved 60, NOT committed
Balance seen by first customer before commit: 100
Journal table: SYS_RESERVJRNL_150287
ORA_STATUS$  ORA_STMT_TYPE$  ACCOUNT_ID  BALANCE_OP  BALANCE_RESERVED
ACTIVE       UPDATE          2           -           60
Second customer tries 60: ORA-02290: check constraint (ADMIN.LFR_RSV_BALANCE_CK) violated
Second customer tries 30: debit of 30 committed
Balance seen by first customer after second customer committed: 70
First customer committed. Final balance: 10

Three things stand out:

• No overdraft, and no waiting to find out. A second debit of 60 would have taken the balance to -20 once both committed, so Oracle rejected it immediately with ORA-02290, while the first debit was still uncommitted. A debit of 30 fitted and committed at once.
• The reservation lives in the journal. The pending debit appears as one ACTIVE row with BALANCE_RESERVED = 60. After all transactions finished, the journal was empty.
• A session does not see its own reservation. After reserving 60, the first customer still read a balance of 100, not 40. The real row changes only at commit. Application code that reads the balance back after debiting, inside the same transaction, must take this into account.

Test 3: 8 sessions, one hot account

Eight scheduler jobs each made 200 payments on account 1. Each payment debits 1, writes a row to a transaction log, does 10 ms of other work, then commits:

UPDATE lfr_accounts_rsv SET balance = balance - 1 WHERE account_id = 1;
INSERT INTO lfr_txn_log (label, account_id, amount) VALUES ('RSV', 1, -1);
DBMS_SESSION.SLEEP(0.01);   -- 10 ms of other work inside the transaction
COMMIT;

If the 1,600 payments ran strictly one after another, the 10 ms of work alone would take 16 seconds. Wait events were captured before and after each run from GV$SYSTEM_EVENT:

TEST_LABEL ELAPSED_SECONDS
---------- ---------------
STD                   17.2
RSV                    3.2

TEST_LABEL ROW_LOCK_WAITS ROW_LOCK_SECONDS
---------- -------------- ----------------
STD                  6828            71.68
RSV                   128             0.18

TAB BALANCE
--- -------
STD  998400
RSV  998400

With the normal column, the load took 17.2 seconds, close to the fully serialised 16 seconds: the 8 sessions effectively ran one at a time. Together they spent 71.68 seconds waiting for the row lock. With the reservable column, the same work finished in 3.2 seconds and row lock wait time was 0.18 seconds. Both tables ended with exactly 1,600 debited, so no update was lost.

The reservable test still shows 128 short row lock waits. That fits how the feature works: the reservation is applied to the real row at commit, which takes a brief lock. The lock is now held for the commit only, not for the whole transaction.

Things to know before using it

• Fixed-value updates fail, including your own fixes. When I reset the test data with SET balance = 1000000, it failed with ORA-55746 too. Data-fix scripts, support procedures and batch jobs that set a balance directly must be rewritten as relative updates, or the column must be switched back with ALTER TABLE ... MODIFY (balance NOT RESERVABLE) first.
• Read-after-write inside a transaction changes. A session does not see its own pending reservation. Check any code that debits and then reads the balance back before committing.
• The gain depends on how long the row is held. This test held each transaction open for 10 ms on one hot row. Short transactions or rows that are rarely shared will gain less. Measure with your own workload.
• Monitor the right view. In this Autonomous Database sandbox, V$SYSTEM_EVENT did not show the row lock waits, while GV$SYSTEM_EVENT did. I did not confirm why, so when measuring, check both.

Clean-up

DROP TABLE lfr_accounts_std PURGE;
DROP TABLE lfr_accounts_rsv PURGE;
DROP TABLE lfr_txn_log PURGE;

Summary

For a hot account updated by many sessions, Lock-Free Reservations in Oracle AI Database 26ai removed nearly all row lock waiting in my test: 1,600 payments finished in 3.2 seconds instead of 17.2, with the same exact result and the check constraint still preventing overdrafts. The trade-offs are real but manageable: updates must be relative, a session does not see its own reservation before commit, and existing scripts that set balances directly need review. If your system has hot balance rows, this is worth testing.

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox.

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.