Locking–Lock Free Reservations
In the realm of database management, traditional updates in Oracle databases were once considered the standard method for modifying data within a table.
When 2 session wants to doing update on a sample same record that its was lock by session1, then session 2 was going to hange until session 1 commit or rollback own dml, this role must be work fine because of consistency guaranteed.
But here in 23c you can change this game by Lock-Free Reservations feature.
Lock-Free Reservations enable concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free Reservations are held on the rows instead of locking them. It verifies if the updates can succeed and defers the updates until the transaction commit time. Lock-Free Reservations improves the user experience and concurrency in transactions.
This innovation fosters a more streamlined and responsive data processing environment, allowing for faster and more seamless data modifications without compromising data integrity. Lock-free reservations have revolutionized the way databases handle updates, providing a more optimized and scalable solution in the present digital landscape.
Here, I introduces the concept of lock-free reservations.
Using the same three Session, we will now perform updates on the inventory_reservations table. In Session 1, we will decrease the budget of a record by 200 without committing. In Session 2, we will decrease the same record by 200, and unlike before, the session will not hang. In Session 3, we will attempt to decrease the record by 200, going below the "threshold," which will result in a session error. We will then commit the changes in Session 1 and rollback the changes in Session 2.
Finally, we will run the transaction again in Session 3, which should succeed because we have restored the reserved amount in the budget column.
Note: Using the same 3 session.
Session 1
update table (inventory_reservations) and decrease the record by 200.
Do not commit.
UPDATE s1.inventory_reservations
SET budget = budget - 200 where ID = 1;
Session 2 update table and decrease the same record by 200.
UPDATE s1.inventory_reservations
SET budget = budget - 200
WHERE ID = 1;
COMMIT;
Note: Notice how the session does not hang like it did using old normal updates in the last task. This is due to the concurrency Lock-Free enables for dba user experience.
Commit Session 1
COMMIT;
In Session 3 update t2 and decrease the same record by 300 going below the “threshold” and the session errors because we go below 400. In lock-free every user needs to stay above the constraint.
UPDATE s1.inventory_reservations
SET budget = budget - 300
WHERE ID = 1;
Execute this statement in Session 2 to give back the 200.
UPDATE s1.inventory_reservations
SET budget = budget + 200
WHERE ID = 1;
COMMIT;
Go to Session 3 and run the transaction again and it should succeed because we gave back the 200 to the reserved column, budget.
UPDATE s1.inventory_reservations
SET budget = budget - 200 where ID = COMMIT;
Congratulations on completing the Lock-Free Reservations feature !
You have experienced firsthand how this innovative feature enhances database performance by enabling concurrent updates to specific columns without session hanging.
With lock-free reservations, you can ensure smoother and more efficient operations, providing a seamless user experience.
Keep leveraging these powerful capabilities to optimize your database management and stay ahead in your Oracle journey!
Can we use this feature in ticket sales and reservations system?
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.
Regards,
Alireza Kamrani
Senior Database Consultant.
