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

--session 1:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
      2190
SQL> update USEF.TBL1 set  id=1;
1 row updated

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

SQL> update USEF.TBL1 set id=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

ممکن است کاربر دوم که تراکنشش در حالت انتظار قرار دارد، برای ما اولویت بیشتری داشته باشد. در این حالت چه راهکاری وجود دارد؟

اوراکل در نسخه 23c با ارائه چند پارامتر این مسئله را قابل کنترل کرده است و این امکان را فراهم کرده تا اگر تراکنشهای با اولویت پایین، سد راه تراکنشهای با اولویت بالا شوند، به صورت خودکار و با گذراندن زمان مشخصی، تراکنشهای با اولویت کمتر rollback شوند.

پارامترهای مربوط به قابلیت Automatic transaction rollback را در قسمت زیر مشاهده می کنید:

txn_priority                                   string      HIGH
txn_auto_rollback_mode                         string      ROLLBACK
txn_auto_rollback_high_priority_wait_target    integer     2147483647
txn_auto_rollback_medium_priority_wait_target  integer     2147483647

پارامتر txn_priority یکی از پارامترهای مهم و اثر گذار در این زمینه است و از طریق آن می توانیم برای تراکنشها در سطح session اولویت تنظیم کنیم اولویت یک تراکنش می تواند HIGH، MEDIUM و یا LOW باشد که به صورت پیش فرض اولویت همه تراکنشها به HIGH تنظیم شده و عملا به صورت خودکار تراکنشی Rollback نخواهند شد.

SQL> alter session set txn_priority ={HIGH,MEDIUM,LOW};

پارامتر txn_auto_rollback_high_priority_wait_target هم دیگر پارامتر اثرگذار در این زمینه است این پارامتر مشخص می کند تراکنش با اولویت HIGH، حداکثر چند ثانیه باید منتظر تراکنشی با اولویت LOW و یا MEDIUM بماند بعد از مدت زمان تعیین شده، تراکنش با اولویت کمتر Rollback شده و session مربوط به آن kill خواهد شد.

در سناریوی زیر بیشتر با نقش این دو پارامتر آشنا خواهیم شد:

در ابتدا پارامتر txn_auto_rollback_high_priority_wait_target را به 5 ثانیه تنظیم می کنیم:

SQL> alter system set txn_auto_rollback_high_priority_wait_target=40;
System altered.

در session شماره یک تراکنشی را با اولویت پایین اجرا می کنیم:

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

SQL> alter session set txn_priority=LOW;
Session altered.

SQL> update USEF.TBL1 set id=1391 where id=1;
1 row updated.

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

SQL> alter session set txn_priority=LOW;
Session altered.

SQL> update USEF.TBL1 set id=1408 where id=1;
Executing…

با اجرای این دستور، session 1408 توسط session 1391 بلاک خواهد شد:

SQL> select sid, event, seconds_in_wait, blocking_session  from v$session where event like ‘%enq%’;  

در session شماره سه، تراکنشی با اولویت HIGH اجرا خواهد شد:

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

SQL> alter session set txn_priority=HIGH;
Session altered.

SQL> update USEF.TBL1 set id=2910 where id=1;
Executing…

Session شماره سه هم توسط session 1391 بلاک شده است اما با توجه به آنکه session شماره سه از اولویت بیشتری برخوردار است، حداکثر ظرف مدت 40 ثانیه از حالت block خارج می شود و رکورد مورد نظر را در اختیار خواهد گرفت:

SQL> select sid, event, seconds_in_wait, blocking_session  from v$session where event like ‘%enq%’;  

برای قابلیت Automatic transaction rollback دو ستون txn_priority و txn_priority_wait_target به ویوی v$transcation اضافه شده است و از طریق این ویو هم می توانیم بر روی این قابلیت نظارت داشته باشیم:

SQL> select ADDR,txn_priority, txn_priority_wait_target from v$transaction;
ADDR                TXN_PRI TXN_PRIORITY_WAIT_TARGET
----------------    ------- ------------------------
0000000085CA11A0   HIGH                          40

بعد از گذشت چهل ثانیه، session شماره سه، رکورد را در اختیار خواهد گرفت و دو session دیگر kill خواهند شد:

--session 3:
SQL> update USEF.TBL1 set id=2910 where id=1;
1 row updated.

--session 1:
SQL> select sid from v$mystat where rownum=1;
ORA-03113: end-of-file on communication channel
Process ID: 76244
Session ID: 1391 Serial number: 61119

--session 2:
SQL> select sid from v$mystat where rownum=1;
ORA-03113: end-of-file on communication channel
Process ID: 76246
Session ID: 1408 Serial number: 35823

بعد از اتفاقات فوق، پیام زیر در alert log نمایش داده می شود:

TEHRANPDB(3):Session (sid: 1391, serial: 61119, xid: 1.0.17832, txn_priority: "LOW") terminated by transaction (sid: 2910, serial: 43419, xid: -1.-1.-1, txn_priority: "HIGH") because of the parameter "txn_auto_rollback_high_priority_wait_target = 40"

2023-08-23T12:23:48.287763+04:30
TEHRANPDB(3):Session (sid: 1408, serial: 35823, xid: 9.27.23564, txn_priority: "LOW") terminated by transaction (sid: 2910, serial: 43419, xid: -1.-1.-1, txn_priority: "HIGH") because of the parameter "txn_auto_rollback_high_priority_wait_target = 40"