TM表锁各种mode的实验及2-6 的TM锁相互间的互斥示例

TM 表锁，发生在insert,update,delete以及select for update操作时，目的是保证操作能够正常进行，并且阻止其它人对表执行DDL操作。

TM锁几种模式的互斥关系图:

在巡检期间，检查如果发现有相关的session_wait锁，可按以下步骤查找相应的锁

1. 使用V$LOCK找出session持有的锁。

2. 使用V$SESSION找出持有锁或等待锁的session执行的sql语句。

3. 使用V$SESSION_WAIT找出什么原因导致session持有锁堵塞。

4. 使用V$SESSION获取关于持有锁的程序和用户的更多信息。

V$LOCK中的常用列

 SID：表示持有锁的会话信息。

TYPE：表示锁的类型。值包括TM和TX等。

 LMODE：表示会话等待的锁模式的信息。用数字0－6表示，和表1相对应。

REQUEST：表示session请求的锁模式的信息。

 ID1,ID2：表示锁的对象标识。

例如：首先查出当前TM','TX等待事件的等待会话

BYS@ bys3>select a.sid,a.kaddr,a.addr,a.type,a.id1,a.id2,a.lmode,a.request,a.block from v$lock a where a.type in('TM','TX') order by 1,2;
       SID KADDR    ADDR     TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -------- -------- -- ---------- ---------- ---------- ---------- ----------
         1 B693D590 B693D560 TM      22326          0          0          3          0
        44 B693D590 B693D560 TM      22326          0          5          0      
BYS@ bys3>select sql_id from v$session where sid=1;   通过上一步会话SID，查出1号会话当前在等待的语句
SQL_ID
-------------
01mv5v746cfbq
BYS@ bys3>select * from v$sqltext where sql_id='01mv5v746cfbq';   --通过上一步查出SQL_SID，查出SQL_TEXT
ADDRESS  HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
-------- ---------- ------------- ------------ ---------- ------------------------------
25DDD5FC 3362142582 01mv5v746cfbq            6          0 update test set status=8899

用一条语句来查询是：select a.*,b.sid,b.username from v$sqltext a,v$session b,v$lock c where  a.sql_id=b.sql_id and b.sid=c.sid and c.type in('TM','TX');

详见：v$session/v$process视图涉及的相关会话信息的查询

1.环境准备：--有些实验数据是后来补充的，可能不太一致。
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

TM锁1号锁实验：

 create table order_info(customerid number);

CREATE OR REPLACE PROCEDURE kzcz214001 (cust_id in NUMBER)
   AS
   BEGIN
      insert into order_info values(cust_id);
      commit;
   END;
/
 col owner for a10
 col name for a10
 col type for a10
select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from  dba_ddl_locks WHERE NAME='KZCZ214001';
这一句查询dba_ddl_locks表，如果手工建库，dba_ddl_locks表可能不存在，需要SYS用户运行脚本去创建：?/rdbms/admin/catblock.sql
exec kzcz214001(1);
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from  dba_ddl_locks WHERE NAME='KZCZ214001';
       SID OWNER      NAME       TYPE       HELD      REQUEST
---------- ---------- ---------- ---------- --------- ---------
         1 BYS        KZCZ214001 Table/Proc Null      None
                                 edure/Type
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
VALID

BYS@ bys3>alter table order_info add(name varchar2(10));
Table altered.
BYS@ bys3>select status from dba_objects where object_name='KZCZ214001';
STATUS
-------
INVALID
索引失效后，1号锁还能查到。。。
BYS@ bys3>select SESSION_ID sid,OWNER,NAME,Type,MODE_HELD held,MODE_REQUESTED request from  dba_ddl_locks WHERE NAME='KZCZ214001';
       SID OWNER      NAME       TYPE       HELD      REQUEST
---------- ---------- ---------- ---------- --------- ---------
         1 BYS        KZCZ214001 Table/Proc Null      None
                                 edure/Type

####################################

等级共享锁 2 row share----rs

产生2号TM锁方法1：--手动锁定

把在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

如果使用DML语句，会报错：ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

产生2号TM锁方法2：--大表在线建索引

在会话1：--在大表上在线创建索引，要用ONLINE，不用ONLINE不会有2号锁。
BYS@ bys3>create index idx_test1 on test1(id) online;
Index created.
会话2：--有4号锁和2号锁
BYS@ bys3>select * from v$lock where sid=1;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2AD66694 2AD666C0          1 AE        100          0          4          0       2154          0
2AD6697C 2AD669A8          1 DL      22325          0          3          0          4          0
2AD66AF0 2AD66B1C          1 OD      22325          0          4          0          4          0
2AD66C64 2AD66C90          1 DL      22325          0          3          0          4          0
B6A0DB98 B6A0DBC8          1 TM      22325          0          2          0          4          0
B6A0DB98 B6A0DBC8          1 TM      22354          0          4          0          4          0
2926D618 2926D690          1 TS          4   16777362          6          0          2          0
28471AE4 28471B24          1 TX     327696       2557          6          0 
BYS@ bys3>col object_name for a20
BYS@ bys3>select a.sid,a.type,a.lmode,a.request,b.object_name from v$lock a,dba_objects b where a.id1=b.object_id and sid=1;
       SID TY      LMODE    REQUEST OBJECT_NAME
---------- -- ---------- ---------- --------------------
         1 TM          2          0 TEST1
         1 TM          4          0 SYS_JOURNAL_22360
         1 AE          4          0 ORA$BASE
         1 OD          4          0 TEST1
         1 DL          3          0 TEST1
         1 DL          3          0 TEST1

小结：行级共享锁，是限制最少的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，for update 都可以产生一个3级锁，也允许其他事务来修改锁定的表
BYS@ bys001>select * from test;
A
----------
22
BYS@ bys001>update test set a=99 where a=22;
1 row updated.
BYS@ bys001>select * from test;
A
----------
99
BYS@ bys001>select distinct sid from v$mystat;
       SID
----------
       147
BYS@ bys001>insert into test values(55);
1 row created.
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,没有阻塞。

此时，如执行DDL语句，报错：ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired

小结：行级排他锁，通常已经有事务在修改行或者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

共享锁，其它事务可以查询锁定的表但不能修改，只允许当前事务修改，但可以多个事务持有它。

另一个产生4号锁的，见：  --2号锁在线建索引实验。

##########################################################

TM锁5号锁实验：

BYS@ bys001>lock table test in share 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          5          0          0
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

小结：共享行级排他锁，同一时间只允许一个事务持有和修改锁定的表，其他事务可以查询但不能修改。

5号锁--在存储过程运行中会出现：A B两个会话同时执行同一个存储过程时

session A   exec p
session B    exec p
#############################################

TM锁6号锁实验：

BYS@ bys001>lock table test in 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          6          0          0
在会话147进行hang住
BYS@ bys001>lock table test in row share mode;   ---在此HANG住

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          6          0          1
       147 TM      77389          0          0          2          0