شرایطی را در نظر بگیرید که قرار است فیلدی به عنوان شمارنده، دائما توسط تعداد زیادی session بروزرسانی شود(با همزمانی بالا) به این شیوه که با هر بار انجام «اقدامی مشخص»، دستور updateای اجرا شده و یک عدد به این شمارنده اضافه کند.

در این حالت با توجه به زیاد بودن تعداد sessionهای همزمان، احتمال رخ دادن Blocking session هم بسیار افزایش می یابد چرا که در دیتابیس اوراکل اگر دو کاربر قصد ویرایش یک رکورد را داشته باشند و با فاصله زمانی کمی دستور Update را اجرا کنند، کاربری که دیرتر دستور update را صادر کرده Block خواهد شد و تا زمانی که کاربر اول(کاربری که زودتر رکورد را در اختیار گرفته) به تراکنش خاتمه ندهد، کاربر دوم در حالت Block باقی خواهد ماند.

--session 1:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
      2190
SQL> update tbl_counter set counttt=counttt+1;
1 row updated

--session 2:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
       944

SQL> update tbl_counter set counttt=counttt+1;
Executing…

بلاک شدن session دوم را می توانیم از طریق دستور زیر ببینیم:

SQL>  select SID,ID1,ID2,LMODE,block,request from v$lock where type='TX';
       SID        ID1        ID2      LMODE      BLOCK    REQUEST
---------- ---------- ---------- ---------- ---------- ----------
       944     458766       2511          0          0          6
      2190     458766       2511          6          1          0

بنابرین زمانی که کاربران زیادی قصد کار بر روی یک رکورد را دارند، مکانیزم locking امکان ویرایش رکورد را در هر لحظه به یک نفر از آنها خواهد داد و بقیه sessionها Block می شوند. پیدا کردن ترفندی برای جلوگیری از Block شدن sessionها می تواند در بعضی از این شرایط نظیر «شرایط ذکر شده در ابتدای متن» بسیار راهگشا باشد.

اوراکل در نسخه 23c قابلیت جدیدی به نام Lock-free reservation را ارائه کرده است که از طریق این قابلیت می توانیم ستونهایی از یک جدول را به عنوان RESERVABLE تنظیم کنیم تا در زمان بروزرسانی این دسته از ستونها Blocking session رخ ندهد.

در زمان استفاده از این قابلیت، اوراکل از Journal Table استفاده می کند و تا قبل از commit شدن تراکنش، تغییری را در جدول اصلی اعمال نمی کند و به جای بروزرسانی رکورد در جدول اصلی، در سطح session به Journal Table رکوردی را اضافه خواهد کرد و همزمانی را به این روش کنترل می کند.

بنابرین هر session در Journal Table اطلاعات مختص به خودش را می بیند و اطلاعاتش در sessionهای دیگر قابل رویت نیست همچنین با پایان یافتن تراکنشهای هر session، رکوردهای متعلق به آن session از Journal Table حذف خواهند شد(همانند temporary tableها).

برای استفاده از قابلیت Lock-free reservation باید شرایطی را رعایت کنیم که مهمترین آنها را در قسمت زیر می بینید:

1.جدول باید دارای primary key باشد.

SQL> Create table tbl_counter(
  2  id number ,
  3  counttt float RESERVABLE
  4  );
'ORA-55728: Reservable column property can only be specified for a column on a table that has a primary key.'

SQL> Create table tbl_counter(
  2  id number primary key,
  3  counttt float RESERVABLE
  4  );
Table created

2.دیتاتایپ ستون مدنظر باید از نوع NUMBER، INTEGER و یا FLOAT باشد.

SQL> Create table tbl_counter(
  2  id number primary key,
  3  counttt varchar2(10) RESERVABLE
  4  );
'ORA-55748: Reservable column property specified on column "COUNTTT" is supported only on columns of data types Oracle NUMBER, INTEGER, and FLOAT.'

SQL> Create table tbl_counter(
  2  id number primary key,
  3  counttt number RESERVABLE
  4  );
Table created

SQL> Create table tbl_counter2(
  2  id number primary key,
  3  counttt int RESERVABLE
  4  );
