从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 loc