介绍：

enqueue - TX 是DML的锁，从事务初始化开始获得。直到事务结束提交或者回滚结束。

TYPE - TX, mode - 4 （Share）

1> ITL (interested transaction list) shortage
2 > Unique key enforcement
3 > Bitmap index entry

ITL是指block的事务槽的个数。interested transactin list，表示有多少个transaction可以使用block。这有段的参数
inittrans和maxtrans来控制的。没有ITL占用24个字节。常见的情况是block的空间都被占用完了。没有空间留个ITL。在这
种情况下，事务的会话开始请求share 4的enqueue。等待其他的会话释放ITL。

下面是block中的ITL的数据。有俩个会话在这个block上有事务操作。

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0003.002.00008aca 0x0080050f.018c.16 --U-    1 fsc 0x0000.019d4d56
0x02   0x0002.01f.00007f06 0x0080048f.0191.58 --U-    3 fsc 0x0000.019d4d53

测试案例：

下面是测试的过程：

创建一个表，要求有4条数据，并占满整个block的空间。
drop table t1;

create table t1 (c1 varchar2(3000));

insert into t1 values ('1');
insert into t1 values ('2');
insert into t1 values ('3');
insert into t1 values ('4');
commit;

update t1 set c1 = c1 || rpad('A',2040) where rownum <= 3;
commit;
update t1 set c1 = c1 || rpad('A',1911) where c1 = '4';
commit;

这4条记录所占的空间是：8058字节
select (2047*3+1917) from dual;

然后看看4条记录的block的使用情况。可以看到tosp是4个字节，也就是说只有4个字节是空闲的。

data_block_dump,data header at 0x2fa1064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x02fa1064
bdba: 0x024009f4
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1799
0x14:pri[1] offs=0xf9a
0x16:pri[2] offs=0x79b
0x18:pri[3] offs=0x1e
block_row_dump:
tab 0, row 0, @0x1799
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 1, @0xf9a
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
32 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 2, @0x79b
tl: 2047 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [2041]
33 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
tab 0, row 3, @0x1e
tl: 1917 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [1911]
34 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
......
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
End dump data blocks tsn: 9 file#: 9 minblk 2548 maxblk 2548

然后打开3个会话：

session 1:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAA';

session 2:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAB';

session 3:
update t1 set c1 = c1 where ROWID = 'AAAHiJAAJAAAAn0AAC';

当运行第3个会话时，会话被hang住了。

查看会话的等待时间：
set linesize 200
col event format a40
select sid,event,p1,p1raw,p2,p2raw,p3,p3raw from v$session_wait where sid in (16);
set linesize 100

sys@ORCL>select sid,event,p1,p1raw,p2,p2raw,p3,p3raw from v$session_wait where sid in (16);

SID EVENT P1 P1RAW P2 P2RAW P3 P3RAW
---------- ---------------------------------------- ---------- -------- ---------- -------- ---------- --------
16 enqueue 1415053316 54580004 524288 00080000 32862 0000805E

查看什么请求的enqueue。
sys@ORCL>select chr(to_char(bitand(p1,-16777216))/16777215)||
2 chr(to_char(bitand(p1, 16711680))/65535) "Lock",
3 to_char( bitand(p1, 65535) ) "Mode"
4 from v$session_wait
5 where event = 'enqueue';

Lock Mode
---- ----------------------------------------
TX 4

查看会话在等待那个object。发现是-1。
select SID, SERIAL#, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid = 16;

SID SERIAL# ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------- ------------- -------------- --------------- -------------
16 3 -1 0 0 0

查看有什么会话在持有这个enqueue
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 11                                           524288      32862          6          0 TX
Waiter: 16                                           524288      32862          0          4 TX

select * from dba_locks where blocking_others = 'Blocking';

查看dba_waiters;
set linesize 200
select waiting_session, holding_session, lock_type, lock_id1, lock_id2, mode_held, mode_requested from dba_waiters;
set linesize 100

WAITING_SESSION HOLDING_SESSION LOCK_TYPE LOCK_ID1 LOCK_ID2 MODE_HELD MODE_R
EQUESTED
--------------- --------------- -------------------------- ---------- ---------- ---------------------------------------
- ----------------------------------------
16 11 Transaction 524288 32862 Exclusive Share

查看dba_blockers,
select * from dba_blockers

HOLDING_SESSION
---------------
11

获取undo的信息：
select trunc(524288/power(2,16)) rbs, bitand(524288,to_number('ffff','xxxx'))+0 slot from dual;

RBS SLOT
---------- ----------
8 0

select usn,name from v$rollname where usn=8;

USN NAME
---------- ------------------------------
8 _SYSSMU8$

dump出undo segment头。
alter session set tracefile_identifier='MYSESSION';
alter system dump undo header '_SYSSMU8$';

index state cflags wrap#    uel         scn            dba            parent-xid    nub     stmt_num
------------------------------------------------------------------------------------------------
   0x00   10    0x80 0x805e 0x0002 0x0000.019dffb8 0x00800424 0x0000.000.00000000 0x00000001   0x00000000
   0x01    9    0x00 0x805d 0x001f 0x0000.019dfc4e 0x00800423 0x0000.000.00000000 0x00000001   0x00000000
   0x02    9    0x00 0x805d 0x000b 0x0000.019dfa8f 0x00800423 0x0000.000.00000000 0x00000001   0x00000000

SELECT dbms_utility.data_block_address_block(8389668) "BLOCK",
        dbms_utility.data_block_address_file(8389668) "FILE"
FROM dual;
     BLOCK       FILE
---------- ----------
      1060          2

dump出undo块的信息。
alter system dump datafile 2 block 1060;

*-----------------------------
* Rec #0xe slt: 0x00 objn: 30857(0x00007889) objd: 30857 tblspc: 9(0x00000009)
*       Layer: 11 (Row)   opc: 1   rci 0x00
Undo type: Regular undo    Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800424.0167.0c ctl max scn: 0x0000.019df9d9 prv tx scn: 0x0000.019dfa4d
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.01f.00007f06 uba: 0x0080048f.0191.58
                      flg: C---    lkc: 0     scn: 0x0000.019d4d53
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x024009f4 hdba: 0x024009f3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col 0: [2041]
31 41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

相关的data block的地址0x024009f4。
alter system dump datafile 9 block 2548;

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0002.005.00007f39 0x008004ab.0191.34 ----    1 fsc 0x0000.00000000
0x02   0x0008.000.0000805e 0x00800424.0167.0e ----    1 fsc 0x0000.00000000

data_block_dump,data header at 0x5c21064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x05c21064
bdba: 0x024009f4
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1e
avsp=0x4
tosp=0x4

可以看到itl是2，剩余空间是4。

ITPub博客

enqueue - TX mode 4 ITL争用

最新文章