blogs.oracle.com
Open in
urlscan Pro
2a02:26f0:e300:187::a15
Public Scan
URL:
https://blogs.oracle.com/coretec/post/lock-free-reservation-in-23c
Submission: On September 24 via api from DE — Scanned from DE
Submission: On September 24 via api from DE — Scanned from DE
Form analysis 1 forms found in the DOM
<form class="scs-search-form" data-bind="attr: {id: searchId}" role="search" onsubmit="return triggerContentListComponents()" id="scs-search-b6b22ef4-4f69-4c15-a57d-7e1bf9689381">
<input class="scs-search-input" data-bind="value: queryString, attr:{ 'data-show-icon': showSearchIcon, style: computedStyle, placeholder: placeholderText, 'aria-label': placeholderText}" type="text" data-show-icon="true" style=""
placeholder="Enter Search..." aria-label="Enter Search...">
<button class="scs-search-button" data-bind="visible: showSearchIcon, 'click': triggerContentListComponents, attr: {'aria-label': placeholderText}" aria-label="Enter Search..."></button>
</form>Text Content
* Skip to content
* Accessibility Policy
Oracle
Database & Cloud Technology Blog
Search
Exit Search Field
Clear Search Field
Select Language
*
Menu
CATEGORIES
* 23ai
* Advisor
* Analytics
* Autonomous Database
* Cloud
* Database Management
* Development
* English Content
* JSON
* Security
* Spatial & Graph
* SQL, PL SQL und mehr
* Blogs Home
* Blogs Directory
* Featured Authors
* RSS
Database & Cloud Technology Blog
Follow:
* RSS
* Facebook
* Twitter
* LinkedIn
* Youtube
* Instagram
* 23ai,
* Database Management,
* English Content,
* SQL, PL SQL und mehr,
LOCK-FREE RESERVATION: HOW TO START WITH
May 23, 2023 | 11 minute read
Ulrike Schwinn
Distinguished Data Management Expert
Stephane Duprat
Technical License Eng Specialist
Text Size 100%:
－ ＋
What is lock-free Reservation in 23ai and how can it be used? In this posting we
will illustrate first steps in using lock-free reservation.
First let's describe a possible use case. Transaction processing usually
involves transactions with data updates that replace old values with new. For
example, an application handles multiple transactions at various states sucha
as when a user puts an item into the cart, the item becomes unavailable to other
buyers and yet is unsold. If there are multiple concurrent transactions adding
items to the cart and checking out or abandoning the cart, the field must be
locked for a transaction before a commit or rollback can change the quantity.
Locking data for long periods prevents other concurrent transactions from
modifying the item until the lock is released. If you allow concurrent
transactions to access data, you must control the transactions to preserve
application correctness. However serialization will block other concurrent
transactions from modifying the row until the transaction that initiated the
update is completed.
How can we solve this?
The new capability in 23ai to assign a priority low or medium to a transaction
(see blog posting Priority Transactions with high, medium and low priority) does
not help here because it can only terminate the low priority sessions after a
certain wait time.
But what about lock-free reservation in 23ai? 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.
In general, you can find more information in
* Database Development Guide Using Lock-Free Reservation
* Concepts Guide Lock-free Reservation.
How does it work? Lock-free reservation allows transactions to concurrently add
or subtract from the same row’s 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
creating a CHECK constraint for the column. 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.
So, let's illustrate the feature with a simple example to get an idea how it
works.
First let's create a table INVENTORY with the reservable column QTY_ON_HAND.
Keep in mind that only numeric data types are supported and reservable column
property can only be specified for a column on a table that has a primary key.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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) NOT NULL,
shelf_capacity NUMBER NOT NULL,
CONSTRAINT shelf_ck CHECK (qty_on_hand <= shelf_capacity)
)
A reservable column may be also added to a table using the ALTER TABLE command
and a non-reservable column may be converted to a reservable column using the
ALTER TABLE command and vice versa. In addition, the usage of a constraint is
optional. The transaction, based on the constraints placed on the reservable
column, decides whether the quantity is sufficient to make the update. In our
example we want to make sure that the value in QTY_ON_HAND is always positive.
With and ALTER TABLE command you can change the column to NOT RESERVABLE again.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> alter table inventory modify (qty_on_hand NOT RESERVABLE);
Table altered.
SQL> drop table inventory;
Table dropped.
You can get an overview concerning the restrictions in Database Development
Guide 29.5 Guidelines and Restrictions for Lock-Free Reservation.
How can we monitor the creation and usage of reservable columns? Let's assume we
created the table INVENTORY again as shown above.
Let's query the data dictionary view USER_TABLES and USER_TAB_COLS and
investigate the new columns for this feature.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> col table_name format a30
SQL> col has_reservable_column format a30
SQL> col reservable_column format a30
SQL> select table_name, has_reservable_column
from user_tables
where table_name = 'INVENTORY';
TABLE_NAME HAS_RESERVABLE_COLUMN
------------------------------ ---------------------
INVENTORY YES
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select column_name, reservable_column
from user_tab_cols
where table_name = 'INVENTORY' and reservable_column = 'YES';
COLUMN_NAME RESERVABLE_COLUMN
------------------------- -----------------
QTY_ON_HAND YES
As usual more information about the constraints can be found in
USER_CONSTRAINTS:
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> col search_condition format a40
SQL> col constraint_name format a20
SQL> select constraint_name, search_condition
from user_constraints
where table_name='INVENTORY';
CONSTRAINT_NAME SEARCH_CONDITION
-------------------- ----------------------------------------
SYS_C008386 "ITEM_DISPLAY_NAME" IS NOT NULL
SYS_C008387 "QTY_ON_HAND" IS NOT NULL
SYS_C008388 "SHELF_CAPACITY" IS NOT NULL
QTY_CK qty_on_hand >= 0
SHELF_CK qty_on_hand <= shelf_capacity
INV_PK
6 rows selected.
The creation of the table INVENTORY creates in addition an associated
reservation journal table. In our case it's called SYS_RESERVJRNL_100639 (where
100639 is the object id of table INVENTORY). The reservation journal table is
created under the same user schema and in the same tablespace as the user table.
Let's query the table USER_OBJECTS to list the objects and to verify the
creation of the two tables - INVENTORY and the journal
table SYS_RESERVJRNL_100639.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select object_name, object_type, created
from user_objects order by 3 desc;
OBJECT_NAME OBJECT_TYPE CREATED
---------------------------------------- ----------------------- ---------
INV_PK INDEX 23-MAY-23
SYS_RESERVJRNL_100639 TABLE 23-MAY-23
INVENTORY TABLE 23-MAY-23
...
With the help of this journal table, lock-free reservation enables tracking the
reservable UPDATEs within the database during the execution of transactions. A
transaction can read its own lock-free reservations by selecting from the
reservation journal tables (here SYS_RESERVJRNL_100639) on which the transaction
has issued a reservable update.
Please note: Reservations made by other transactions are not visible.
Let's check the structure:
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> desc SYS_RESERVJRNL_100094
Name Null? Type
----------------------------------------------------------------------------- -------- --------------------------------------------
ORA_SAGA_ID$ RAW(16)
ORA_TXN_ID$ RAW(8)
ORA_STATUS$ VARCHAR2(11)
ORA_STMT_TYPE$ VARCHAR2(6)
ITEM_ID NOT NULL NUMBER
QTY_ON_HAND_OP VARCHAR2(1)
QTY_ON_HAND_RESERVED NUMBER
Let's add some rows to the table and perform a COMMIT:
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
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);
commit;
Let's try a first UPDATE and violate the rule that direct assignments to a
reservable column are allowed.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory set qty_on_hand=qty_on_hand;
update inventory set qty_on_hand=qty_on_hand
*
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.
There are more limitations you need to be aware of e.g. mixing reservable and
non-reservable column updates in the same update statement is not allowed. If
you need to get a complete overview concerning the rules and restrictions please
refer to Database Development Guide 29.5 Guidelines and Restrictions for
Lock-Free Reservation.
At the moment the table content looks like:
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select item_id, qty_on_hand, shelf_capacity
from inventory;
ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
123 100 120
456 50 100
789 50 75
Now let's update the INVENTORY table and violate constraints. If the reservation
fails due to insufficient capacity in SHELF_CAPACITY, the UPDATE statement will
fail with the CHECK constraint violation.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory set qty_on_hand=qty_on_hand+100 where item_id=123;
update inventory set qty_on_hand=qty_on_hand+100 where item_id=123
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SHELF_CK) violated
Or let's perform an UPDATE and try to substract 110. In this case the constraint
SCOTT.QTY_CK will be checked and the following constraint error occurs.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory
set qty_on_hand = qty_on_hand - 110
where item_id = 123; 2 3
update inventory
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.QTY_CK) violated
In both cases a statement level rollback occurs.
Also, you really need to add or subtract values to the reservable column. You
cannot do the following ...
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory set QTY_ON_HAND=10 where item_id=123;
update inventory set QTY_ON_HAND=10 where item_id=123
*
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.
In our next scenario we will change the value of the reservable column
QTY_ON_HAND for ITEM_ID 123 in two transactions.
Please keep in mind UPDATEs to reservable columns do not lock the row until the
COMMIT of the transaction. Instead, reservable columns provide for lock-free
reservations.
At time T1: transaction 1
We try to perform two UPDATEs in transaction 1. The second UPDATE leads to a
constraint error and a statement level rollback occurs.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory
set qty_on_hand = qty_on_hand - 50
where item_id = 123;
1 row updated.
SQL> update inventory
set qty_on_hand = qty_on_hand - 60
where item_id = 123;
update inventory set qty_on_hand = qty_on_hand - 60 where item_id = 123
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.QTY_CK) violated
The transaction can see the changes it has made to the reservable columns by
inspecting the associated journal table (SYS_RESERVJRNL_100639).
Let's query the journal table to list the change(s). Only the successful
UPDATE is listed.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> set linesize window
SQL> select * from SYS_RESERVJRNL_100639;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- --------------------
090003007C3E0000 ACTIVE UPDATE 123 - 50
At time T2: transaction 2
Reservable columns provide lock-free reservations. Therefore we can perform
an UPDATE of the same item in another transaction 2.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> update inventory
set qty_on_hand = qty_on_hand + 20
where item_id = 123;
1 row updated.
Let's check again the journal table SYS_RESERVJRNL_100639.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select * from SYS_RESERVJRNL_100639;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- --------------------
0800010044340000 ACTIVE UPDATE 123 + 20
At time T3: transaction 2
As we can see the UPDATEs of the row are not performed yet because the
transactions are not committed.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select item_id, qty_on_hand, shelf_capacity
from inventory where item_id=123;
ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
123 100 120
Now let's perform a COMMIT because lock-free reservations are transformed to the
actual updates at COMMIT of the transaction.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> commit;
Commit complete.
The change of transaction 2 is applied.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> select item_id, qty_on_hand, shelf_capacity
from inventory where item_id=123;
ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
123 120 120
At time T4: transaction 1
Now let's perform a COMMIT in transaction 1. Finally, also the change of
transaction 1 is applied and visible in both transactions.
Copy code snippet
Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL> commit;
Commit complete.
SQL> select item_id, qty_on_hand, shelf_capacity
from inventory where item_id=123;
ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
123 70 120
After the update operations are committed the journal table is empty again. You
may choose another order of transaction commit order, because the order of
commit of the transactions does not matter.
Please note the journal table is for internal usage only. Therefore, user DML
and DDL operations are not permitted on a reservation journal table. You cannot
create or modify a reservation journal table using DML. You also cannot use SQL
to drop, rename, or change the reservation table's definition.
This concludes our first test scenario.
Note: Read also the posting Lock-free reservation: scale your apps from Stephane
to get insights about the usage scenarios especially concerning scalability.
Further Reading
* Database Development Guide Using Lock-Free Reservation
* Database Concepts Lock-free Reservation
* 23ai Team Publications on GitHub
ULRIKE SCHWINN
DISTINGUISHED DATA MANAGEMENT EXPERT
Ulrike Schwinn studied mathematics and works as Distinguished Data Management
Expert for Oracle. She consults customers in latest Oracle database and cloud
technologies. She is author/co-author of books and papers, podcaster, speaker at
conferences and member of the advisory board of IT-Tage, DOAG database
committee, German Data Science Community and volunteers in Cyber Mentor Online
Mentoring program for girls and young women.
Show more
STEPHANE DUPRAT
TECHNICAL LICENSE ENG SPECIALIST
Stéphane Duprat is a Data Management Expert at Oracle Iberica, member of Oracle
EMEA TSE group.
After spending 10 years as an application developper at La Française des Jeux
(France), Stéphane joined Oracle as a technical consultant. For 18 years, he
was involved in huge and complex projects as an Enterprise Architect and
database performance tuning expert. Now, as a Data Management Expert, he is
focused on technical solutions around Data management, supporting Oracle Data
Management solutions portfolio, from Database Cloud Service to Autonomous
Databases.
Stéphane holds a superior engineer grade in Physics, by the Ecole Nationale
Supérieure de Physique de Strasbourg. He owns 12 Oracle certifications, as well
as Togaf 9.1.
Stéphane enjoys reading, playing chess, playing Spanish guitar, and traveling
with his family.
Show more
Previous Post
23C SQL FIREWALL
Rob Watson | 12 min read
Next Post
ORACLE DATABASE - FIT FÜR KUBERNETES
Marcel Boermann-Pfeifer | 19 min read
Resources for
* About
* Careers
* Developers
* Investors
* Partners
* Startups
Why Oracle
* Analyst Reports
* Best CRM
* Cloud Economics
* Corporate Responsibility
* Diversity and Inclusion
* Security Practices
Learn
* What is Customer Service?
* What is ERP?
* What is Marketing Automation?
* What is Procurement?
* What is Talent Management?
* What is VM?
What's New
* Try Oracle Cloud Free Tier
* Oracle Sustainability
* Oracle COVID-19 Response
* Oracle and SailGP
* Oracle and Premier League
* Oracle and Red Bull Racing Honda
Contact Us
* US Sales 1.800.633.0738
* How can we help?
* Subscribe to Oracle Content
* Try Oracle Cloud Free Tier
* Events
* News
--------------------------------------------------------------------------------
* © 2024 Oracle
* Privacy/Do Not Sell My Info
* Cookie-Einstellungen
* Ad Choices
* Careers
Oracle Chatbot
Disconnected
* Close widget
* Select chat language
* Detect Language
* undefined
* Español
* Português
* Deutsch
* French
* Dutch
* Italian