itl deadlock(出自http://www.orawh.com/80.html)

昨天有同事碰到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 wner=’TEST’ and  table_name=’TEST’;

 INI_TRANS
———-
         1

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0010.023.0000229f  0×01c01177.05f5.1c  C—    0  scn 0×0005.e88ed5fb
0×02   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×03   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.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.

3 Responses to “itl deadlock”

Feed for this Entry Trackback Address

---

1. 1 NinGoo
   Mar 1st, 2007 at 11:21 pm

   我在Oracle10203上重复同样的实验，没有发生死锁。不知道是不是oracle有改进了。

   从理论上说
   session 3和session 4确实都等待enq: TX - allocate ITL entry，但并不需要分别等待对方释放ITL，只需要session 1或者session 2释放ITL，操作就可以继续

2. 2 piner
   Apr 26th, 2007 at 5:45 am

   这个应当会重复出现的

1. 1 DBA Story (dba on unix/linux) - 推荐：深入研究ITL阻塞与ITL死锁
   Pingback on May 14th, 2007 at 6:33 am

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15142212/viewspace-566518/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15142212/viewspace-566518/