昨天有同事碰到itl竞争导致的deadlock，这在以前比较少见，那么在这里我们来作个实验重现一下，
从理论上解释为什么itl竞争会导致deadlock。
create table test(a number);
insert into test values(1);
SQL 10G>insert into test select * from test;
1 row created.
SQL 10G>/
2 rows created.
SQL 10G>/
4 rows created.
SQL 10G>/
8 rows created.
SQL 10G>/
16 rows created.
SQL 10G>/
32 rows created.
SQL 10G>/
64 rows created.
SQL 10G>/
128 rows created.
SQL 10G>/
256 rows created.
SQL 10G>/
512 rows created.
SQL 10G>/
1024 rows created.
SQL 10G>/
2048 rows created.
SQL 10G>/
4096 rows created.
SQL 10G>/
8192 rows created.
SQL 10G>/
16384 rows created.
SQL 10G>commit;
Commit complete.
SQL 10G>alter table test move pctfree 0;
Table altered.
move表，缩小pctfree为0使表不能创建多余的itl
SQL 10G>select ini_trans from dba_tables where owner='TEST' and table_name='TEST';
INI_TRANS
----------
1
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.023.0000229f 0x01c01177.05f5.1c C--- 0 scn 0x0005.e88ed5fb
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
这里可以看到虽然dba_tables里面的ini_trans为1，但是实际上block里面默认的itl是3个，从下面的实验中可以发现。
SQL 10G>select distinct dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block# from test order by block#;
BLOCK#
----------
61612
61613
61614
61615
61616
61618
61619
61620
61621
61622
61623
BLOCK#
----------
61624
61626
61627
61628
61629
61630
61631
61632
61634
61635
72057
BLOCK#
----------
72058
72059
72060
72061
72062
72063
72064
72065
72066
72067
72068
BLOCK#
----------
72069
72070
72071
72072
72817
72818
72819
72820
72821
72822
72823
BLOCK#
----------
72824
45 rows selected.
我们挑2个block来做实验，block 61612,block 61613
session 1:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1;
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61613 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1;
session1更新分别更新61612,61613的第2行，分别占据61612,61613的一个itl entry
session 2:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2;
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61613 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2;
session2更新分别更新61612,61613的第3行，分别占据61612,61613的另一个itl entry
session 3:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=0;
session3 更新61612的第1行，占据61612的第3个itl entry
session 4:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61613 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;
session4 更新61613的第4行，占据61613的第3个itl entry,这个时候2个block的itl entry都被用完，
每个block itl都是3个并且不能扩展
session 4:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;
session4 更新61612的第4行，这时候出现"enq: TX - allocate ITL entry"等待
session 3:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61613 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=0;
session3 更新61613的第1行，出现"enq: TX - allocate ITL entry"等待
session 4:
SQL 10G>update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61612 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
而当session3 更新61613的第1行时，session4这边报出了deadlock,看下图
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0013002e-0001ac43 18 154 X 19 143 S
TX-0014002a-0001c990 19 143 X 18 154 S
session 154: DID 0001-0012-0001B9E7 session 143: DID 0001-0013-00000DB7
session 143: DID 0001-0013-00000DB7 session 154: DID 0001-0012-0001B9E7
Rows waited on:
Session 143: no row
Session 154: no row
Information on the OTHER waiting sessions:
Session 143:
pid=19 serial=60295 audsid=6783 user: 55/TEST
O/S info: user: oracle, term: pts/1, ospid: 664, machine: csdba
program:
sqlplus@csdba (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update test set a=a where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=61613 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=0
End of information on OTHER waiting sessions.
Herramientas 
Ayuda
