23c – Continuing exploration of Lock-free reservations

Hello,

Days ago I posted here about one of the nicest features of Oracle 23c: the lock-free reservations. Please read that post if you haven’t yet 🙂 .

One of the things that let me intrigued was to understand how Oracle manages the journal table, how a session could only see its own reservations and if there would be a way of reading all the pending reservations.

So, I went a little further on the investigation. I tried to find if there was a VPD, generated a 10043 trace of a query against the journal table to see if Oracle was adding a filter predicated in the background, and finally I dumped a block of the journal table to see the content.

There in the dump, I saw the two transactions in the ITL, and then realized the obvious: of course, it’s just the case that the INSERT to the journal table and the pending UPDATE to the main table they represent, are both in the same transaction!

This makes total sense: as always has been the case, only the session that is executing a transaction can see the modified data. Other sessions cannot, because this would be a dirty read, that is never allowed in Oracle.

So, this solve my understanding of how Oracle manages the pending updates to a reservable column.

But, you know, this is kind of weird in a way, because as opposed to what happens in a regular transaction with regular row locks, a transaction with reservations is not totally isolated: yes, you heard right! In a totally isolated transaction, you don’t need to know what is happening in other sessions, since they are completely isolated. One transaction can only change the same data after the previous finishes manipulating that row. This, via row locks, is what guarantee the isolation.

In a transaction with lock-free reservations, on the other hand, your transaction needs to see what is pending in all other transactions for that table, to then decide for example if your update will work or will receive an error for violating a constraint.

So, I still think it would be important to have a global view of all pending reservations in a table. Consider for example, you issue an UPDATE decreasing 5 against a reservable column that has a value of 20 and receive an error for violating the check constraint that avoids negative numbers. You go and check again, value is 20, I’m decreasing 5, final result should be 15, why am I receiving an error?

Yes, we know technically that this is because there are 16 or more reserved across all other transactions, but at this point we simply cannot determine what transaction is reserving it. If I knew it, a DBA could for example kill the reserving session to release the reservation if my transaction was for some reason more important than the others.

The fact is that there is a lack of visibility of pending reservations that thus affect other transactions. And I think this should be improved.

Let me explain in more details the difference between a regular transaction and one with lock-free reservations:

Regular Transaction without lock-free reservations

If you instruct Oracle to update a non-reservable column with the following statement:

update my_table set my_column = my_column + 1;

What Oracle will really do is:

1. Read the CURRENT value of MY_COLUMN. This is a consistent read, so it considers the value of the last committed transaction. Any ongoing transactions are ignored (remember the ACID acronym for transactions? Theoretically a transaction occurs entirely in one atomic moment which is the COMMIT time; before that moment, that transaction does not exist).
2. Calculate the new value of MY_COLUMN, adding 1 to the current value.
3. Check if the new value would hit any existing constraint. If yes, throw an error, if not store the new value in MY_COLUMN and lock the row.

So, let’s say the value was 3. Your update will add 1, so put 4 in the column.

Before the COMMIT, other transactions could only see the “old” value, before your update (consistent read), and would be locked if tried to update it. After the COMMIT the row will be unlocked, meaning any other session can now read the new value and also change it in a new transaction if needed.

Transaction on lock-free reservations columns

This is not the case for lock-free reservations! If you instruct Oracle to do a similar update but now on a reservable column:

update my_table set my_reservable_column = my_reservable_column + 1;

this is what Oracle will do:

1. Read the CURRENT value of MY_RESERVABLE_COLUMN.
2. Get all the pending reserved updates FROM ALL TRANSACTIONS to get the theoretical current value. So, Oracle is “invading” other’s transactions are to check what they are doing.
3. Calculate the theoretical final value considering all pending reservations and your new update.
4. Check if this theoretical final value will violate an existing constraint. If yes, throw an error, if not store the new reservation in the journal table.

Then, when you COMMIT the transaction:

1. Read again the CURRENT value of MY_RESERVABLE_COLUMN. It may be changed since the first verification.
2. Please note that the constraint validation does not need to be rechecked since the update was reserved.
3. Calculate the new value with your Increment/decrement.
4. Store the new value in MY_RESERVABLE_COLUMN.

I hope this made the differences more clear to you.

