从mode 2-6的TM锁相互间的互斥示例
从mode 2-6的TM锁相互间的互斥示例
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>insert into test values(1);
1 row created.
BYS@ bys001>commit;
Commit complete.
BYS@ bys001>select * from test;
A
----------
1
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
no rows selected
####################################
等级共享锁 2 row share----rs
把在19会话将test表设置为等级共享锁模式
BYS@ bys001>lock table test in row share mode;
Table(s) Locked.
可以查询到是在test表加了个模式2的锁
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
BYS@ bys001>select object_name from dba_objects where object_id=77389;
OBJECT_NAME
-------------------------------
TEST
在另一个会话147上做DML操作：
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>insert into test values(2);
1 row created.
BYS@ bys001>delete from test where a=1;
1 row deleted.
BYS@ bys001>select * from test;
A
----------
2
BYS@ bys001>update test set a=22 where a=2;
1 row updated.
BYS@ bys001>select * from test;
A
----------
22
在会话19上查询：
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 2 0 0
147 TM 77389 0 3 0 0
147 TX 196621 28393 6 0 0
小结：行级共享锁，是限制最少的TM锁，可以提供最高程度的并发性。其他会话可以对锁定的表进行任何类型的DML操作，还可以与其他会话锁并存。
################################################################################
等级排他锁 row exclusive table lock---RX 3
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>lock table test in row exclusive mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
3级锁是一个灵活性比较大的锁，insert delete update 都可以产生一个3级锁，也允许其他事务来修改锁定的表
BYS@ bys001>select * from test;
A
----------
22
Elapsed: 00:00:00.00
BYS@ bys001>update test set a=99 where a=22;
1 row updated.
Elapsed: 00:00:00.01
BYS@ bys001>select * from test;
A
----------
99
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
Elapsed: 00:00:00.01
BYS@ bys001>insert into test values(55);
1 row created.
Elapsed: 00:00:00.00
BYS@ bys001>select * from test;
A
----------
22
55
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
147 TM 77389 0 3 0 0
147 TX 589824 28423 6 0 0
block列全部是0,没有阻塞。
小结：行级排他锁，通常已经有事务在修改行或者select…for update 修改结果集。允许其他事务对锁定的表进行select insert update delete 或 lock table 同时锁定一张表。
#####################################################################################
共享锁 share table lock 4
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
19
BYS@ bys001>select * from test;
A
----------
99
55
BYS@ bys001>lock table test in row share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 0
模式标识：4
4级锁禁止其他会话对锁定的表进行DML操作但可以select查询，还允许多个事物一起持有
在会话147上进行删除操作，
BYS@ bys001>select distinct sid from v$mystat;
SID
----------
147
BYS@ bys001>DELETE TEST ;
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 3 0 0
19 TX 655390 28214 6 0 1
147 TM 77389 0 3 0 0
147 TX 655390 28214 0 6 0
BYS@ bys001>lock table test in share mode;
Table(s) Locked.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 0
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 4 0 1
147 TM 77389 0 0 3 0
共享锁，其它事务可以查询锁定的表但不能修改，只允许当前事务修改，但可以多个事务持有它。
##########################################################
BYS@ bys001>lock table test in share row exclusive mode;
Table(s) Locked.
Elapsed: 00:00:00.01
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
Elapsed: 00:00:00.01
BYS@ bys001>select * from test;
no rows selected
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
BYS@ bys001>insert into test values(88);
1 row created.
BYS@ bys001>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX') order by 1,2;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
19 TM 77389 0 5 0 0
19 TX 524320 28676 6 0 0
在147会话执行DML操作会hang住
BYS@ bys001>insert into test values(88);
此时查询
BYS@ bys001>select sid,type,id1,id2,lmode,request,bl上一个：导致死锁的SQL示例
下一个：数据字典简介
