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!
EDUARDO CLARO 