دیتابیس اوراکل برای پیاده سازی مفاهیمی چون Data Concurrency و Read Consistency، مشخصات تراکنشها را در قسمتی از هدر بلاک ثبت می کند. این قسمت از هدر بلاک که به آن (Interested Transaction List(ITL هم گفته می شود، می تواند شامل تعدادی slot باشد.

تراکنشها برای تصاحب یک رکورد، باید slotای را در هدر بلاکی که رکورد در آن قرار دارد، در اختیار بگیرند و اطلاعاتی نظیر (transaction ID(XID و (undo block address(UBA و همچنین تعداد رکوردهایی که تراکنش در این بلاک قفل کرده را در این قسمت ثبت کنند البته هر slot صرفا به یک transaction تعلق دارد و هر transaction در هر بلاک، تنها می تواند یک slot را اشغال کند.

با انجام commit و rollback و متعاقب آن، پایان یافتن تراکنش، itl slot می تواند توسط تراکنش دیگری استفاده شود.

حجم هر transaction slot حدودا 24 بایت می باشد البته قرار نیست آدرس فیزیکی رکوردها یا همان rowid در این فضا ذخیره شود:

SQL> select type_size from v$type_size where description=‘TRANSACTION VARIABLE HEADER’

24

تعداد اولیه Transaction Slotها برای هر بلاک، با خصیصه INITRANS قابل تنظیم است(در سطح هر شی). مقدار پیش فرض INITRANS برای جداول برابر با یک و برای ایندکسها به دو تنظیم شده است. زمانی که به ITL slot بیشتری نیاز باشد، به صورت خودکار اوراکل ITL slot جدیدی را رزرو خواهد کرد مگر انکه تعداد ITL slotها به عدد تعیین شده برای خصیصه MAXTRANS برسد و یا اینکه فضای کافی در بلاک موجود نباشد.

SQL> create table MYTBL (id number,text varchar2(4000)) ;

Table created

SQL> select p.INI_TRANS,p.MAX_TRANS from user_tables p where p.TABLE_NAME=’MYTBL’;

 INI_TRANS  MAX_TRANS

———- ———-

         1        255

همانطور که می بینید، مقدار پیش فرض خصیصه max_trans برابر با 255 می باشد پس بطور همزمان و در صورتی که فضای بلاک اجازه دهد، می توان حداکثر 255 تراکنش بر روی هر بلاک در حال اجرا داشت البته این عدد قابل توجه است زیرا که تعداد رکوردها حاضر در یک بلاک، در بسیاری از مواقع به این عدد نمی رسد.

توضیح 1: از اوراکل 9i، با وجود تنظیم initrans به عدد یک، اوراکل برای هر دیتابلاک دو transaction slot در نظر می گیرد.

توضیح 2: خصیصه Maxtrans از اوراکل 10gR1 غیرقابل تنظیم است و در صورت تنظیم، اوراکل از همان عدد 255 استفاده خواهد کرد.

 

آشنایی بیشتر با ساختار ITL SLOTها

برای آشنایی بیشتر با ساختار transaction slotها، قصد داریم از بلاکهای یک جدول دامپی گرفته و با مشاهده محتویات آن، به بررسی اطلاعات موجود در آن بپردازیم.

برای این کار، ابتدا جدولی را ایجاد می کنیم که تعداد initrans آن برابر با عدد دو تنظیم شده است:

SQL> create table mytbl (id number,text varchar2(4000)) PCTFREE 0 INITRANS 2;

Table created

برای تهیه دامپ از بلاکهای این جدول، باید شماره بلاکها را مشخص کنیم، به این جهت به ویوی dba_extents رجوع می کنیم:

SQL> select file_id,BLOCK_ID,blocks from dba_extents p where owner=’USEF’ and segment_name=’MYTBL’;

   FILE_ID   BLOCK_ID     BLOCKS

———- ———- ———-

        10        136          8

همانطور که می بینید، جدول mytbl شامل 8 بلاک می باشد(از شماره 136 تا 143). با کمک دستور زیر، اوراکل از این بلاکها dump گرفته و خروجی را در یک فایل تریس قرار خواهد داد:

SQL> alter system dump datafile 10 block min 136 block max 143;

System altered

پرس و جوی زیر، آدرس فایل تریس مربوط به session جاری را مشخص می کند.

SQL> SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE  s.sid =(SELECT sid FROM v$mystat WHERE ROWNUM = 1);

TRACEFILE

——————————————————————————–

/18c/base/diag/rdbms/db18c/db18c/trace/db18c_ora_11978.trc

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

[root@ol7 ~]# less /18c/base/diag/rdbms/db18c/db18c/trace/db18c_ora_11978.trc

Itl           Xid                                          Uba                 Flag    Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

قصد داریم با انجام تراکنشی، بررسی کنیم که چه تغییری در این ساختار ایجاد می شود:

–session 1:

SQL> insert into mytbl values(1,’v.u’);

1 row inserted

SQL> SELECT   dbms_rowid.rowid_block_number(rowid) block_number FROM mytbl;

BLOCK_NUMBER

————

         141

همانطور که می بینید، این رکورد در بلاک شماره 141 ثبت شده است پس با اجرای دستور زیر، از این بلاک دامپی را تهیه می کنیم:

SQL>  alter system dump datafile 10 block  141;

System altered

Itl           Xid                                    Uba                        Flag  Lck        Scn/Fsc

0x01   0x003e.00d.00000698  0x034000e3.03d7.1d  —-    1  fsc 0x0000.00000000

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

همانطور که انتظار می رود، تراکنش در حال اجرا، یکی از slotها را در اختیار گرفته است با توجه به این که این تراکنش صرفا یک رکورد را در این بلاک درگیر کرده است، مقدار ستون LCK برای 0x01 از عدد 0 به عدد 1 تغییر کرده است.

مجددا در همین session، بدون انکه دستور insert قبلی را commit یا rollback کرده باشیم، دستور insert دیگری را اجرا می کنیم:

–session 1:

SQL> insert into mytbl values(2,’v.u’);

1 row inserted

–session 2:

SQL>  alter system dump datafile 10 block  141;

System altered

Itl           Xid                                    Uba                        Flag  Lck        Scn/Fsc

0x01   0x003e.00d.00000698  0x034000e3.03d7.1e  —-    2  fsc 0x0000.00000000

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

همانطور که می بینید، عدد یک برای ستون LCK از 1 به 2 که به معنی دو رکورد از بلاک شماره 141 می باشد، تغییر کرده است.این اتفاق برای insertهای بعدی هم رخ می دهد:

SQL> insert into mytbl values(3,’v.u’);

1 row inserted

SQL> insert into mytbl values(4,’v.u’);

1 row inserted

SQL> insert into mytbl values(5,’v.u’);

1 row inserted

Itl           Xid                                    Uba                        Flag  Lck        Scn/Fsc

0x01   0x003e.00d.00000698  0x034000e3.03d7.21  —-    5  fsc 0x0000.00000000

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

در نهایت با اجرای دستور commit، به اجرای این تراکنش خاتمه می دهیم.

SQL> commit;

Commit complete

Itl           Xid                                    Uba                        Flag  Lck        Scn/Fsc

0x01   0x003e.00d.00000698  0x034000e3.03d7.21  –U-    5  fsc 0x0000.a7d3633b

0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

قصد داریم در sessionهای مجزا، دستور حذف رکوردهای درج شده را صادر کنیم:

–session 1:

SQL> delete mytbl where id=1;

1 row deleted

–session 2:

SQL> delete mytbl where id=2;

1 row deleted

–session 3:

SQL> delete mytbl where id=3;

1 row deleted

با اجرای دستورات فوق، خواهیم دید که مجموع تعداد transaction slotها از دو به سه تغییر کرده است که هر تراکنش صرفا اطلاعات یک رکورد را تغییر می دهد:

Itl           Xid                                     Uba                        Flag       Lck        Scn/Fsc

0x01   0x003b.001.0000052d  0x04000ddf.0292.1d  —-    1  fsc 0x0008.00000000

0x02   0x003c.007.000006ba  0x0400039b.03ea.01  —-    1  fsc 0x0008.00000000

0x03   0x0042.001.0000066e  0x04000e52.03f3.01  —-    1  fsc 0x0008.00000000

با اجرای دستور حذف دو رکورد بعدی، تعداد transaction slot، به عدد پنج خواهد رسید:

–session 4:

SQL> delete mytbl where id=4;

1 row deleted

–session 5:

SQL> delete mytbl where id=5;

1 row deleted

 

Itl           Xid                  Uba                                          Flag    Lck        Scn/Fsc

0x01   0x003b.001.0000052d  0x04000ddf.0292.1d  —-    1  fsc 0x0008.00000000

0x02   0x003c.007.000006ba  0x0400039b.03ea.01  —-    1  fsc 0x0008.00000000

0x03   0x0042.001.0000066e  0x04000e52.03f3.01  —-    1  fsc 0x0008.00000000

0x04   0x003a.016.0000052c  0x04000142.0328.1b  —-    1  fsc 0x0008.00000000

0x05   0x0039.01e.000004fc  0x0400031d.0377.0c  —-    1  fsc 0x0008.00000000

نکته: با کمک ویوی v$transaction و ستون XID موجود در فایل dump، می توان مشخصات دقیق تراکنشها را مشاهده کرد:

SQL> select utl_raw.reverse(substr(xid,1,4)) COL1,utl_raw.reverse(substr(xid,5,4)) COL2,utl_raw.reverse(substr(xid,9,8)) COL3 from v$transaction;

 

بررسی enq: TX – allocate ITL entry

زمانی که امکان ایجاد ITL slot جدیدی برای یک transaction در یک بلاک وجود نداشته باشد، sessionای که آن transaction را اجرا کرده است، با wait eventای بنام enq: TX – allocate ITL entry در حالت انتظار باقی خواهد ماند.

به دو دلیل ممکن است امکان ایجاد ITL slot جدید از بین برود:

1.رسیدن تعداد ITL slotها به عدد تعیین شده برای MAXTRANS

2.پر شدن فضای بلاک

در این قسمت قصد داریم با پر کردن فضای یک بلاک، sessionای را با این ITL wait در حالت انتظار قرار دهیم.

با اجرای دستورات زیر، جدول mytbl ایجاد می شود و با درج 6 رکورد در این جدول، فضای خالی یکی از بلاکهای این جدول، به صورت کامل پر می شود:

SQL> create table mytbl (id number,text varchar2(4000)) PCTFREE 0 INITRANS 1;

Table created

توجه: برای بلاکهای جدول mytbl فضای خالی در نظر گرفته نشده است(PCTFREE=0).

SQL> insert into mytbl values(1,rpad(‘v’,2000,’v’));

1 row inserted

SQL> insert into mytbl values(2,rpad(‘v’,2000,’v’));

1 row inserted

SQL> insert into mytbl values(3,rpad(‘v’,2000,’v’));

1 row inserted

SQL> insert into mytbl values(4,rpad(‘v’,2000,’v’));

1 row inserted

SQL> insert into mytbl values(5,rpad(‘v’,2,’v’));

1 row inserted

SQL> insert into mytbl values(6,rpad(‘v’,6,’v’));

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT  dbms_rowid.rowid_block_number(rowid) block# FROM mytbl;

    BLOCK#

———-

       228

       228

       228

       228

       228

       228

6 rows selected

همانطور که می بینید، همه این 6 رکورد در یک بلاک درج شده اند همچنین رکورد بعدی به دلیل پر بودن بلاک شماره 228، در بلاک بعدی ثبت می شود:

SQL> insert into mytbl values(7,”);

1 row inserted

SQL> SELECT  dbms_rowid.rowid_block_number(rowid) block# FROM mytbl;

    BLOCK#

———-

       228

       228

       228

       228

       228

       228

       229

7 rows selected

SQL> rollback;

Rollback complete

همانطور که ملاحظه شد، بلاک 228 گنجایش بیشتری ندارد با این وجود، در صورتی که روش ما برای بررسی پر بودن بلاک، روش درستی باشد(که هست)، امکان انجام بیش از دو تراکنش همزمان بر روی بلاک 228 وجود نخواهد داشت.

توجه: همانطور که قبلا هم ذکر شد، به صورت پیش فرض اوراکل در هر بلاک دو ITL slot را ایجاد خواهد کرد حتی اگر مقدار  INITRANS برابر با یک باشد.

در ادامه با اجرای سه دستور delete از سه session مختلف، این مسئله را بررسی می کنیم:

–session 1(transaction 1):

SQL> delete mytbl where id=1;

1 row deleted

Transaction شماره یک، هم slot و هم رکورد را در اختیار گرفته و بدون مشکل در حال اجراست.

–session 2(transaction 2):

SQL> delete mytbl where id=2;

1 row deleted

همانند تراکنش شماره یک، تراکنش شماره دو هم slot دوم را مشغول کرده و بدون انتظار در حالت اجرا قرار گرفته است.

–session 3(transaction 3):

SQL> select sid from v$mystat where rownum<=1;

       SID

———-

       382

SQL> delete mytbl where id=3;

Executing…

با اجرای تراکنش سوم، دستور delete در حالت انتظار باقی خواهد ماند. با رجوع به ویوی v$session_wait خواهیم دید که این session گرفتار wait eventای بنام enq: TX – allocate ITL entry شده است:

SQL> select event from v$session_wait where sid=382;

EVENT

—————————————————————-

enq: TX – allocate ITL entry

با خاتمه یافتن یکی از تراکنشهای شماره یک یا دو، session شماره سه هم از این حالت انتظار خارج می شود.

 

دو راهکاری برای  جلوگیری ITL WAIT

با افزایش pct_free جدول و همچنین افزایش خصیصه INITRANS در جدول و ایندکس، از تعداد دفعات رخ دادن این نوع از wait eventها کاسته می شود البته این کار نیازمند بازسازی جداول و ایندکسها خواهد بود:

SQL> alter table mytbl INITRANS 50;

Table altered

 

Deadlock و ITL WAIT

زمانی را تصور کنید که session شماره یک transaction slot آخر بلاک A از جدول mytbl1 را تصرف کرده است همچنین session شماره دو، آخرین  slot بلاک B از جدول mytbl2 را در اختیار دارد. با توجه به اینکه ایجاد slot جدیدی در هیچ کدام از این دو بلاک امکان پذیر نمی باشد، اگر session شماره یک slotای را از بلاک B درخواست کند و session شماره دو هم slotای را از بلاک A در خواست کند، Deadlock رخ خواهد داد.

برای مثال، دستور زیر به دلیل زخ دادن deadlock متوقف شده است:

–session 1:

SQL> delete mytbl1 where id=3;

1 row deleted

–session 2:

SQL> delete mytbl2 where id=4;

1 row deleted

–session 2:

SQL>delete mytbl1 where id=5

waiting…

–session 1:

SQL> delete mytbl2 where id=6;

ORA-00060: deadlock detected while waiting for resource

با رجوع به فایل تریسی که در alert log آدرس آن موجود است، اطلاعات کاملتری را در مورد این deadlock مشاهده خواهیم کرد:

2020-02-16T13:38:08.719469+03:30

ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /18c/base/diag/rdbms/db18c/db18c/trace/db18c_ora_29336.trc.

قسمتی از محتویات فایل تریس را در قسمت زیر می بینید:

Deadlock graph:

                                          ————Blocker(s)———–  ————Waiter(s)————

Resource Name                             process session holds waits serial  process session holds waits serial

TX-0042001D-00000710-00000000-00000000         89     137     X        31007      92     510           S  27570

TX-003E0011-0000075C-00000000-00000000         92     510     X        27570      89     137           S  31007

—– VKTM Time Drifts Circular Buffer —–

session 137: DID 0001-0059-00000030     session 510: DID 0001-005C-00000006

session 510: DID 0001-005C-00000006     session 137: DID 0001-0059-00000030

 —– Information for waiting sessions —–

Session 137:

  sid: 137 ser: 31007 audsid: 570616 user: 104/USEF

    flags: (0x100041) USR/- flags2: (0x40009) -/-/INC

    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-

  current SQL:

  delete mytbl2 where id=6

 Session 510:

  sid: 510 ser: 27570 audsid: 570619 user: 104/USEF

    flags: (0x100041) USR/- flags2: (0x40009) -/-/INC

    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-

  pid: 92 O/S info: user: oracle, term: UNKNOWN, ospid: 30324

    application name: PL/SQL Developer, hash value=1190136663

    action name: Command Window – New, hash value=254318129

  current SQL:

  delete mytbl1 where id=5

 —– End of information for waiting sessions —–

 *** 2020-02-16T13:38:06.735658+03:30

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)

—– Error Stack Dump —–

—– Current SQL Statement for this session (sql_id=91q18bnn0zm8b) —–

delete mytbl2 where id=6

    The history is displayed in reverse chronological order.

     Current Wait Stack:

     0: waiting for ‘enq: TX – allocate ITL entry’

        name|mode=0x54580004, usn<<16 | slot=0x3e0011, sequence=0x75c

        wait_id=119 seq_num=120 snap_id=1

        wait times: snap=10.214547 sec, exc=10.214547 sec, total=10.214547 sec

        wait times: max=32.000000 sec, heur=10.214547 sec

        wait counts: calls=3 os=3

        in_wait=1 iflags=0x15a0

    There is at least one session blocking this session.

      Dumping 1 direct blocker(s):

        inst: 1, sid: 510, ser: 27570

      Dumping final blocker:

        inst: 1, sid: 510, ser: 27570

    There are 1 sessions blocked by this session.

    Dumping one waiter:

      inst: 1, sid: 510, ser: 27570

      wait event: ‘enq: TX – allocate ITL entry’

        p1: ‘name|mode’=0x54580004

        p2: ‘usn<<16 | slot’=0x42001d

        p3: ‘sequence’=0x710

      row_wait_obj#: 124758, block#: 175, row#: 0, file# 10

      min_blocked_time: 0 secs, waiter_cache_ver: 35252

    Wait State:

      fixed_waits=0 flags=0x22 boundary=(nil)/-1

    Session Wait History:

        elapsed time of 0.000066 sec since current wait

     0: waited for ‘enq: TX – allocate ITL entry’

        name|mode=0x54580004, usn<<16 | slot=0x41001a, sequence=0x777

        wait_id=118 seq_num=119 snap_id=1

        wait times: snap=5.000921 sec, exc=5.000921 sec, total=5.000921 sec

        wait times: max=5.000000 sec

        wait counts: calls=2 os=2

        occurred after 0.000075 sec of elapsed time

     1: waited for ‘enq: TX – allocate ITL entry’

        name|mode=0x54580004, usn<<16 | slot=0x3e0011, sequence=0x75c

        wait_id=117 seq_num=118 snap_id=1

        wait times: snap=15.999972 sec, exc=15.999972 sec, total=15.999972 sec

        wait times: max=16.000000 sec

        wait counts: calls=6 os=6

        occurred after 0.000045 sec of elapsed time

     2: waited for ‘enq: TX – allocate ITL entry’

        name|mode=0x54580004, usn<<16 | slot=0x41001a, sequence=0x777

        wait_id=116 seq_num=117 snap_id=1

        wait times: snap=4.999940 sec, exc=4.999940 sec, total=4.999940 sec

        wait times: max=5.000000 sec

        wait counts: calls=2 os=2

        occurred after 0.000072 sec of elapsed time

itl wait صرفا برای update و delete رخ می دهد و در زمان insert این نوع از event wait اتفاق نمی افتد زیرا که اگر itlای در بلاک خالی نباشد، اوراکل برای insert به سراغ بلاک بعدی می رود.