Lock-Free Reservations in Oracle 26ai: Comparing Traditional Locking and High-Concurrency Transactions

 

Introduction

Handling concurrent updates has always been a challenge in high-volume transactional systems. Consider a banking application where hundreds of transactions attempt to credit or debit the same account at the same time. With traditional row-level locking, once one session updates the account balance, other sessions must wait until the transaction commits or rolls back. As concurrency increases, this waiting can become a bottleneck, resulting in lock contention, increased response times, and events such as enq: TX - row lock contention.

One of the newer Oracle features designed to address this problem is Lock-Free Reservations. Instead of relying on immediate row locking for certain numeric updates, it allows concurrent transactions to reserve resources while preserving transactional consistency.

In this article, I will compare traditional row-level locking with Lock-Free Reservations using practical examples and see how each approach behaves under concurrent load.

This article is part of a small series on modern concurrency features in Oracle 26ai, including Lock-Free Reservations, transaction prioritization, and value-based concurrency control.

1. The Traditional Way (Pessimistic Locking)

Before looking at Lock-Free Reservations, let's first examine the traditional row-level locking behavior used by Oracle transactions. While this mechanism guarantees data consistency, it can become a bottleneck when multiple sessions attempt to update the same row concurrently.

For this demonstration, we'll use the HR.EMPLOYEES table and treat the SALARY column as a simple account balance. The goal is to show how concurrent updates to the same row result in blocking and lock contention.

To make the behavior easier to observe, I opened three SQL*Plus sessions connected to FREEPDB1 using the HR user:

• Session 1 (white terminal) performs the first update and holds the lock.
• Session 2 (black terminal) attempts to update the same row and becomes blocked.
• Monitoring Session (yellow terminal)  used to monitor locks, waits, and session activity.

Let's see what happens when both sessions attempt to update the same employee record. 

The employee with EMPLOYEE_ID = 100 initially has a salary of 26,900.

In Session 1, at 10:41:28, I executed the following update:

UPDATE hr.employees 
SET salary = salary + 500 
WHERE employee_id = 100;

The statement was completed immediately, and within Session 1 the salary became 27,400. However, the transaction was intentionally left uncommitted, meaning Oracle continued to hold an exclusive lock on the row.

Ten seconds later, at 10:41:38, I opened Session 2 and attempted to update the same row:

UPDATE hr.employees
SET salary = salary + 200
WHERE employee_id = 100;

This time, the statement did not return. The session became blocked and waited indefinitely because Session 1 was still holding the row lock.

To confirm the blocking situation, I queried the monitoring session and checked the BLOCKING_SESSION information. The query showed that Session 2 was waiting on Session 1, confirming a classic row-lock contention scenario.

Session 2 remains blocked and waits on enq: TX - row lock contention until Session 1 COMMIT or ROLLBACK the transaction.

2. The Modern Way (Lock-Free Reservations)

Let's repeat the same test using Lock-Free Reservations. Instead of relying on traditional row-level locking for every update, Oracle allows concurrent transactions to reserve changes against a designated numeric column and validates those reservations during transaction processing.

2.1. Enable Lock-Free Reservations

To make a column eligible for lock-free updates, it must be defined as RESERVABLE.

 ALTER TABLE hr.employees MODIFY (salary RESERVABLE);

My first attempt failed with the ORA-02296: cannot enable (HR.) - null values found error:

For Oracle to track concurrent mathematical adjustments (like adding $500 or subtracting $200) without locking the row, the starting value must be a concrete number. Mathematically, NULL + 500 is still NULL, which completely breaks the background reservation logic.

Because of this, Oracle strictly enforces that any column marked as RESERVABLE must also be explicitly constrained as NOT NULL.

To identify the problem, I checked for rows with a missing salary value:

After identifying rows with NULL salaries, I attempted to replace them with zero, this time failed with the ORA-02290 check constraint HR.EM_SALARY_MIN viodated:

Instead of setting missing values to 0, look up the minimum allowed salary in the constraint, or simply use a safe default value that complies with the schema rules (such as 1 or the minimum threshold, which is typically 1 in standard HR schemas).

After correcting the data, I successfully enabled the feature by making the column both NOT NULL and RESERVABLE:

UPDATE hr.employees
SET salary = 1
WHERE salary IS NULL;

COMMIT;

ALTER TABLE hr.employees MODIFY (salary NOT NULL);

ALTER TABLE hr.employees MODIFY (salary RESERVABLE);

Behind the scenes, Oracle maintains reservation information separately from the base row, allowing concurrent transactions to record pending adjustments without immediately locking the target row.

2.2. Simulate Concurrent Updates (No Blocking)

Go back to the two SQL*Plus sessions used in the previous test. Ensure any previous transactions have been rolled back before continuing.

Session 1

Add $500 to employee 100.

UPDATE hr.employees
SET salary = salary + 500
WHERE employee_id = 100; 

While Session 1 remains uncommitted, querying the row again in the same session still shows the original salary value of 26,900. Although the UPDATE statement successfully reserves an additional 500, the base column value is not immediately modified. The reserved adjustment remains pending until the transaction is committed.

This behavior differs from the traditional row-locking approach demonstrated earlier. In that case, the same query executed in Session 1 immediately reflected the change, showing the updated salary of 27,400 even before the transaction was committed.

Session 2

While Session 1 remains uncommitted, execute the following statement on Session 2:

 -- Add $200 to the same employee
UPDATE hr.employees
SET salary = salary + 200
WHERE employee_id = 100;

In Session 2, unlike the traditional locking example, the UPDATE statement also completes immediately. The session does not block and does not wait for Session 1 to commit.

If we query the row in Session 2 after the update, the salary still appears as 26,900. Although Session 2 has successfully reserved an additional 200, the base column value is not immediately updated. At this point, both Session 1 and Session 2 have pending reservations against the same row, but neither reservation is reflected in the displayed salary until the transactions are committed.

If we check the monitoring session, we can see that there is no row-level lock or blocking session reported for this operation. Unlike the traditional approach, the sessions are able to proceed concurrently without triggering enq: TX - row lock contention, even though both transactions are modifying the same row.

3.2. Finalize the Transactions

Session 1 reserved +500 and Session 2 reserved +200. Once both transactions committed, Oracle applied the reservations, and the final salary became 27,600 (26,900 + 500 + 200).

Session 1: COMMIT;

Session 2: COMMIT;

After both transactions commit successfully, Oracle applies both adjustments and the final salary reflects the combined changes from the two transactions.

4. Head-to-Head Comparison

To understand when to use Lock-Free Reservations, it helps to compare it directly with traditional row-level locking using the same salary/bank balance example.

Metric	Traditional Locking	Lock-Free Reservations
Column Setup	Standard column	RESERVABLE column
Concurrency Model	Pessimistic locking (row locked during update)	Reservation-based updates (no immediate row lock for conflict cases)
Row Contention Behavior	One session blocks others on the same row	Sessions proceed concurrently without blocking
Wait Event	enq: TX - row lock contention	No blocking wait for same-row numeric updates
Update Visibility (Uncommitted)	Visible only within the session; base row changes immediately in session	Base value remains unchanged; updates are tracked as reservations
Commit Behavior	Single transaction applies final value	Multiple reservations are merged at commit time

Conclusion

In this article, we saw the difference between traditional row locking and Lock-Free Reservations in Oracle for high-concurrency updates.

With the traditional method, concurrent updates on the same row cause blocking, and we clearly saw enq: TX - row lock contention, which reduces performance when many sessions try to update the same record.

With Lock-Free Reservations, updates can run in parallel without blocking. Each session records its own reservation, and the final value is calculated at commit time.