Oracle Database 23c - Lock-Free Reservations to Prevent Blocking Sessions
When multiple concurrent sessions/users are modifying or updating the same rows for example shopping cart multiple sessions and users are keeping the same product into the cart. This will reserve the quantity for their session until the payment is not completed. If you allow concurrent transactions to access data, you must control the transactions to preserve application correctness. Multiple save points need to be created in application logic.
Let's take an example of a product inventory table.
As you can see here, we have a product_inv table. It has 6 rows out of which the first record is going to be updated by session 1 and Also session 2 has initiated the transaction on the same row.
Session 2 is in waiting mode as session 1 has not completed the transaction. Neither commit nor rollback done.
For developers, such a situation needs to be handled at application level by making save points or making some global variables or in session cache.
In Oracle Database 23c, new feature "Lock free reservation" helps you to update the same row without blocking your session. Lock-Free Reservation provides an in-database capability for transactions and operates on so-called reservable columns. It enables concurrent transactions to proceed without being blocked on updates made to reservable columns.
Let's make prod_qty column reservable by using below command,
alter table product_inv modify (prod_qty reservable);
Once you done, now concurrent session can update the same row as shown in below example.
To make this example more realistic, let's add the check constraint so that no one can reserve more than available quantity.
For example, We do have a total 10 quantities, out of that session 1 has reserved the 8 quantities. What if session 2 is reserving 3 quantities which are out of the availability. Hence check constraint is required.
Let's look below snapshot where it is giving an error in session 2 while reserving the 3 quantities.
Some facts about Lock Free Reservations as below,
- Only numeric (number) columns can be marked as reservable columns.
- You can only do + or - (increase or decrease) operation on a reservable column. Cannot modify column like prod_qty=5 with literal value to update the column.
- You can either update the reservable columns or non-reservable columns. Both cannot be updated in the same statement.
- Your updated transaction is reservable until you commit or rollback. In the above example of updating the product quantity is reserved for that particular session. Other sessions can reserve quantity based on the availability of product quantity.
Benefit of Lock Free Reservations is, it reduces the headache of the developers to write code and maintain each session's value in application.
Thanks & Regards,
Chandan Tanwani
