How to Use Lock-Free Reservations in Oracle

Some rows are changed by many transactions at the same time: the seats left on a flight, the stock of a product, the balance of an account. With ordinary updates, each booking locks the row and the next one waits. Lock-free reservations, new in Oracle AI Database 26ai, let such updates proceed concurrently: an update of a reservable column records a reservation instead of locking the row, and the reservations are applied at commit, while constraints are still guaranteed.

Code for This Guide

The main examples are in the examples/transactions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

column type reservable [constraint name check (...)]

update table set column = column {+ | -} expr where primary_key = ...

Updates of a reservable column must add or subtract, and must identify the row by its primary key. Oracle checks the column's constraints against all pending reservations, so no combination of commits can break them.

Concurrent Bookings without Waiting

A flight has ten seats. The main transaction books two; a second, autonomous transaction books three more and commits, without waiting for the first.

Example:

create table seat_inventory (
  flight_id   number primary key,
  seats_left  number reservable constraint seats_left_ck check (seats_left >= 0)
);
insert into seat_inventory values (2800, 10);
commit;

-- this transaction books 2 seats: a reservation, not a row lock ...
update seat_inventory set seats_left = seats_left - 2 where flight_id = 2800;

-- ... so a concurrent transaction can book 3 more without waiting
declare
  pragma autonomous_transaction;
begin
  update seat_inventory set seats_left = seats_left - 3 where flight_id = 2800;
  commit;
end;
/
commit;
select seats_left from seat_inventory where flight_id = 2800;

Output:

Table SEAT_INVENTORY created.

1 row inserted.

Commit complete.

1 row updated.

PL/SQL procedure successfully completed.

Commit complete.

   SEATS_LEFT
_____________
            5

After both commits, five seats are left. With a normal column, the second update would have waited for the first transaction to end. The example drops the table afterward.

Constraints Still Hold

With four seats and three already reserved by an open transaction, a second reservation of three would take the total below zero. It is refused at once.

Example:

update seat_inventory set seats_left = seats_left - 3 where flight_id = 2800;

-- a second booking of 3 would take the seats below zero: the reservation is refused
declare
  pragma autonomous_transaction;
begin
  update seat_inventory set seats_left = seats_left - 3 where flight_id = 2800;
  commit;
end;
/
rollback;

Output:

1 row updated.

declare
*
ERROR at line 1:
ORA-02290: check constraint (NIMBUS.SEATS_LEFT_CK) violated
ORA-06512: at line 4

Rollback complete.

ORA-02290 comes immediately, counting the pending reservation of the other transaction, so overbooking is impossible even though nobody waited.

Things to Know

• A reservable column cannot be set directly to a value while reservations are pending; only additions and subtractions are allowed.
• Only numeric columns can be reservable, and the table needs a primary key.
• Other columns of the same row are still updated with normal locks.

Related Guides

• How to Lock Rows with SELECT FOR UPDATE in Oracle
• How to Find Deadlocks in Oracle

Conclusion

Reservable columns let many transactions add to or subtract from the same row without row locks, applying reservations at commit and checking constraints against all of them. Use them for hot counters such as seats, stock, and balances.