So, what’s my point when I said it’s weird not being able see all the pending reservations?

Let’s imagine a very common case that would benefit from lock-free reservations: to control the inventory of products. So I would have a table with my products, and a reservable column INVENTORY that would contain the number of available pieces of a that particular product, and it would be RESERVABLE to allow lock-free reservations. This way, you could start a new order, put a lot of different products in the cart, and since the moment you put a certain quantity in your cart, that quantity is reserved for you until you issue the order (COMMIT your transaction).

This is the perfect case for lock-free reservations. You don’t lock other people putting the same product in the cart. Each different order can reserve the quantity they need, having guaranteed the reservations and avoiding the disgust of receiving an “out of stock” error message when checking out. Perfect, amazing.

But now, let’s see you want to purchase all the available inventory for a certain product, no matter how many they are. The ideal situation in this case would be: let’s see how many units are in stock, deduct all the reservations (meaning, people with the item in the cart), and create an order with all the remaining.

For example, if you have a current inventory of 20 and 6 units reserved in other people’s carts, your ideal order would be of 14 units, to bring the inventory down to 0.

Well, this is not exactly possible with lock-free reservations because you simply cannot see all the pending reservations.

So, translating it back to Oracle, if I have a CHECK constraint avoiding the inventory to go negative, and I see there are 20 units in stock, I can TRY to put the 20 in the cart. This would translate into an UPDATE like:

update products set inventory = inventory - 20;

As explained above, Oracle would verify the current value and all the pending reservations and decide if my update would be allowed or not. Considering the example with 6 units already reserved, my UPDATE would violate the CHECK constraint and if I really wanted to buy all available, I would need to be guessing values, decreasing the quantity one by one, until I do not violate the constraint.

They would be blind tests! Why? Because I cannot see the total number of reservations.

You may argue that my examples are not strong enough, or this is a situation that would not exist in real world. Maybe, but I still think there should be a way to see all the pending transactions. I don’t think it would be considered a dirty read per se, since it’s not a change executed but not committed; in this case, it’s a change NOT executed yet, just reserved to take place at COMMIT.

That’s it.

I know, a lot of text and not much sample codes this time, but I think this was necessary.

Let me know you thoughts. Do you agree with my point? Was I clear enough explaining it?

See you on the next one!

Testing Oracle 23c Lock-Free Reservations

Hello,

In this post I will practice test how the new 23c feature Lock-Free Reservations work.

If you are not aware, here is a summary of what is Lock-Free Reservations:

• It allows a session to update a specific column in a table row without locking the row.
• The column must be defined with the new attribute RESERVABLE.
• This is only valid for NUMERIC columns.
• The UPDATE statement must change ONLY reservable columns, and filter the rows using the primary key.
• The column value must be increased or decreased. You cannot specify a literal value.
• The updated value after the COMMIT will be based on the value available at COMMIT time, not at transaction start time. The value could have been modified by other sessions after your transaction started, using lock-free reservation updates.
• The proposed update is “promised” and reserved, but will only be effective at COMMIT time.
• The pending updates are registered in a journal.

So, lets test it in real world.

Creating a table and inserting data

First, I will create a sample table with two reservable columns, and insert some data. By the way, my INSERT statement is inserting 3 rows in the VALUES clause, another nice new feature in 23c called Table Value Constructor:

create table t1 (
id number primary key, 
value number,
res1 number reservable constraint ck_res1 check (res1 >= 0),
res2 number reservable constraint ck_res2 check (res2 >= 0));

Table created.

insert into t1 values 
(1, 0, 10, 10),
(2, 0, 10, 10),
(3, 0, 10, 10)
;

3 rows created.

commit;

commit complete.

Updates on reservable columns must increase or decrease the value

Note below that the first update fails because I tried to put a specific value in the reservable column. The second one works because I used an increment:

update t1 set res1 = 102 where id=1;
update t1 set res1 = 102 where id=1
       *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.

update t1 set res1 = res1 + 1 where id=1;

1 row updated.

Updates cannot mix reservable and non-reservable columns

Again, below the first update fails because I tried to update reservable and non-reservable columns at the same time. The second one works and shows that I can update multiple reservable columns at the same time:

