从mode 2-6的TM锁相互间的互斥示例(二)
k 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,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 1
19 TX 524320 28676 6 0 0
147 TM 77389 0 0 3 0
小结：共享行级排他锁，同一时间只允许一个事务持有和修改锁定的表，其他事务可以查询但不能修改。
#############################################
BYS@ bys001>lock table test in exclusive mode;
Table(s) Locked.
Elapsed: 00:00:00.00
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