T3
delete tun2_tab where id = 2 ;
waiting…
发生等待。
ITL 引起的阻塞
当block中没有多余的空间来添加ITL entry的时候，就会发生阻塞。具体可以看下面的例子：
_dexter@FAKE>create table tb_itl (id int , name varchar2(4000)) pctfree 0 initrans 1 ;
Tablecreated.
_dexter@FAKE>insert into tb_itl select level , 'd' from dual connect by level <= 10000 ;
10000 rowscreated.
_dexter@FAKE>commit ;
Commitcomplete.
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) ;
10000 rowsupdated.
_dexter@FAKE>commit ;
Commitcomplete.
上面的操作保证至少第一个block中不会有多余的空间
selectt.id,
dbms_rowid.rowid_relative_fno(t.rowid)as "FNO#",
dbms_rowid.rowid_block_number(t.rowid)as "BLK#",
dbms_rowid.rowid_row_number(t.rowid) as"ROW#"
from dexter.tb_itl t
whererownum<5 ;
_dexter@FAKE>select t.id,
2 dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",
3 dbms_rowid.rowid_block_number(t.rowid) as "BLK#",
4 dbms_rowid.rowid_row_number(t.rowid) as "ROW#"
5 from dexter.tb_itl t
6 where id<5 ;
ID FNO# BLK# ROW#
-------------------- ---------- ----------
1 6 187 0
2 6 187 1
3 6 187 2
4 6 187 3
先dump一下看一下block中剩余有几个itl slot
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.00000a60 0x00c000ef.0284.14 C--- 0 scn 0x0000.003d7a84
0x02 0x0003.01c.000009ea 0x00c00153.028c.1c ---- 733 fsc 0x0000.00000000
只有2个事务槽了。
下面内容引用自网络。
每个ITL entry包括以下的内容：
Transactionid（Xid）: 8bytes。其中包括rollback segment number, transaction table中的slot number等。
Undoblock address（Uba）: 8bytes。其中包括rollback segment block的DBA，sequence number等。
Flags:1nibble。
---- =transaction is active, or committed pending cleanout
C--- =transaction has been committed and locks cleaned out
-B-- =this undo record contains the undo for this ITL entry
--U- =transaction committed (maybe long ago); SCN is an upper bound
---T =transaction was still active at block cleanout SCN
Locks:3nibbles. 也就是所谓的行级锁（row-level locks）
SCN orfree space credit: 6bytes. 如果这个事务已经clean out，这个值就是SCN；否则，前两个字节表示由这个事务释放的此block中的空间数。
我们来尝试更改一下数据
session1 session_id=144：
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) where id =1 ;
1 rowupdated.
session2 session_id=18：
_dexter@FAKE> update tb_itl set name=lpad('x',2000,name)where id =2 ;
1 rowupdated.
session3 session_id=21：
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) where id =3 ;
waiting...
看一下锁信息
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in ('TM', 'TX')
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
18 TX 393241 6 0 1
18 TM 82846 3 0 0
21 TX 393241 0 4 0
21 TM 82846 3 0 0
144 TX 131088 6 0 0
144 TM 82846 3 0 0
6 rowsselected.
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 21 ;
SID SEQ# EVENT
---------- ---------------------------------------------------------
21 268 enq: TX - allocate ITL entry
_sys@FAKE>
因为在block 187中无法添加更多的ITL entry（拓展一个只需要24b）而引发的阻塞。
|
|
Session1 |
Session2 |
Session3 |
Description |
| T1 |
update tb_itl set name=lpad('x',2000,name) where id =1 ; |
|
|
|
| T2 |
|
update tb_itl set name=lpad('x',2000,name) where id =2 ; |
|
|
| T3 |
|
|
update tb_itl set name=lpad('x',2000,name) where id =3 ; waiting… |
常理来说这里应该顺利执行才对，可是事实上，因为block中无法再拓展出ITL entry，所以它被阻塞。 |
通常情况下不会发生这种情况。
解决办法：设置表的inittrans 参数为合理值。
Bitmap 引起的阻塞
_dexter@FAKE>create table tb_bitmap_test (id number , gender varchar2(1)) ;
Tablecrea