update t1 set value = 1, res1 = res1 + 1 where id=1;
update t1 set value = 1, res1 = res1 + 1 where id=1
       *
ERROR at line 1:
ORA-55735: Reservable and non-reservable columns cannot be updated in the same statement.

update t1 set res1 = res1 + 1, res2 = res2 + 1 where id=1;

1 row updated.

Updates on reservable columns are only effective at COMMIT time

When you update a reservable column and it’s successful, you actually receive a promise that you will be able to make the update at COMMIT time. So you won’t see your update right after issuing it. It’s important to note that for application purposes:

rollback;

Rollback complete.

select * from t1 where id=1;

        ID      VALUE       RES1       RES2
---------- ---------- ---------- ----------
         1          0         10         10

1 row selected.

update t1 set res1 = res1 + 1, res2 = res2 + 1 where id=1;

1 row updated.

select * from t1 where id=1;

        ID      VALUE       RES1       RES2
---------- ---------- ---------- ----------
         1          0         10         10

commit;

Commit complete.

select * from t1 where id=1;

        ID      VALUE       RES1       RES2
---------- ---------- ---------- ----------
         1          0         11         11

1 row selected.

Updates on reservable columns must filter rows by Primary Key

If I don’t mention the primary key in the WHERE clause, an error is raised:

update t1 set res1 = res1 + 1;
update t1 set res1 = res1 + 1
       *
ERROR at line 1:
ORA-55732: Reservable column update should specify all the primary key columns in the WHERE clause.

update t1 set res1 = res1 + 1 where value=1;
update t1 set res1 = res1 + 1 where value=1
       *
ERROR at line 1:
ORA-55732: Reservable column update should specify all the primary key columns in the WHERE clause.

Updates on reservable columns cannot use other columns’ values, even if the mentioned column is also reservable

And I cannot even mention the column itself, in an update that syntactically would do nothing 🙂 :

update t1 set res1 = res2 + 1 where id=1;
update t1 set res1 = res2 + 1 where id=1
       *
ERROR at line 1:
ORA-55747: The SET clause of a reservable update statement requires the amount to be added or subtracted from the same reservable column, "RES2".

update t1 set res1 = res1 where id=1;
update t1 set res1 = res1 where id=1
       *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.

The same transaction can update reservable and a non-reservable columns

You cannot mix updates on reservable and non-reservable columns in the same statement, but you can in the same transaction.

EDIT 04/25/2023: as opposed to what I mentioned here initially, if you update a non-reservable column, the updated row will be locked even for other transactions, even if they only try to update reservable columns. I explain this better here.

In this case, the UPDATE on the non-reservable column will hold a lock as usual, and prevent other transactions from updating that row, even if only updating the reservable columns.

Session 1:

select userenv('sid');

USERENV('SID')
--------------
301

1 row selected.
update t1 set res1=res1+1 where id=1;

1 row updated.

update t1 set value=1 where id=1;

1 row updated.

Session 2:

select userenv('sid');

USERENV('SID')
--------------
300

1 row selected.
update t1 set res1=res1+2 where id=1;

1 row updated.

update t1 set value=1 where id=1;

(LOCKED...)

Checking the journal for pending reservations

When you create a table with reservable columns, Oracle creates an associated table to record the journal of pending updates on these columns. The name of the table is SYS_RESERVJRNL_<object_id>.
Please note that each session can only see its own reservations, not all of them. I could not find a way to see all pending reservations from all sessions, nor could find anything related in the documentation.

Session 1:

select userenv('sid');

USERENV('SID')
--------------
301

1 row selected.
select object_id from user_objects where object_name='T1';

 OBJECT_ID
----------
     78433

1 row selected.

select * from SYS_RESERVJRNL_78433;

ORA_SAGA_ID$                     ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$           ID RES1_OP RES1_RESERVED RES2_OP RES2_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- ------------- ------- -------------
                                 0800080003020000 ACTIVE       UPDATE                    1 +                   1

1 row selected.

commit;

commit complete.

Session 2:

select userenv('sid');

USERENV('SID')
--------------
300

1 row selected.

select * from SYS_RESERVJRNL_78433;

