DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

enq: TX — allocate ITL entry

#oracle #devops #linux #database

To implement Data Concurrency and Read Consistency, Oracle stores transaction metadata in part of the data block header. This area of the block header is called the Interested Transaction List (ITL) and can contain multiple slots.

A transaction that wants to acquire a row must occupy an ITL slot in the block header of the block that contains that row. In the slot the database records information such as the Transaction ID (XID), the Undo Block Address (UBA), and the number of rows the transaction has locked in that block. Each slot belongs to a single transaction, and each transaction can occupy only one slot per block.

When a transaction ends (COMMIT or ROLLBACK), its ITL slot becomes reusable by other transactions.

The size of each transaction slot is about 24 bytes. Note that physical row addresses (ROWIDs) are not stored in this space:

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

TYPE_SIZE
---------
24
Enter fullscreen mode Exit fullscreen mode

The initial number of transaction slots per block can be set using the INITRANS attribute at the object level. The default INITRANS is 1 for tables and 2 for indexes. If more ITL slots are needed, Oracle will automatically allocate additional slots unless either the number of ITL slots reaches the value of MAXTRANS or the block has no free space.

Example:

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

Table created

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

INI_TRANS  MAX_TRANS
---------            ---------
1                          255
Enter fullscreen mode Exit fullscreen mode

As shown, MAXTRANS defaults to 255, so (space permitting) up to 255 concurrent:

transactions can be active on a single block. In practice this is seldom reached because the number of rows per block is usually much smaller.

Note 1: Since Oracle 9i, even when INITRANS is set to 1, Oracle reserves two ITL slots per data block by default.
Note 2: From Oracle 10gR1 onward, MAXTRANS is not user-configurable; Oracle effectively uses 255.

Understanding ITL SLOT structure
To investigate the internal structure of transaction slots we will dump blocks from a table and inspect the trace output.

First, create a table with INITRANS set to 2:

SQL> create table VAHID.MYTBL (id number, text varchar2(4000)) PCTFREE 0 INITRANS 2;
Table created
Enter fullscreen mode Exit fullscreen mode

Identify the block ranges for the table via DBA_EXTENTS:

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

FILE_ID   BLOCK_ID     BLOCKS
-------        --------             ------
10               136                   8
Enter fullscreen mode Exit fullscreen mode

The table occupies 8 blocks (136 through 143). Dump those blocks to the session trace file with:

SQL> alter system dump datafile 10 block min 136 block max 143;
System altered
Enter fullscreen mode Exit fullscreen mode

Find the session trace file path:

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
Enter fullscreen mode Exit fullscreen mode

A portion of the trace showing ITL slots for one block looks like:

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
Enter fullscreen mode Exit fullscreen mode

Now we will run a transaction and watch how the ITL entries change.
Session 1:

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

1 row inserted

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

BLOCK_NUMBER
------------
141
Enter fullscreen mode Exit fullscreen mode

The inserted row resides in block 141. Dump that block:

SQL>  alter system dump datafile 10 block  141;
System altered
Enter fullscreen mode Exit fullscreen mode

Trace:

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
Enter fullscreen mode Exit fullscreen mode

As expected, the running transaction has occupied one ITL slot (0x01). Because it modified one row, LCK changed from 0 to 1 for that slot.

Without committing, issue another insert in the same session:

SQL> insert into VAHID.MYTBL values(2,’v.u’);
1 row inserted
Enter fullscreen mode Exit fullscreen mode

Dump the same block again (from another session) and you will see:

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
Enter fullscreen mode Exit fullscreen mode

LCK increased to 2, indicating two rows in block 141 are held by the same transaction. Further inserts continue to increment this value.

After several inserts (ids 3,4,5) the trace shows:

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
Enter fullscreen mode Exit fullscreen mode

Trace:

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
Enter fullscreen mode Exit fullscreen mode

Commit the transaction:

SQL> commit;
Commit complete
Enter fullscreen mode Exit fullscreen mode

Trace after commit:

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
Enter fullscreen mode Exit fullscreen mode

Now the XID remains present with Flag ‘-U-’ (committed) and the LCK value reflects the number of rows changed in that block.

We issue DELETE statements from different sessions to observe how Oracle allocates multiple ITL slots when several transactions concurrently modify rows within the same block.

Session 1:

SQL> delete VAHID.MYTBL where id=1;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 2:

SQL> delete VAHID.MYTBL where id=2;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 3:

SQL> delete VAHID.MYTBL where id=3;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

After these three concurrent deletes, the number of ITL slots increased from two to three. Each transaction touched one row in the block, and the trace shows three separate ITL entries:

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
Enter fullscreen mode Exit fullscreen mode

When two more deletes are issued concurrently (ids 4 and 5), the number of ITL slots grows to five:

Session 4:

SQL> delete mytbl where id=4;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 5:

SQL> delete mytbl where id=5;
1 row deleted
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

