Lock-Free Reservation 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. This includes applications such as bank account balance based on debit and credit transactions, inventory control, supply chain, financial or investment banking, stocks, travel, and entertainment.
In these applications, the likelihood of many transactions concurrently accessing numeric data is high, and often this data is identified as a “hot” resource because applications continuously, and repeatedly, read or update such data. In long-running transactions such as microservices applications, a resource remains locked for an extended period, potentially making it a hot resource. The long-term resource locking limits concurrency, and for many business applications blocking concurrent accesses to “hot” data can severely impact performance, reducing user experience and throughput.
By avoiding the traditional locking mechanism during updates, Lock-Free reservation allows you to greatly improve on the user experience with reduced blocking in the presence of frequent concurrent updates to numeric data.
How Concurrency is Typically Handled – an Example
In previous 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.
For example, a customer’s shopping cart has items added to the cart before being sold, in this case, the customer wants to purchase a smart watch and has placed it in their shopping cart but is still deciding if they really want to complete the purchase.
This shopping application is able to handle multiple transactions at various states like when a user puts an item to the cart, but without Lock-Free reservation, the row is locked while the customer ponders their purchase.
Because of the row lock, the remaining watches, of that particular item, in the store are not available to other buyers (for some amount of time) and cannot be sold to those customers still waiting to make a purchase – who then may look to other sites to make their purchase or who may take to social media to comment on their user experience.
How Lock-Free Reservation Can Help
With Lock-Free reservation, you could instead allow transactions to concurrently add or subtract from the same reservable column without blocking each other by specifying the conditions for which the updates may proceed. This is accomplished by specifying that the numeric column is a RESERVABLE column and by creating a CHECK constraint for the column to ensure a sufficient quantity of the item is available before allowing the transaction to perform the reservation.
Additional throughput improvement may also be achieved because the reservable column updates do not lock the rows and hence do not block another transaction from updating non-reservable columns of the same row concurrently.
Overview of Reservable Columns, Reservation Journals and Check Constraints
The Lock-Free Reservation feature is enabled by default. A Lock-Free Reservation parameter, named lockfree_reservation, is provided at the PDB level, and the default value of this system level parameter is ‘ON’. The Lock-Free Reservation feature can be disabled on a per PDB basis using the parameter as follows:
ALTER SYSTEM SET lockfree_reservation = OFF;
To use Lock-Free Reservation, use the RESERVABLE keyword to declare a RESERVABLE column when you CREATE or ALTER a table. Lock-Free reservation concurrency is offered on columns with numeric data types.
For example:
CREATE TABLE Account( ID NUMBER PRIMARY KEY, Name VARCHAR2(10), Balance NUMBER RESERVABLE CONSTRAINT minimum_balance CHECK (Balance >= 50))
To identify a potential reservable column, look for hot resources with numeric aggregate data that could benefit from improved concurrency.
The creation of a table with the RESERVABLE column automatically creates an associated reservation journal table. The reservation journal table is created under the same user schema and in the same tablespace as the user table.
When a transaction issues an update operation on a reservable column, the reservable update is tracked as a lock-free reservation in a reservation journal. The transaction update does not lock the row (that has the reservable update) but indicates an intention to modify (add or subtract) the reservable column in the row by a delta amount.
The modification amount is reserved and promised so that the transaction may proceed without waiting on other uncommitted and concurrent transactions. The reservation enables other concurrent transactions to issue reservable updates to the same row.
You can have reservable columns in column-level or table level CHECK constraints. A CHECK constraint lets you specify a condition that each row in the table must satisfy.
For example:
CREATE TABLE Account( ID NUMBER PRIMARY KEY, Name VARCHAR2(10), Balance NUMBER reservable, Earmark NUMBER, Limit NUMBER, CONSTRAINT minimum_balance CHECK (Balance + Limit – Earmark >= 0));
You can also define reservable columns without constraints – for such reservable columns all lock-free reservations are successful. The concurrent updates still proceed in parallel without locking rows.
Example Use Case
The following example creates a table inventory with a reservable column “qty_on_hand” and check constraints.
CREATE TABLE inventory
(item_id NUMBER CONSTRAINT inv_pk PRIMARY KEY,
item_display_name VARCHAR2(100) NOT NULL,
item_desc VARCHAR2(2000),
qty_on_hand NUMBER RESERVABLE CONSTRAINT qty_ck CHECK (qty_on_hand >= 0),
shelf_capacity NUMBER NOT NULL, CONSTRAINT shelf_ck CHECK (qty_on_hand <=
shelf_capacity));
The CHECK constraint on qty_on_hand specifies that there must be zero or more items in stock and not more than the shelf capacity for this item for an update on qty_on_hand to proceed.
Now the example will INSERT some values,
INSERT INTO inventory VALUES (123, ‘Milk’, ‘Lowfat 2%’, 100, 120);
INSERT INTO inventory VALUES (456, ‘Bread’, ‘Multigrain’, 50, 100);
INSERT INTO inventory VALUES (789, ‘Eggs’, ‘Organic’, 50, 75);
Inventory Table after INSERTS.
ITEM_ID ITEM_DISPLAY_NAME ITEM_DESC QTY_ON_HAND SHELF_CAPACITY
———– ———————————– ———————– ———————– ———————
123 Milk Lowfat 2% 100 120
456 Bread Multigrain 50 100
789 Eggs Organic 50 75
Next, the example will perform multiple concurrent UPDATEs to the RESERVABLE column.
Transaction 1: UPDATE inventory SET qty_on_hand = qty_on_hand – 10 WHERE item_id = 123;
Transaction 2: UPDATE inventory SET qty_on_hand = qty_on_hand + 20 WHERE item_id = 123;
Transaction 3: UPDATE inventory SET qty_on_hand = qty_on_hand – 30 WHERE item_id = 123;
None of the CHECK constraints are violated in this example so the concurrent transactions are successful in making their lock-free reservations.
UPDATE transactions commit.
Transaction 2: commit;
Transaction 3: commit;
Transaction 1: commit;
Notice that the order of the transaction commits in this example was 2, then 3, then 1. This is due to the Lock-Free reservation deferring lock acquisition until commit allowing the UPDATE transactions to commit as, and when, they are ready. Compare this to the same operation without Lock-Free reservation, where transaction 2 would have to wait for transaction 1 to release its row lock before it could update and then commit and likewise, transaction 3 would have to wait for transaction 2 to commit.
A pending lock-free reservation that was approved at the time that an update is issued may later violate such a constraint at the commit time of the transaction. This can happen if the non-reservable columns of the table level constraint had been updated in the duration after the reservation had been made such that their current values may violate the CHECK constraint.
The transaction will have to be terminated if the constraint is violated. However, the non-reservable columns in a table level constraint are typically thresholds which are modified very infrequently.
Benefits of Lock-Free Reservation
Applications can benefit if there is improved concurrency with reduced isolation while maintaining the atomicity, consistency, and durability properties of transactions.
Improves Concurrency
- Improves concurrency on updates with highly contended numerical values.
- Lock-Free reservation, on reservable columns, enables concurrent transactions to reserve an amount from the reservable column’s value, without locking the row.
- Hold locks for a very short duration as opposed to transaction duration locks.
Improves OLTP Scalability
- A lock-free reservation request is honored if the new reservation can be allowed in the mix of concurrent reservations.
- The actual row modification, and lock acquisition, is deferred to the time of transaction commit.
- Helps OLTP scaling in applications with highly contended numeric values, such as account balance, item inventory, etc.
Improves User Experience
- Improves user experience as concurrent updates to reservable columns, of hot rows, can simultaneously proceed without being blocked.
- Beneficial in applications that provide inventory control, supply chain, financial or investment banking, stocks, travel, entertainment etc. which often operate on “hot” numeric aggregate data.
Summary
With Lock-Free Reservation, hot resources do not cause concurrency bottlenecks. Applications such as inventory control, banking, shopping cart, billing and payments, retail, supply chain, ticketing and reservation management will benefit from this feature.
For more information, and additional usage details and examples, please see the Oracle documentation [here].