8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Lock-Free Reservations to Prevent Blocking Sessions in Oracle Database 23c
In Oracle 23c we can use Lock-Free Reservations to reduce the incidents of blocking sessions for frequently updated numeric column values.
Relational databases have a heavy focus on ACID compliance. This stops logical data corruption, which is really important for most of our data. We would rather have sessions block each other, than risk having incorrect data in our tables.
Despite this, there are some scenarios where blocking sessions are problematic and unnecessary. The main example in this article uses a column that is frequently incremented to maintain sales total counters.
The Problem : Blocking Sessions
We create a table to hold product information and seed it with some products. We include an ITEMS_SOLD column, which provides a running total of the sales as a summary, so we don't have to aggregate the raw data.
-- Clean up before doing a new run.
drop trigger if exists sales_trg;
drop table if exists sales purge;
alter table if exists products modify (items_sold not reservable);
drop table if exists products purge;
create table products (
product varchar2(10) primary key,
items_sold number
);
insert into products (product, items_sold)
values ('banana', 0),
('apple', 0),
('lemon', 0),
('lime', 0);
commit;
We create a table to hold sales information for our products. Each sale is a new insert, so we don't have any problems with updates getting blocked because of heavy transaction rates.
create table sales ( id number generated always as identity primary key, product varchar2(10), quantity number, constraint sales_products_fk foreign key (product) references products (product) );
We would expect our application to update the PRODUCTS table for every new sale to keep the ITEMS_SOLD column up to date. In this case we will implement that using a trigger.
create or replace trigger sales_trg after insert on sales for each row begin update products p set p.items_sold = p.items_sold + :new.quantity where p.product = :new.product; end; /
With each insert into the SALES table, our PRODUCTS table is updated. We check our current totals.
select * from products; PRODUCT ITEMS_SOLD ---------- ---------- banana 0 apple 0 lemon 0 lime 0 SQL>
We create some new sales. The trigger fires during the inserts, and we see the impact immediately in the ITEMS_SOLD column values.
insert into sales (product, quantity) values ('banana', 10);
insert into sales (product, quantity) values ('apple', 5);
select * from products;
PRODUCT ITEMS_SOLD
---------- ----------
banana 10
apple 5
lemon 0
lime 0
SQL>
We commit the new sales.
commit;
The problem with this setup is inserts from different sessions can block each other. Open two new connections to the database. In session 1 we issue the following statement.
insert into sales (product, quantity) values ('banana', 1);
In session 2 we issue the following statement.
insert into sales (product, quantity) values ('banana', 5);
Notice the insert in session 2 is blocked until session 1 issues a commit or rollback. This is because both sessions are attempting to update the same row in the PRODUCTS table via the trigger.
In session 1 we issue a commit, and we see the insert in session 2 completes. Issue a commit in session 2 also.
-- Session 1 commit; -- Session 2 commit; select * from products; PRODUCT ITEMS_SOLD ---------- ---------- banana 16 apple 5 lemon 0 lime 0 SQL>
The Solution : Lock-Free Reservations
Lock-free reservations stop the sessions from blocking each other. Transactions against a reservable column are held in a reservation journal and only processed on commit of the transaction.
Let's clean up the SALES and PRODUCTS tables.
truncate table sales; update products set items_sold = 0; commit;
We set the ITEMS_SOLD column to RESERVABLE.
alter table if exists products modify (items_sold reservable);
We check the USER_OBJECTS view and we see a reservation journal table has been created.
select object_name from user_objects where object_type = 'TABLE'; OBJECT_NAME -------------------------------------------------------------------------------- PRODUCTS SALES SYS_RESERVJRNL_87694 SQL>
We describe this reservation journal table and we see it includes the primary key column (PRODUCT), the operation on the ITEMS_SOLD column (ITEMS_SOLD_OP) and the reserved value for the operation on the ITEMS_SOLD column (ITEMS_SOLD_RESERVED).
desc sys_reservjrnl_87694 Name Null? Type ----------------------------------------- -------- ---------------------------- ORA_SAGA_ID$ RAW(16) ORA_TXN_ID$ RAW(8) ORA_STATUS$ CHAR(12) ORA_STMT_TYPE$ CHAR(16) PRODUCT NOT NULL VARCHAR2(10) ITEMS_SOLD_OP CHAR(7) ITEMS_SOLD_RESERVED NUMBER SQL>
We create some new sales. The trigger fires during the inserts, but we don't see the impact immediately in the ITEMS_SOLD column values.
insert into sales (product, quantity) values ('banana', 10);
insert into sales (product, quantity) values ('apple', 5);
select * from products;
PRODUCT ITEMS_SOLD
---------- ----------
banana 0
apple 0
lemon 0
lime 0
SQL>
We check the reservation journal table and we can see the two reservations for the updates to the PRODUCTS table.
select ora_stmt_type$,
product,
items_sold_op,
items_sold_reserved
from sys_reservjrnl_87694;
ORA_STMT_TYPE$ PRODUCT ITEMS_S ITEMS_SOLD_RESERVED
---------------- ---------- ------- -------------------
UPDATE apple + 5
UPDATE banana + 10
SQL>
We issue a commit, and we see the changes to the PRODUCTS table, and the journal table is empty.
commit;
select * from products;
PRODUCT TOTAL_QUANTITY
---------- --------------
banana 10
apple 5
lemon 0
lime 0
SQL>
select ora_stmt_type$,
product,
items_sold_op,
items_sold_reserved
from sys_reservjrnl_87694;
no rows selected
SQL>
Open two connections to the database. In session 1 we issue the following statement.
insert into sales (product, quantity) values ('banana', 1);
In session 2 we issue the following statement.
insert into sales (product, quantity) values ('banana', 5);
The insert in session 2 is no longer blocked by the first session. Both still performed their updates, but the trigger changes to the ITEMS_SOLD column in the PRODUCTS table were reserved.
We don't see any changes to the ITEMS_SOLD column until the sessions commit their changes.
select * from products; PRODUCT TOTAL_QUANTITY ---------- -------------- banana 10 apple 5 lemon 0 lime 0 SQL> -- Session 2 commit; select * from products; PRODUCT TOTAL_QUANTITY ---------- -------------- banana 15 apple 5 lemon 0 lime 0 SQL> -- Session 1 commit; select * from products; PRODUCT TOTAL_QUANTITY ---------- -------------- banana 16 apple 5 lemon 0 lime 0 SQL>
Basic Usage
There are a lot of guidelines and restrictions associated with reservable columns, listed here, but we'll run through some basic usage below.
The table must have a primary key.
drop table if exists t1 purge; create table t1 ( code varchar2(10), res_col number reservable ); * ERROR at line 1: ORA-55728: Reservable column property can only be specified for a column on a table that has a primary key. SQL>
We repeat the table creation, but this time give the table a primary key and populate it with a row of data.
drop table if exists t1 purge;
create table t1 (
code varchar2(10) primary key,
res_col number reservable
);
insert into t1 values ('one', 0);
commit;
We can't update the column to set a specific value. We must use a + or - operation.
update t1
set res_col = 5
where code = 'one';
*
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.
SQL>
As expected, we can increment or decrement the value using a + or - operation.
update t1 set res_col = res_col + 5 where code = 'one'; update t1 set res_col = res_col - 5 where code = 'one'; commit;
We can't perform any other mathematical operations on it.
update t1
set res_col = res_col * 5
where code = 'one';
*
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.
SQL>
We have to reference the primary key when doing an update. If we omit the primary key, we get an error.
update t1
set res_col = res_col - 5;
*
ERROR at line 1:
ORA-55732: Reservable column update should specify all the primary key columns
in the WHERE clause.
SQL>
We can't delete a row from a table with a reservable column if there are any outstanding reservations. We update the reservable column them attempt a delete, which fails. If we commit or rollback the update, the delete operation works fine.
update t1 set res_col = res_col + 5 where code = 'one'; delete from t1 where code = 'one'; * ERROR at line 1: ORA-55754: Resource busy error is detected for the reservable column update statement. A delete or a DDL operation is conflicting with this update statement. SQL> commit; delete from t1 where code = 'one'; 1 row deleted. SQL>
We can't drop the table if it has a reservable column.
drop table if exists t1 purge;
*
ERROR at line 1:
ORA-55764: Cannot DROP or MOVE tables with reservable columns. First run "ALTER
TABLE MODIFY ( NOT RESERVABLE)" and then
DROP or MOVE the table.
SQL>
Instead we have to change the column to non reservable first, then drop the table.
alter table if exists t1 modify (res_col not reservable); drop table if exists t1 purge;
Considerations
Some things to consider about this functionality.
- There are a lot of guidelines and restrictions associated with reservable columns, listed here.
- This functionality is focussed on preventing blocking row locks on hot columns in a table.
- There is more work happening to complete the update processing. For a typical update there is now the maintenance of reservation journal and the final update.
- If there are multiple updates to the same row by our session in a single transaction, this will result in multiple journal entries for that row. When the final update is applied to the table, the multiple journal entries will be consolidated and applied to the destination table using a single update.
- Rollback and rollback to save points are supported as normal.
For more information see:
Hope this helps. Regards Tim...