20 maart 2024

If you update a row in Oracle Database then the database places a lock on that record preventing other sessions from updating the same row, while you are working on it. As long as you don’t end your transaction, by issuing a commit or rollback, the row stays locked.

Usually this is not a problem because different sessions work on different rows in your database, but what would happen if you have to sell tickets to, let’s say, the main grandstand for a Formula1 race at Circuitpark Zandvoort. The main grandstand has 2750 seats. It would take a very long time for all the seats to be sold, when they have to be sold sequentially, but even if you would sell them in sets of multiple seats at a time it would take some time when all these transactions are performed sequentially.

This is what happens when you use Oracle Database the way you are used to. Let’s say you sell the tickets in batches of five, and it would take a buyer about 2 minutes to complete a transaction, that would mean it would take more than 18 hours to sell all the tickets. And we always hear, on the news, that when the tickets go on sale, they are sold out in a matter of minutes.

Oracle Database 23 now gives you the opportunity to mark a column as reservable which effectively means that you can update its value without placing a lock on the record so other sessions can update the same column of the same record without being blocked. Let’s take a look at how this works.

Oracle Database 23c introduces a new attribute for numeric (number, integer, or float) columns. A column of these datatypes can now be marked as reservable. This gives us the opportunity to increase or decrease the value of the column without putting a lock on the entire record. For this to work, the table has to have a primary key constraint in place.

Let’s create a couple of tables first, with their primary and foreign keys in place:

create table if not exists circuits
( circuitid  number  (  11 ) generated by default on null 
                            as identity 
, name       varchar2( 256 ) 
, location   varchar2( 256 )
, country    varchar2( 256 )
, constraint pk_circuits primary key( circuitid )
);
create table if not exists stands
( standid    number  (  11 ) generated by default on null
                            as identity
, circuitid  number  (  11 )
, name       varchar2( 256 )
, seatcount  number  (   5 )
, seatssold  number  (   5 ) reservable
, constraint pk_stands primary key ( standid )
, constraint fk_stands_circuits foreign key ( circuitid )
                               references circuits( circuitid )
, constraint ck_seat_available  check ( seatcount >= seatssold )
)
/

As you can see a check constraint has also been added, ck_seat_available to check if the number of seats sold doesn’t exceed the number of seats available in a stand. 

Now it is time to put in some data. The 2023 season consists of a lot of races at different circuits, but for this example we a just adding Circuit Park Zandvoort to the table.

insert into circuits
 ( name, location, country )
values ('Circuit Park Zandvoort', 'Zandvoort', 'Netherlands')
/

Circuit Park Zandvoort has a lot of stands, like Tarzan, Pit, Arena, and Ben Pon, but for this example we are just using the Main stand.

insert into stands
 ( circuitid, name, seatcount, seatssold )
values ( 1, 'Main', 2750, 0 )
/

Since we just added one circuit to the circuits table, we ‘know’ the circuitid = 1, if you added multiple circuits to the table, you might want to use a construction like this:

insert into stands
 ( circuitid
 , name
 , seatcount
 , seatssold
 )
values ( ( select circuitid from circuits where location = 'Zandvoort')
      , 'Main'
      , 2750
      , 0
      )
/

Now we can start selling the seats. If we try to update the column to a new value we run into an error:

update stands
set    seatssold = 10
where  standid   = 1
/
update stands
      *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.

This is a restriction of the reservable column. You give it a value when you insert the row, but after that you can only increase or decrease its current value.
So, to able to sell the 10 tickets we need to run a different statement:

update stands
set    seatssold = seatssold + 10
where  standid   = 1
/

1 row updated.

We do not commit or rollback this transaction. In another session we run a similar statement:

update stands
set    seatssold = seatssold + 42
where  standid   = 1
/

1 row updated.

This also runs without errors, so we now have two open transactions that have sold a total of 52 seats, but, when we query the stands table, nothing seems to have happened:

select name
    , seatcount
    , seatssold
from   stands
where  standid = 1
/

NAME        SEATCOUNT  SEATSSOLD
---------- ---------- ----------
Main         2750           0

The value of seatssold has not changed at all. This is because Oracle Database doesn’t change the value at all. Instead, it writes the change to the column to a SYS_RESERVJRNL_<object_id> table. If we query this table, it shows the changes that were made in this session:

select ora_status$
    , ora_stmt_type$
    , standid
    , seatssold_op
    , seatssold_reserved
from   sys_reservjrnl_721229
/
ORA_STATUS$  ORA_STMT_TYPE$   STANDID    SEATSSO SEATSSOLD_RESERVED
------------ ---------------- ---------- ------- ------------------
ACTIVE       UPDATE                    1 +                       10 

The name of your table will be different. You can use this code snippet to read the data from the correct table:

-- find the journalling table
-- sys_reservjrnl_ + the object_id of the table
column table_name new_value tablename
select 'sys_reservjrnl_' || to_char( object_id ) as table_name
from   user_objects
where  object_name = 'STANDS'
/
-- query the journalling table
select ora_status$
    , ora_stmt_type$
    , standid
    , seatssold_op
    , seatssold_reserved
from   &tablename
/

As you can see, it only shows us the changes made in our own session. But that doesn’t mean we can reserve all the seats in our session if there are seats reserved in different sessions. Since we reserved 10 seats, that would leave 2740 more seats available. Let’s try to reserve all of the seats:

update stands
set    seatssold = seatssold + 2740
where  standid   = 1
/

update stands
*
ERROR at line 1:
ORA-02290: check constraint (PATRICK.CK_SEAT_AVAILABLE) violated

So, even though it looks like there are enough seats available, when we subtract the reserved seats from the seatcount, Oracle Database does check if there are other sessions that have a reservation on this column.

You must only use the primary key in the update statement when you are altering the value of a reservable column. If you want to change any other column of the same table, you don’t have to use the primary key in your statement.

The source code used in this article can be found at:
https://github.com/Qualogy-Solutions/OracleDatabase23c/blob/main/reservable.sql

Also read my previous blogs about Oracle Database 23:

• Introduction blog series Oracle Database 23
• Developer Role
• Group by Alias
• If [Not] Exists 
• Table values constructor
• The Boolean data type
• New PL/SQL iterator constructs
• Annotations
• SQL Macros 
• Immutable tables
• SQL Domains

Auteur: Patrick Barel

Terug naar overzicht

Deel

Facebook Twitter LinkedIn E-mail Whatsapp