Table created

3.بروزرسانی ستون صرفا باید از طریق عملگرهای +  و – انجام شود.

Counttt=counttt+10
Counttt=counttt-20

SQL> update counter set counttt=counttt*10 where id=1;
'ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.'

SQL> update counter set counttt=10 where id=1;
'ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.'

2.در هنگام اجرای دستور update، باید از ستون PK در where clause استفاده کرد:

SQL> update tbl_counter set counttt=counttt+1;
'ORA-55732: Reservable column update should specify all the primary key columns in the WHERE clause.'

SQL> update tbl_counter set counttt=counttt+1 where id=1;
1 row updated

با این شرایط، سناریوی فوق را می توانیم با استفاده از این قابلیت تکرار کنیم:

Connected to Oracle Database 23c Free, Release 23.0.0.0.0

SQL> create table tbl_counter(id number primary key,counttt number RESERVABLE);
Table created
SQL> insert into tbl_counter values(1,0);
1 row inserted
SQL> commit;
Commit complete

ستون has_reservable_column در ویوی dba_tables مشخص می کند که جدول tbl_counter از ستون RESERVABLE استفاده کرده است:

SQL> select has_reservable_column from user_tables where table_name like ‘TBL_COUNTER’;
HAS_RESERVABLE_COLUMN
------------------------------
YES

بلافاصله بعد از ایجاد جدول اصلی، journal table هم ایجاد خواهد شد اسامی journal tableها با پیشوند SYS_RESERVJRNL_ شروع می شوند:

SQL> select table_name from user_tables c where table_name like ‘SYS_RESERVJRNL_%’;
TABLE_NAME
--------------------
SYS_RESERVJRNL_110654

قصد داریم در session 1 مقدار counttt را “بعلاوه یک” کنیم:

'session 1'> select sid from v$mystat where rownum=1;
       SID
----------
        54
'session 1'> update tbl_counter set counttt=counttt+1 where id=1;
1 row updated

'session 1'> select * from tbl_counter;
        ID    COUNTTT
---------- ----------
     1	        0

'session 1'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from SYS_RESERVJRNL_110654;
ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$  ID COUNTTT_OP COUNTTT_RESERVED
---------------- ------------ -------------- --- ---------- ----------------
01001000B5150000 ACTIVE       UPDATE           1 +                         1

'session 1'> select SID,ID1,ID2,LMODE,block,request from v$lock where type=’TX’;
       SID        ID1        ID2      LMODE      BLOCK    REQUEST
---------- ---------- ---------- ---------- ---------- ----------
        54      65552       5557          6          0          0

به طور همزمان در session دیگری COUNTTT را “بعلاوه پنج” می کنیم:

'session 2'> select sid from v$mystat where rownum=1;
       SID
-----------
        40
'session 2'> update tbl_counter set counttt=counttt+5 where id=1;
1 row updated

SQL> select * from tbl_counter;
        ID    COUNTTT
---------- ----------
     1	         0

'session 2'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from SYS_RESERVJRNL_110654;
ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$ ID COUNTTT_OP COUNTTT_RESERVED
---------------- ------------ -------------- -- ---------- ----------------
04001D008E150000 ACTIVE       UPDATE          1 +                         5

'session 2'> select SID,ID1,ID2,LMODE,block,request from v$lock where type=’TX’;
       SID        ID1        ID2      LMODE      BLOCK    REQUEST
---------- ---------- ---------- ---------- ---------- ----------
        40     262173       5518          6          0          0
        54      65552       5557          6          0          0

همانطور که می بینید، Blocking Session اتفاق نیفتاده و هر دو session یک رکورد را در اختیار گرفته اند.

در ابتدای متن هم نوشتیم که journal table در هر session مقادیر همان session را نمایش خواهد داد:

'session 1'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from SYS_RESERVJRNL_110654;
ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$  ID COUNTTT_OP COUNTTT_RESERVED
---------------- ------------ -------------- --- ---------- ----------------
01001000B5150000 ACTIVE       UPDATE           1 +                         1

