在数据块内事务槽不足或没有空间扩展时，对同一个数据块内数据行的dml操作将表现为“数据块锁”，等待事件为“enq: TX - allocate ITL entr”，验证如下。

1，测试表准备
[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 19 15:14:30 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> create table t(x int) pctfree 0;

Table created.

SQL> insert into t select rownum n from dba_objects where rownum<1000;

999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
2         dbms_rowid.rowid_block_number(rowid) bno,
3         count(*) cnt
4    from t
5   group by dbms_rowid.rowid_relative_fno(rowid),
6            dbms_rowid.rowid_block_number(rowid);

       FNO        BNO        CNT
---------- ---------- ----------
         4      67252        733
         4      67253        266

SQL>
SQL> select x,
2         dbms_rowid.rowid_relative_fno(rowid) fno,
3         dbms_rowid.rowid_block_number(rowid) bno
4    from t
5   where x in (1, 2, 3, 4);

         X        FNO        BNO
---------- ---------- ----------
         1          4      67252
         2          4      67252
         3          4      67252
         4          4      67252

2，多个会话更新同一块中的不同行

--会话1
SQL> select distinct sid from v$mystat;

SID
----------
769

SQL> update t set x=x where x=1;

1 row updated.

SQL>

--会话2
SQL> select distinct sid from v$mystat;

SID
----------
755

SQL> update t set x=x where x=2;

1 row updated.

SQL>

--会话3

SQL> select distinct sid from v$mystat;

SID
----------
831

SQL> update t set x=x where x=3;
--blocked

--会话4
SQL> select distinct sid from v$mystat;

SID
----------
763

SQL> update t set x=x where x=4;

--blocked

3,查看等待事件
--会话5
SQL> select sid, type, id1, id2, lmode, request, ctime, block
2    from v$lock
3   where sid in (769, 755, 831, 763)
4   order by ctime;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       763 TX     458756      60873          0          4        298          0
       763 TM     155086          0          3          0        303          0 --会话4
       831 TX     458756      60873          0          4        339          0
       831 TM     155086          0          3          0        344          0 --会话3
       755 TX      65549      52755          6          0        357          0
       755 TM     155086          0          3          0        357          0 --会话2
       769 TX     458756      60873          6          0        397          1
       769 TM     155086          0          3          0        397          0 --会话1

8 rows selected.

SQL>
SQL> col sid for 9999
SQL> r
1 select sid,
2         event,
3         p1,
4         p2,
5         p3,
6         row_wait_obj#,
7         row_wait_block#,
8         last_call_et,
9         blocking_session,
10         wait_time,
11         seconds_in_wait
12    from v$session
13* where sid in (769, 755, 831, 763)

SID EVENT                                  P1         P2         P3 ROW_WAIT_OBJ# ROW_WAIT_BLOCK#
----- ------------------------------ ---------- ---------- ---------- ------------- ---------------
LAST_CALL_ET BLOCKING_SESSION WAIT_TIME SECONDS_IN_WAIT
------------ ---------------- ---------- ---------------
769 SQL*Net message from client    1650815232          1          0           340           50425 --会话1
        1021                           0            1021

755 SQL*Net message from client 1650815232 1 0 -1 0 --会话2
981 0 981

831 enq: TX - allocate ITL entry 1415053316 458756 60873 -1 0 --会话3
968 769 0 963

763 enq: TX - allocate ITL entry 1415053316 458756 60873 -1 0 --会话4
927 769 0 922

==〉1,会话1、2正常更新，说明该块有两个itl slot，会话3、4被会话1阻塞，等待事件为enq: TX - allocate ITL entry；
2,v$lock中只给出了该会话阻塞了其他会话，并没有给出被阻塞会话数量（v$lock.block:A value of either 0 or 1, depending on whether or not the lock in question is the blocker）

SQL> alter system dump datafile 4 block 67252;

System altered.

--查看该块dump：
Block header dump: 0x010106b4
Object id on Block? Y
seg/obj: 0x25dce csc: 0x56f.f8d44b4d itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x10106b1 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0001.00d.0000ce13 0x008f0252.2f6d.07 ----    1 fsc 0x0000.00000000
0x02   0x0007.004.0000edc9 0x008f1cec.34c4.03 ----    1 fsc 0x0000.00000000

data_block_dump,data header at 0xe9eb464
===============
tsiz: 0x1f98
hsiz: 0x5cc
pbl: 0x0e9eb464
bdba: 0x010106b4
     76543210
flag=--------
ntab=1
nrow=733 --行数
frre=-1
fsbo=0x5cc
fseo=0xbf7
avsp=0x7
tosp=0x7
0xe:pti[0] nrow=733 offs=0
0x12:pri[0] offs=0x1904
0x14:pri[1] offs=0x190a
0x16:pri[2] offs=0x1910
0x18:pri[3] offs=0x1916
0x1a:pri[4] offs=0x191c
0x1c:pri[5] offs=0x1922
0x1e:pri[6] offs=0x1928
...
0x5c6:pri[730] offs=0x11f9
0x5c8:pri[731] offs=0x1200
0x5ca:pri[732] offs=0x1207
block_row_dump:
tab 0, row 0, @0x1904 ==>6404
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1 ==>itl 2
col 0: [ 2] c1 02   ==>x=1
tab 0, row 1, @0x190a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1 ==>itl 1
col 0: [ 2] c1 03   ==>x=2
tab 0, row 2, @0x1910
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 04   ==>x=3
tab 0, row 3, @0x1916
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 05   ==>x=4
tab 0, row 4, @0x191c
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 06
tab 0, row 5, @0x1922
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 07
tab 0, row 6, @0x1928
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 08
...
tab 0, row 730, @0x11f9
tl: 7 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 3] c2 08 20   ==>x=731
tab 0, row 731, @0x1200
tl: 7 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 3] c2 08 21   ==>x=732
tab 0, row 732, @0x1207
tl: 7 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 3] c2 08 22   ==>x=733
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 67252 maxblk 67252

ITPub博客

数据块内事务槽不足导致数据块锁

最新文章