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)