'session 2'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from SYS_RESERVJRNL_110654;
ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$ ID COUNTTT_OP COUNTTT_RESERVED
---------------- ------------ -------------- -- ---------- ----------------
04001D008E150000 ACTIVE       UPDATE          1 +                         5

با اجرای دستور commit وضعیت را مجددا بررسی می کنیم:

'Session 2'> commit;
Commit complete

'Session 2'> select * from tbl_counter;
        ID    COUNTTT
---------- ----------
     1	          5

'Session 2'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from usef.SYS_RESERVJRNL_110654;
no rows selected

'session 1'> select ORA_TXN_ID$,ORA_STATUS$,ORA_STMT_TYPE$,ID ,COUNTTT_OP,COUNTTT_RESERVED from SYS_RESERVJRNL_110654;
ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$  ID COUNTTT_OP COUNTTT_RESERVED
---------------- ------------ -------------- --- ---------- ----------------
01001000B5150000 ACTIVE       UPDATE           1 +                         1

'session 1'> commit;
Commit complete

'session 1'>  select * from tbl_counter;
        ID    COUNTTT
---------- ----------
      1	         6

ملاحظه می کنید که دو session به طور همزمان توانستند مقداری را به ستون COUNTTT اضافه کنند بدون آنکه Blocking sessionای رخ دهد.

در زمان استفاده از این قابلیت باید در نظر داشته باشیم که امکان بروزرسانی همزمان ستونهای از نوع RESERVABLE و non-RESERVABLE وجود ندارد:

SQL> create table tbl_counter(id number primary key,counttt number RESERVABLE,test varchar2(1000));
Table created

SQL> insert into tbl_counter values(1,0,'oracle DB');
1 row inserted

SQL> commit;
Commit complete

SQL> update tbl_counter set counttt=counttt+5,test='usefzadeh.com' where id=1;
'ORA-55735: Reservable and non-reservable columns cannot be updated in the same statement.'

در مثالهای قبلی جداولی که ایجاد کردیم صرفا یک ستون از نوع RESERVABLE داشتند در صورتی که هر جدول می تواند دارای چندین ستون از نوع RESERVABLE باشد:

SQL> Create table tbl_counter(
  2       id number primary key,
  3       counttt number RESERVABLE,
  4       counttt2 number RESERVABLE
  5       );
Table created

SQL> insert into tbl_counter values(1,0,0);
1 row inserted

SQL> update tbl_counter set counttt=counttt+3,counttt2=counttt2+5 where id=1;
1 row updated

SQL> select ORA_TXN_ID$,ORA_STMT_TYPE$,ID,COUNTTT_OP, COUNTTT_RESERVED,COUNTTT2_OP,COUNTTT2_RESERVED from usef.SYS_RESERVJRNL_111231;
ORA_TXN_ID$      ORA_STMT_TYPE$ ID COUNTTT COUNTTT_RESERVED COUNTTT COUNTTT2_RESERVED
---------------- -------------- -- ------- ---------------- ------- -----------------
05000C00D31C0000 UPDATE          1 +                      3 +                       5

قابلیت Lock-free reservation را می توان برای یک ستون به خصوص غیرفعال کرد:

SQL> alter table tbl_counter modify (counttt not reservable);
Table altered

همچنین این قابلیت را می توان برای ستونهای از نوع numeric فعال کرد:

SQL> alter table tbl_counter modify (counttt reservable);
Table altered

برای حذف این دسته از جداول هم باید ابتدا خصوصیت RESERVABLE را غیرفعال کنیم:

SQL> drop table tbl_counter;
ORA-55764: Cannot DROP or MOVE tables with reservable columns. First run "ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE)" and then DROP or MOVE the table.

SQL> alter table tbl_counter modify (counttt not reservable);
Table altered
SQL> drop table tbl_counter;
Table dropped 

در مورد حذف و یا دستکاری journal table هم باید در نظر داشت که امکان انجام اقداماتی نظیر DROP TABLE، RENAME، DML و … بر روی این جدول وجود ندارد:

SQL> drop table SYS_RESERVJRNL_110654;
ORA-55727: DML, ALTER, RENAME, and CREATE UNIQUE INDEX operations are not allowed on the reservation journal table “USEF”.”SYS_RESERVJRNL_110654”.