An old truth in Oracle and just about all relational databases has been that an update on one row by one session will block an update by another session on the same row. Until the first session commits or rolls back.
That is until 23ai. Or at least in some very specific scenarios.
Let’s consider a situation where we need to track changes to a numeric value up or down by some quantity. This could be the amount in a bank account or inventory balance or something like that.
Let’s look at how it has always been.
Test Case
First a test table and a row to update.
create table test
( id number(5) primary key
, item_no number(5)
, in_stock number(7));
insert into test (id,item_no,in_stock) values(1,12345,42);Now with that in place, lets issue a standard update that increases the inventory with 10 units and issue it in two separate sessions.
update test set in_stock = in_stock + 10 where id = 1;The first session of course completes and can issue more SQL. The second hangs waiting for the first to release the lock on the row with id = 1.
Make It Reservable
Let’s now change the table so the inventory is reservable.
alter table test MODIFY (in_stock reservable);Let’s now attempt the update in both sessions again.
That worked, two sessions has updated the same row without committing in between. Surely the database we love and trust is broken beyond belief?
What if I told you that the table has not been updated at all. There is no undo data to apply in a rollback.
Journal Table
To understand what has happened let’s look at what table there is in the schema. You will find one odd named with a name like SYS_RESERVJRNL_98765. So SYS indicates it is a system object. RESERVJRNL sytands for “reservation journal”. Meaning this is book keeping for reservable columns. The last part then, just an obscure number.
Well maybe obscure but a very obvious link once you know it. Take a look user_objects and you will find the table test having the number this table has in your database. Oracle creates one such journal table for every table you create with at least one reservable column.
Let’s take a look at the contents.
ORA_TXN_ID$ ORA_STATUS$ ORA_ST ID I IN_STOCK_RESERVED 030020004D090000 ACTIVE UPDATE 1 + 10
I skipped the ORA_SAGA_ID$ as it is not populated and it is used for micro services which we’re not looking at here. There is just one row even though we have two sessions with reservations. A session can only see its own reservations.
The columns here are transaction id, status of the transaction, and what kind of operation (always UPDATE). Those are the generic ones. The rest refers to the table and operation performed. ID 1 was updated to add (+) 10 units. ID, IN_STOCK_OP and IN_STOCK_RESERVED will of course change if the tables columns has different names. The last two are also repeated if there are more reservable columns in a table.
This table indicates a little what is going on. Oracle keeps track of the requested reservations in this table. At commit time it then issues the actual updates on the target table.
Updates that no longer work
Let us now update the row using the item_no. It is unique but the primary key.
update test set in_stock = in_stock + 10 where item_no = 12345;ORA-55732:
55732. 00000 - "Reservable column update should specify all the primary key columns in the WHERE clause."
*Cause: The reservable column update statement does not have a WHERE clause
with all the primary keys.
*Action: Add a WHERE clause with all the primary keys to the
reservable column update statement.
Using a reservable column can only be done by referencing the full primary key in the where-clause. Pretty nice actually, no more tables that lacks a primary key. At least not if it contains a reservable column.
Let’s set the value directly then. We have just counted how many of this item we actually have.
update test set in_stock = 58 where item_no = 12345;ORA-55746:
55746. 00000 - "Reservable column update statement only supports + or - operations on a reservable column."
*Cause: An attempt is being made to update a reservable column with something
other than a + or -. An amount should be added or subtracted from
the reservable column. Direct assignments to reservable columns
are not supported.
*Action: Change the update statement to add or subtract from the same
reservable column. The set clause should be of the form
reservable_col1 = reservable_col1 + (<expression>) or
reservable_col1 = reservable_col1 - (<expression>)."
where the expression in parenthesis evaluates to the amount to be
added or subtracted from the same reservable column,
reservable_col1.
Wonderfully descriptive error message. And pretty obvious that if we have reservations against a column it cannot have it’s value changed to an absolut value suddenly. So we can adjust it to it as we want.
update test set in_stock = in_stock + (58 - in_stock) where id = 1;You will want to be careful with that as what is in_stock when to command runs may not be what is in_stock when i commits. Any attempt to fix it to a known correct value ought to be done where you control that no other concurrent DML is issued.
Check Constraints
How are constraints honored when the actual table changes are not performed until commit?
Let’s require the intems in stock to be a positive number.
alter table test modify (in_stock constraint must_be_positive check (in_stock >= 0));
Let’s first look at what happens if one session subtracts and another adds.
-- in_stock = 52 as we start.
-- Session 1:
update test set in_stock = in_stock - 25 where id = 1;
-- in_stock = 27
-- Session 2:
update test set in_stock = in_stock + 25 where id = 1;
-- in_stock = 52
-- Session 1:
update test set in_stock = in_stock - 25 where id = 1;
-- in_stock = 27
update test set in_stock = in_stock - 25 where id = 1;Error starting at line : 1 in command - update test set in_stock = in_stock - 25 where id = 1 Error report - ORA-02290: check constraint violated (<schema>.MUST_BE_POSITIVE)
What happened, we were still within the check. Consider what would happen if session 2 rolled back and session one committed. We would then have a negative value. So it is evaluated that the check constraint will be valid no matter which transactions are committed and rolled back.
There is one exception to this. It is if you have a table level constraint that includes reservable as well as non reservable columns then the commit may fail with check constraint validation. This is due to the check not being able to guarantee the value of the non reservable column at the time of the commit when the actual DML for the reservable updates occurs.
This is a very nice feature. But it is not for every situation. But keep it in your tool chest for cases where you have high contention on additions and subtractions from a value that is some form of book keeping.