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)) ;
Tablecreated.
_dexter@FAKE>insert into tb_bitmap_test select level , 'F'from dual connect by level <= 3;
3 rowscreated.
_dexter@FAKE>insert into tb_bitmap_test select level , 'M'from dual connect by level <= 2;
2 rowscreated.
_dexter@FAKE>create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ;
Indexcreated.
_dexter@FAKE>select * from tb_bitmap_test ;
ID GE
------------
1 F
2 F
3 F
1 M
2 M
session1 session_id=144:
_dexter@FAKE>update tb_bitmap_test set gender='M' where id=1 and gender='F' ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>delete tb_bitmap_test where gender='M' and id = 1;
waiting...
session3 session_id=9 :
_dexter@FAKE>insert into tb_bitmap_test values (1,'S') ;
1 rowcreated.
锁情况：
_sys@FAKE>@lock
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TM 82847 3 0 0
9 TX 196626 6 0 0
18 TX 327710 6 0 0
18 TM 82847 3 0 0
18 TX 589854 0 4 0
144 TX 589854 6 0 1
144 TM 82847 3 0 0
7 rowsselected.
不管是gender='M' 或者 'F' ,只要涉及到这两个字段的值的dml操作都将进入等待当中(包括insert)
因为第一个session锁住了整个bitmap segment。但是只要gender的值不涉及M或者F即可顺利执行。所以session3顺利的执行。
Session1
Session2
Session3
Description
T1
update tb_bitmap_test set gender='M' where id=1 and gender='F' ;
因为有了Bitmap索引，所以这个操作会索引表中所有gender=’M’和‘F’的记录，并且会阻塞相关的insert操作
T2
delete tb_bitmap_test where gender='M' and id = 1;
waiting…
这里发生了阻塞
T3
insert into tb_bitmap_test values (1,'S') ;
只要gender的值不等于M或者F即可顺利执行
2.模拟RI锁定导致阻塞的场景。
初始化环境
_dexter@FAKE>create table tun2_p (id int primary key) ;
Tablecreated.
_dexter@FAKE>create table tun2_c (pid references tun2_p(id)) ;
Tablecreated.
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
这里因为有引用完整性约束，子表中的内容必须与父表中的内容匹配。因为父表中没有id=1的记录，所以这里报错
主表插入
_dexter@FAKE>insert into tun2_p values (2) ;
1 rowcreated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 589829 6 0 0
TUN2_C 9 TM 82952 3 0 0
主表更新(子表中没有引用的记录)
_dexter@FAKE>update tun2_p set id=3 where id=2 ;
1 rowupdated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
9TX 262144 6 0 0
TUN2_P 9 TM 82949 3 0 0
主表删除(子表中没有引用的记录)
_dexter@FAKE>delete tun2_p where id=3 ;
1 rowdeleted.
lock status:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
9TX 524294 6 0 0
TUN2_P 9 TM 82949 3 0 0
如果upadte和delete操作中不包含子表引用的记录，就不会对子表加锁。
而insert相对比较复杂一点，它会级联的将子表锁定。
如果在子表引用的记录上发生更改，则会报错。例如：
_dexter@FAKE>update tun2_p set id=3 where id=1 ;
updatetun2_p set id=3 where id=1
*
ERROR atline 1:
ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated - child record found
子表插入
_dexter@FAKE>insert into tun2_c values (2) ;
1 rowcreated.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 589825 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表更新
_dexter@FAKE>update tun2_c set pid=1 where pid=2 ;
1 rowupdated.
lock
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 655390 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表删除
_dexter@FAKE>delete from tun2_c where pid=1 ;
2 rowsdeleted.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 196635 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表的记录一定会引用到父表的记录，所以在对子表进行dml操作的时候，都会锁定父表。
复杂示例
两个表中现在么有任何记录。
session1 session_id=9:
_dexter@FAKE>insert into tun2_p values (1) ;
1 rowcreated.
看一下锁的情况：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_C 9 TM 82952 3 0 0
TUN2_P 9 TM 82949 3 0 0
9TX 262149 6 0 0
可以看到，当向父表中插入记录的时候，会同时锁定父表和子表，加表的3级共享锁。
session1没提交之前其他事���无法看到父表中的id=1的记录，我们再来尝试一下向子表中插入pid=1的记录
session2 session_id=18:
_dexter@FAKE>insert into tun2_c values (1) ;
waiting ...
可以看到session2 进入了阻塞状态，我们来查看一下锁的情况
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_C 9 TM 82952 3 0 0
9TX 262149 6 0 1
TUN2_P 9 TM 82949 3 0 0
18TX 262149 0 4 0
TUN2_C 18 TM 82952 3 0 0
18TX 589848 6 0 0
TUN2_P 18 TM 82949 3 0 0
7 rowsselected.
首先我们可以看到，session2也有两个TM表锁，分别锁定了子表和父表。这说明在子表更新数据的时候，也会对引用的对象加锁。
然后我们还看到，子表陷入了等待当中。
这是因为session2 中的事务是否能够成功执行，取决于session1 中的事务状态。而session1 中的事务现在是悬而未决的状态。
是不是有点和读一致性搞混了？觉得第二个session中的事务不应该进入阻塞当中，而是直接报错？
它不像读一致性，可以在查询的时候根据undo获取一个一致性视图。
在事务执行的时候，只和数据的当前状态相关。
第一个session的事务rollback后
session2就会报错
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
Session1
Session2
Description
T1
insert into tun2_c values (1) ;
T2
insert into tun2_c values (1) ;
waiting…
正常理解，这里应该直接报错，ORA-02291才对，但是这里没有，因为父表中id=1的记录还是悬而未决的状态。这是智能呢？还是智能呢？还是智能呢？那就是智能吧。
T3
rollback
T4
Raise error
ORA-02291: integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
一切都明了了，报错了。
3.从mode 2-6 的TM锁相互间的互斥示例。
再次引用这张表
RS|SS
RX|SX
S
SRX|SSX
X
RS|SS
√
√
√
√
×
RX|SX
√
√
×
×
×
S
√
×
√
×
×
SRX|SSX
√
×
×
×
×
X
×
×
×
×
×
介绍一些操作
lock table tun2_tab in ROW SHARE mode ;
lmode=2
lock table tun2_tab in ROW EXCLUSIVE mode ;
lmode=3
lock table tun2_tab in SHARE MODE ;
lmode=4
lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
lmode=5
lock table tun2_tab in EXCLUSIVE MODE ;
lmode=6
下面的示例演示验证上表的内容
Row Share (RS)
Also called a subshare table lock (SS)
Session1 session_id=35:
dexter@STARTREK>create table tun2_tab (x int) ;
Tablecreated.
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode nowait ;
Table(s)Locked.
session2 session_id=160:
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
dexter@STARTREK>commit;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in EXCLUSIVE MODE ;
waiting ...
看一下锁的情况
sys@STARTREK>@lock
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 2 0 1
160 TM 76917 0 6 0
RS|SS锁和X锁是不能并发的，但是可以兼容其他类型的锁。
Row Exclusive TableLock (RX|SX)
Also called a subexclusive table lock (SX)
Session1 session_id=35:
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
Session2 session_id=160：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
waiting ...
看一下锁的情况
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 3 0 1
160 TM 76917 0 4 0
RX|SX与S锁是无法并发的，经测试SRX|SSX锁也一样无法与RX|SX锁并发。
94
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
