昨天有同事碰到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.