Oracle Database 23c–Fine Grained Locking–Lock Free Reservations

This should surprise if not baffle you. Session 1 inserts a record and commits it. Session 1 performs an update on this record. And does not yet commit its transaction. Session 2 tries to update that same record …. and is able to! It does not run into a lock. It can perform the update, almost as if session 1 does not exist.

The traditional behavior of the Oracle Database is to lock a row as soon as it is updated. Some databases lock a table or a page (set of records) but from its incarnation, Oracle Database has always been very fine grained by only locking the row in question. However, this example shows that in release 23c it can lock even less than a single record. It does lock – make no mistake.

When I introduce a third session and perform two more updates, you will see that the these transactions are not isolated from each other. It is not wild west, free for all. There are still rules enforced.

Step 4 in session B selects the current capacity and finds that the value in column capacity is still at the original 2000 despite the update in session B just prior to this query that took 800 from that capacity.

Step 5 in session C reduces the capacity by another 500 – although a query for the current capacity in that session would also return the original 2000. This update is accepted – again while both sessions A and B have made their own claims on that ticket capacity. Still no row lock preventing session C from making the update. However in step 6, when session C tries to get hold of another 700 (tickets), we finally run into a limitation: a Check Constraint violation. Not shown yet: there is a Check Constraint defined against table ticketsales that prevents the value of capacity from getting below 10.

Despite the fact that the value of column capacity for this record is still at 2000, there are claims (“reservations”) from session A (200), session B (800) and session C (500) that have virtually drained 1500 from that original 2000, meaning that the 700 that was attempted to take out in step 7 were no longer available.

Let’s now commit session A (step 8) – definitively getting hold of the 200- , rollback session B (step 9) – releasing the claim of 800 – and trying to claim those 700 tickets again in session C (step 10).

What we have seen in action here is Lock-Free Reservations. A feature that allows us to have the Oracle Database manage pools of resources – tickets, seats, account balance, lottery tickets – without locking database records. To have multiple transactions each take out a claim on a slice of the resource pool, they do not have to lock the record that describes that particular pool – thereby excluding other transactions until the transaction is either committed or rolled back. They can instead create a reservation, that guarantees that the slice they requested is available by the time they commit. Oracle Database makes sure that not more reservations can be created than the pool can sustain. Many transactions can hold reservations on the same pool [record] at the same time: they do not interfere. The allows for a much higher degree of concurrency than is possible with table, page or even record locking.

Working with a resource pool with lock-free reservations is quite straightforward:

• define a column as reservable. This must be a numeric column
• define check constraint(s) to control the allowable values for the column (usually limiting the lower or upper capacity limit; note: check constraints can compare non reservable (regular) columns with reservable columns
• access the record to be updated using its primary / unique key – make sure the update is a single row statement
• do not use for update of when updating the reservable column’s value (as that would defeat the purpose)
• only use set column = column + claim or set column = column – claim to claim part of the capacity; do not use set column = value.
• the pool can be replenished; capacity can be added for example. However, the transaction that adds capacity needs to be committed before it will have an effect on additional reservations that can be made by other transactions
• Oracle Database creates a “Reservation Journal Table” – SYS_RESERVJRNL_<object_number_of_base_table> – that records the claims made against a resource pool. This table behaves like a global temporary table: each session only sees its own claims. When the session commits (or rolls back) the table is cleared. Flashback query does not return values from this table and even SYS cannot look across sessions to find all currently held reservations

In very brief the definition of the table used in this example:

CREATE TABLE TICKETSALES(

ID NUMBER PRIMARY KEY,

NAME VARCHAR2(100),

CAPACITY NUMBER RESERVABLE CONSTRAINT MINIMUM_CAPACITY CHECK (CAPACITY >= 10)

);

Column CAPACITY is defined as reservable and it appear in the check constraint minimum_capacity that enforces the rule that the capacity should never be lower than 10.

The Reservation Journal Table created in this case:

(where 78947 is the OBJECT_ID found for table TICKETSALES in a query against USER_OBJECTS WHERE OBJECT_NAME=’TICKETSALES’).

Here is an example of the information available to the current transaction regarding its reservations:

These Lock-Free Reservations are among the most intriguing application development oriented features in the latest Oracle Database release. Transactions vying for the resources from the same collection – without heavy handedly locking each other out on a record lock. It seems quite elegant and useful. There is of course much more to be looked into. (When) do row level triggers fire for example. Can we define triggers on the journal table – and would that ever make sense? How can we find out what the maximum claim is a transaction can make? Just trying until we hit a limit? Can we intercept ‘release of a claim’ or ‘replenishment of a resource’?

I am looking forward to trying out more with these reservations. Good stuff.

Note: you have read an article in a series on new features in Oracle Database 23c – released as Developer release early April 2023. This series provides more insight in some of the most eye catching features in this new major release.

Resources

Do you want to explore Oracle Database 23c Free – without installation troubles and without Oracle Cloud account? Read this article on how to get going quickly, easily and for free (using a Gitpod workspace): Get Going with Oracle Database 23c Free.

Oracle Documentation – Lock-Free Reservations