DBA Hub

📋Steps in this guide1/4

Lock-Free Reservations to Prevent Blocking Sessions in Oracle Database 23ai/26ai

In Oracle 23ai/26ai we can use Lock-Free Reservations to reduce the incidents of blocking sessions for frequently updated numeric column values.

oracle 23configurationintermediate
by OracleDba
20 views
1

The Problem : Blocking Sessions

We create a table to hold product information and seed it with some products. We include an column, which provides a running total of the sales as a summary, so we don't have to aggregate the raw data. 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. We would expect our application to update the table for every new sale to keep the column up to date. In this case we will implement that using a trigger. With each insert into the table, our table is updated. We check our current totals. We create some new sales. The trigger fires during the inserts, and we see the impact immediately in the column values. We commit the new sales. 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. In session 2 we issue the following statement. 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 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
-- 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;

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)
);

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;
/

select * from products;

PRODUCT    ITEMS_SOLD
---------- ----------
banana              0
apple               0
lemon               0
lime                0

SQL>

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>

commit;

insert into sales (product, quantity) values ('banana', 1);

insert into sales (product, quantity) values ('banana', 5);

-- Session 1
commit;

-- Session 2
commit;


select * from products;

PRODUCT    ITEMS_SOLD
---------- ----------
banana             16
apple               5
lemon               0
lime                0

SQL>
2

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 and tables. We set the column to . We check the view and we see a reservation journal table has been created. We describe this reservation journal table and we see it includes the primary key column ( ), the operation on the column ( ) and the reserved value for the operation on the column ( ). We create some new sales. The trigger fires during the inserts, but we don't see the impact immediately in the column values. We check the reservation journal table and we can see the two reservations for the updates to the table. We issue a commit, and we see the changes to the table, and the journal table is empty. Open two connections to the database. In session 1 we issue the following statement. In session 2 we issue the following statement. The insert in session 2 is no longer blocked by the first session. Both still performed their updates, but the trigger changes to the column in the table were reserved. We don't see any changes to the column until the sessions commit their changes.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
truncate table sales;

update products
set    items_sold = 0;
commit;

alter table if exists products modify (items_sold reservable);

select object_name
from   user_objects
where  object_type = 'TABLE';

OBJECT_NAME
--------------------------------------------------------------------------------
PRODUCTS
SALES
SYS_RESERVJRNL_87694

SQL>

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>

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>

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>

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>

insert into sales (product, quantity) values ('banana', 1);

insert into sales (product, quantity) values ('banana', 5);

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>
3

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. We repeat the table creation, but this time give the table a primary key and populate it with a row of data. We can't update the column to set a specific value. We must use a + or - operation. As expected, we can increment or decrement the value using a + or - operation. We can't perform any other mathematical operations on it. We have to reference the primary key when doing an update. If we omit the primary key, we get an error. 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. We can't drop the table if it has a reservable column. Instead we have to change the column to non reservable first, then drop the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
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>

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;

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>

update t1
set    res_col = res_col + 5
where  code = 'one';

update t1
set    res_col = res_col - 5
where  code = 'one';

commit;

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>

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>

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>

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>

alter table if exists t1 modify (res_col not reservable);
drop table if exists t1 purge;
4

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: - Using Lock-Free Reservation - Guidelines and Restrictions for Lock-Free Reservation Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!