Note: you can correlate XIDs in dump files with V$TRANSACTION by converting the raw XID bytes, for example using UTL_RAW.REVERSE on substrings of XID.

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;
Enter fullscreen mode Exit fullscreen mode

Investigating enq: TX — allocate ITL entry (the wait event)
When it is not possible to allocate a new ITL slot for a transaction within a block, the session executing that transaction will become blocked and exhibit the wait event named enq: TX — allocate ITL entry.

Two reasons can prevent allocation of a new ITL slot:
1) the number of ITL slots has reached the configured MAXTRANS, or
2) there is no free space left in the data block to store another ITL slot.

The following scenario demonstrates blocking by filling a data block so that no further ITL slots can be created.

Create a table where PCTFREE=0 and INITRANS=1 (no reserved free space in blocks):

SQL> create table VAHID.MYTBL (id number, text varchar2(4000)) PCTFREE 0 INITRANS 1;
Enter fullscreen mode Exit fullscreen mode

Insert 6 rows with large row length so that all rows land in the same block and exhaust the block space:

SQL> insert into VAHID.MYTBL values(1,rpad(‘v’,2000,’v’));
SQL> insert into VAHID.MYTBL values(2,rpad(‘v’,2000,’v’));
SQL> insert into VAHID.MYTBL values(3,rpad(‘v’,2000,’v’));
SQL> insert into VAHID.MYTBL values(4,rpad(‘v’,2000,’v’));
SQL> insert into VAHID.MYTBL values(5,rpad(‘v’,2,’v’));
SQL> insert into VAHID.MYTBL values(6,rpad(‘v’,6,’v’));
SQL> commit;
Enter fullscreen mode Exit fullscreen mode

Check the block numbers:

SQL> SELECT distinct dbms_rowid.rowid_block_number(rowid) block# FROM VAHID.MYTBL;
BLOCK#
------
228
Enter fullscreen mode Exit fullscreen mode

All six rows are in block 228. The next inserted row goes into the next block because block 228 is full:

SQL> insert into VAHID.MYTBL values(7,’’);
1 row inserted

SQL> SELECT distinct dbms_rowid.rowid_block_number(rowid) block# FROM VAHID.MYTBL;
BLOCK#
------
228
229

SQL> rollback;
Rollback complete
Enter fullscreen mode Exit fullscreen mode

Thus block 228 cannot accommodate more rows. Given this situation, more than two concurrent transactions cannot obtain ITL slots in block 228 (remember: Oracle reserves two ITL slots per block by default).

Next we run three concurrent DELETE statements from three different sessions:

Session 1 (transaction 1):

SQL> delete VAHID.MYTBL where id=1;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 2 (transaction 2):

SQL> delete VAHID.MYTBL where id=2;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 3 (transaction 3):

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

SQL> delete VAHID.MYTBL where id=3;
Executing… (the session hangs)
Enter fullscreen mode Exit fullscreen mode

Session 3 will be blocked. Querying V$SESSION_WAIT shows the wait event:

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

EVENT
--------------------------------------------------
enq: TX – allocate ITL entry
Enter fullscreen mode Exit fullscreen mode

When either transaction 1 or 2 finishes (commits or rollbacks), session 3 will be released and continue.

Two mitigations for ITL-related waits
To reduce occurrences of this wait event you can:
— Increase PCT_FREE for the table so blocks retain free space for future updates/inserts, and/or
— Increase INITRANS for the table and indexes so more ITL slots are reserved initially.

Both changes require rebuilding the table/index.

Example:

SQL> alter table VAHID.MYTBL INITRANS 50;
Table altered
Enter fullscreen mode Exit fullscreen mode

Deadlock scenarios involving ITL waits

Consider the following deadlock scenario:

session 1 has occupied the last available ITL slot in block A of table VAHID.MYTBL1 and session 2 has occupied the last available ITL slot in block B of table VAHID.MYTBL2. Neither block can allocate a new ITL slot. If session 1 requests a slot in block B while session 2 requests a slot in block A, a circular wait occurs and Oracle detects a deadlock.

Example interaction that leads to a deadlock:

Session 1:

SQL> delete VAHID.MYTBL1 where id=3;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 2:

SQL> delete VAHID.MYTBL2 where id=4;
1 row deleted
Enter fullscreen mode Exit fullscreen mode

Session 2 now executes:

SQL> delete VAHID.MYTBL1 where id=5;
waiting…
Enter fullscreen mode Exit fullscreen mode

Session 1 executes:

SQL> delete VAHID.MYTBL2 where id=6;
ORA-00060: deadlock detected while waiting for resource
Enter fullscreen mode Exit fullscreen mode

The alert log references a trace file with detailed deadlock information. An excerpt from such a trace file is shown below :

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
Enter fullscreen mode Exit fullscreen mode

Final note:
— — — — — — -
ITL waits occur only for UPDATE and DELETE operations — they do not occur for INSERT. For INSERT, if a block has no free ITL slot or is full, Oracle simply moves to a different block for the new row.

Top comments (0)