ORA_SAGA_ID$                     ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$           ID RES1_OP RES1_RESERVED RES2_OP RES2_RESERVED
-------------------------------- ---------------- ------------ ---------------- ---------- ------- ------------- ------- -------------
                                 0900110009020000 ACTIVE       UPDATE                    1 +                   2

1 row selected.

commit;

commit complete.

Note that each session sees its own transaction reservation (the TXN_IDs are different).

The journal table acts like a temporary table, although it’s not:

select temporary from dba_tables where table_name='SYS_RESERVJRNL_78433';

T
-
N

1 row selected.

The constraints are validated at the statement time

Since the updates reserve the amounts requested, the constraints are validated at the statement time and will raise an error if your update along with all the reservations together would violate the constraint.
Note that in this case, the validation is still pessimistic: if some or all the reservations do not confirm (transactions rollback), your update could make through, but since it’s not guaranteed at that point, the constraint has to raise an error.

Session 1:

The first UPDATE receives an error because it alone would violate the constraint, since I’m trying to decreasing the value by 20, which would result in a negative value.

The second one works since it’s decreasing the value by 10 and the resulting value would not violate the constraint.

select userenv('sid');

USERENV('SID')
--------------
301

1 row selected.

rollback;

Rollback complete.

select * from t1 where id=1;

        ID      VALUE       RES1       RES2
---------- ---------- ---------- ----------
         1          0         14         11

1 row selected.

update t1 set res1 = res1 - 20 where id=1;
update t1 set res1 = res1 - 20 where id=1
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_RES1) violated

update t1 set res1 = res1 - 10 where id=1;

1 row updated.

Session 2:

Note that here, the same update that worked for Session 1 does not work because now the combined reservations would be a decrease of 20, again resulting in a negative number that is not valid for the constraint.

select userenv('sid');

USERENV('SID')
--------------
300

1 row selected.

rollback;

Rollback complete.

update t1 set res1 = res1 - 10 where id=1;
update t1 set res1 = res1 - 10 where id=1
*
ERROR at line 1:
ORA-02290: check constraint (TEST.CK_RES1) violated

Triggers on reservable columns are triggered at COMMIT time

A row-level trigger on a non-reservable column would always be triggered at the statement time, but this is not the case for reservable columns.
Note that I will create two triggers, one on the column VALUE, that is not reservable, and another on RES1 which is reservable. I will create a log table to use in the triggers:

create table t1_log_reservations 
(id number, timestamp date, column_name varchar2(30), value number);

Table created.

create or replace TRIGGER trg_on_value
after UPDATE of value ON t1 
for each row
BEGIN
	insert into t1_log_reservations
	values (:new.id, sysdate, 'VALUE', :new.value);
END;
/

Trigger created.

create or replace TRIGGER trg_on_res1
after UPDATE of res1 ON t1 
for each row
BEGIN
	insert into t1_log_reservations
	values (:new.id, sysdate, 'RES1', :new.res1);
END;
/

Trigger created.

Now I will update the two columns and check my log table that is populated by the triggers:

update t1 set res1=res1+1 where id=1;

1 row updated.

update t1 set value=10 where id=1;

1 row updated.

select * from t1_log_reservations;

        ID TIMESTAMP           COLUMN_NAME                         VALUE
---------- ------------------- ------------------------------ ----------
         1 2023-04-18 16:54:58 VALUE                                  10

1 row selected.

Note that only the trigger on VALUE was triggered so far.

Now, let’s COMMIT and check again:

commit;

Commit complete.

select * from t1_log_reservations;

        ID TIMESTAMP           COLUMN_NAME                         VALUE
---------- ------------------- ------------------------------ ----------
         1 2023-04-18 16:54:58 VALUE                                  10
         1 2023-04-18 16:57:06 RES1                                    2

2 rows selected.

Now the trigger on RES1 was triggered.

You cannot drop a table with reservable columns

If you try to drop a table with reservable columns you get an error. So, you first need to ALTER the columns to NOT RESERVABLE and the you can drop the table:

drop table t1;
drop table t1
           *
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.

alter table t1 modify (res1 not reservable, res2 not reservable);

Table altered.

drop table t1;

Table dropped.

I’m pretty sure I may have missed some important points to address about this new, amazing feature of Oracle 23c.

Let me know in the comments and I can include or create a supplemental post.

I hope I hope it can be useful.

See you soon!