首页 > 代码库 > 从浅到深掌握Oracle的锁

从浅到深掌握Oracle的锁

 
1.分别模拟insert,update和delete造成阻塞的示例,并对v$lock中的相应的信息进行说明,给 出SQL演示。
Insert示例
会话1
SQL> select * from t1;
        ID
         1
         2
         3
         4

SQL> alter table t1 add primary key(id);
表已更改。

SQL> select * from v$lock where type in (‘TX‘,‘TM‘);
未选定行

SQL> insert into t1 values(5);
已创建 1 行。
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          3          0          0
       141 TX     131084       1787          6          0          0

会话二:
SQL> insert into t1 values(5);
__
(阻塞)

会话一:
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TX     131084       1787          0          4          0
(此处出现申请4号锁,insert本身没有阻塞,而是在等待判断主键冲突否)
        19 TM      82975          0          3          0          0
       141 TM      82975          0          3          0          0
        19 TX     327697       1662          6          0          0
       141 TX     131084       1787          6          0          1
 
Update示例
会话一:
SQL> update t1 set id=5 where id=4;
已更新 1 行。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          3          0          0
       141 TX     327706       1663          6          0          0

SQL> select sid from v$mystat where rownum=1;
       SID
       141
SQL> select sid,event from v$session_wait  where sid in(141,19);
       SID EVENT
---------- ----------------------------------------------------------------
        19 SQL*Net message from client
       141 SQL*Net message from client

会话二:
SQL>  select sid from v$mystat where rownum=1;
       SID
        19
SQL> update t1 set id=5 where id=4;
__(阻塞)

会话一:
SQL> select sid,event from v$session_wait  where sid in(141,19);
       SID EVENT
---------- ----------------------------------------------------------------
        19 enq: TX - row lock contention(等待事件出现tx锁,原因是行锁争用)
       141 SQL*Net message from client

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TX     327706       1663          0          6          0
(可以看到会话二在申请6号锁,而141的TX锁block为1,表示阻塞了别的会话)
        19 TM      82975          0          3          0          0
       141 TM      82975          0          3          0          0
       141 TX     327706       1663          6          0          1
 
Delete示例
会话一:
SQL> delete t1 where id=4;
已删除 1 行。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          3          0          0
       141 TX     327706       1663          6          0          0

SQL> select sid from v$mystat where rownum=1;
       SID
       141
SQL> select sid,event from v$session_wait  where sid in(141,19);
       SID EVENT
---------- ----------------------------------------------------------------
        19 SQL*Net message from client
       141 SQL*Net message from client

会话二:
SQL>  select sid from v$mystat where rownum=1;
       SID
        19
SQL> delete t1 where id=4;
__(阻塞)

会话一:
SQL> select sid,event from v$session_wait  where sid in(141,19);
       SID EVENT
---------- ----------------------------------------------------------------
        19 enq: TX - row lock contention(等待事件出现tx锁,原因是行锁争用)
       141 SQL*Net message from client

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TX     327706       1663          0          6          0
(可以看到会话二在申请6号锁,而141的TX锁block为1,表示阻塞了别的会话)
        19 TM      82975          0          3          0          0
       141 TM      82975          0          3          0          0
       141 TX     327706       1663          6          0          1
 
v$lock中的相应的信息
ADDR
KADDR
SID:锁所在的会话id
TYPE:锁的类型,类型有很多种,常用的有TX(事务锁)、TM锁(表级锁)
ID1:TM锁为对象的object_id,TX锁为v$transaction 中 XIDUSN * 2^16 +  XIDSLOT
ID2:TM锁为0,TX锁为v$transaction 中  XIDSQN
LMODE:锁的模式,有2~6,越大锁的限制越高,
REQUEST:为0表示没有申请锁,其他2~6表示在申请该模式的锁
CTIME:表示锁从出现到现在的时间
BLOCK:为1表示阻塞了其他会话申请锁,为0表示没有阻塞别人。
 
2.模拟RI锁定导致阻塞的场景,并分析v$lock相应的锁定信息,给出SQL演示。
 
 
3.自己构想一个使用手工锁定解决一种业务需求的场景,并给出SQL演示。
例如业务中只允许同一时间只有一个用户可以更改表,就需要手动锁表,来组织其他用户dml操作。
会话一:
SQL> lock table t1 in share mode;
表已锁定。

会话二:
SQL>  update t1 set id=11 where id=1;
__(等待)

直到会话一commit或roll,会话二执行。
 
4.给出从mode 2-6 的TM锁相互间的互斥示例。
TM分几种情况,TX目前见过4和6(insert有主键会申请4)
Oracle TM锁的类型:

锁模式

锁描述

含义

锁定表的SQL

0

None

  

1

Null

空,本模式是oracle预留模式

 

2

Row Share(RS)

又叫(SS

行级共享锁,是限制最少的TM锁,可以提供最高程度的并发性。其他会话可以对锁定的表进行任何类型的DML操作,还可以与其他会话锁并存

Lock table t1 in row share mode;

3

Row Exclusive Table Lock(RX)

又叫(SX)

行级排他锁,通常已经有事务在修改行或者selectfor update 修改结果集。允许其他事务对锁定的表进行select  insert  update  delete lock table 同时锁定一张表

Lock table t1 in row exclusive mode;

4

Share Table Lock(S)

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

Lock table t1 in share mode;

5

Share Row Exclusive Table Lock(SRX)

又叫SSX

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

Lock table t in share row exclusive mode;

6

Exclusive Table Lock (X)

排他锁,是限制最高的TM锁,禁止其他事务执行任何类型的DML语句或者锁表

一个表一般只能有一个6号锁

Lock table t1 in exclusive mode;

 
Oracle锁模式互斥关系图(TM与TM之间的):

锁模式

锁名称

允许级别

互斥级别

2

行级共享锁

2 3 4 5 6

6

3

行级排他锁

2 3

4 5 6

4

共享锁

2 4

3 5 6

5

共享行级排他锁

2

3 4 5 6

6

排他锁

 

2 3 4 5 6

 
TM锁产生的情况:
 
坛子上关于TM精华帖截取:
0、无
1NULL,可以某些情况下,如分布式数据库的查询会产生此锁。
2、SS,表结构共享锁
3、SX,表结构共享锁+被操作的记录的排它锁
4、S, 表结构共享锁+所有记录共享锁
5、SRX 表结构共享锁+所有记录排它锁
6、X   表结构排它锁+所有记录排它锁

2  SS, 表结构共享+加上部分数据共享.虽然有人说,ORACLE里,数据没有S状态,但我还是愿意这    样理解:第1个S代表表结构共享,第2个代表表里的数据共享. 你可以想象一下,当往子表里增    加纪录时,主表的相关主键是不是得处于共享模式.
3    SX,   用于DML操作,第1个S代表表结构共享,第2个代表表里被操作的数据独占.
4    S, 代表表结构+表里的数据都是处于共享模式.当对表创建索引时,在创建期间,表处于这种模式.
5    SRX=S(4)+SX(3), 
6    X, 删除表是会用上.
 
LMODE=2开始的情况
141> lock table t1 in row share mode;        //给t1上2号锁
表已锁定。

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          2          0          0

19> select * from t1 for update;        //另开会话给t1上36号锁
        ID
----------
         1
         2
         3
         4

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TM      82975          0          3          0          0
       141 TM      82975          0          2          0          0
        19 TX     393221       1754          6          0          0
可以看到236可以共存,没有互斥

19> lock table t1 in share mode;        //给表再上4号锁
表已锁定。

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TM      82975          0          5          0          0
       141 TM      82975          0          2          0          0
        19 TX     393221       1754          6          0          0
因为34不能共存,因此3+4升级为5号锁

19> lock table t1 in exclusive mode;        //给表上6号锁,发生等待
__

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TM      82975          0          5          6          1
       141 TM      82975          0          2          0          1
        19 TX     393221       1754          6          0          0
因为同是19号会话,上6号TM只需将原有5升级,但是已经存在2号TM,产生互斥,所以等待
 
证明6号TX和6号TM可以共存:
SQL> Lock table t1 in exclusive mode;
表已锁定。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          6          0          0

SQL> update t1 set id=5 where id=4;
已更新 1 行。

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          6          0          0
       141 TX     262155       1324          6          0          0
 
证明2号TM和6号TX可以共存:
141> Lock table t1 in row share mode;
表已锁定。

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘) order by 1;
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       141 TM      82975          0          2          0          0

19> update t1 set id=6 where id=4;
已更新 1 行。

141> select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TX‘,‘TM‘);
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        19 TM      82975          0          3          0          0
       141 TM      82975          0          2          0          0
        19 TX     458778       1387          6          0          0
 
5.给出一个导致死锁的SQL示例。
死锁就是a在等待b释放锁,而b又在等待a释放,形成交叉。
会话一:
SQL> update t1 set id=11 where id=1;
已更新 1 行。

会话二:
SQL> update t2 set id=11 where id=1;
已更新 1 行。

会话一:
SQL> update t2 set id=11 where id=1;
__(等待)
会话二:
SQL>  update t1 set id=11 where id=1;
__(等待)

会话一:
SQL> update t2 set id=11 where id=1;
update t2 set id=11 where id=1
       *
1 行出现错误:
ORA-00060: 等待资源时检测到死锁
 
(以下部分引用自http://blog.csdn.net/changyanmanman/article/details/7451043)
    在讲到lock的上文中已经提到,这种控制机制需要resource,lock联众数据结构,但是需要内存分配lock数据结构,对于粗粒度(可以理解为文件很大)或者数量有限的资源,使用这种机制还可以接受,因为分配的内存并不多。但是对于表的数据记录,动辄几百G 的表,每个记录如果都分配一个resource 和lock 数据结构对,无论从内存需求还是维护开销上都是一个噩梦。所以,对于数据记录这种细粒度的资源,oracle使用的是行级锁(row level  lock)。记数据块内存储的是一条条的用户记录,用户记录也是按照一定的格式保存的,每条记录可以分成 记录头 和 记录体 两部分。记录头中是描述信息,比如列宽度,和事务有关的是ITL Entry pointer字段。
1)ITL:这个比较熟悉,用于记录哪些事务修改了这个数据块的内容,可以把他想象成一个表格,每个表格对应一个事务,包括事务号,事务是否提交等重要信息。
2)记录头ITL索引:每条记录的记录头部有一个字段,用于记录ITL表项号,可以看做是指向ITL表的指针,如果一个进程来访问记录,那就先得根据这个指针去ITL中看看事务已经提交了没啊。。前一个事务是用的什么锁啊。。等待
下面我们详细说一下运行机制:
    当一个事务开始时,必须先申请一个TX锁,注意 这种锁保护的资源是回滚段,回滚数据块。因此这个申请也就意味着:用户必须先申请到一个回滚段资源后才能开始一个事务,才能执行语句修改数据。申请到回滚段资源后,用户事务就可以修改数据了。在修改数据表的记录时,需要遵循下面的操作顺序:
1、首先获得这个表的TM锁,这个锁用于保护事务执行过程中其他用户不能修改表结构(但是可以修改表内的数据)。
2、事务修改某个数据块中的记录时,首先要在数据块块头的ITL表中申请一个空闲表项,并且在其中记录事务号,实际就是记录这个事务要使用的回滚段地址。
3、事务修改该数块中的某条记录时,会设置该记录头部的ITL索引指向上一步申请到的表项。然后再修改记录的内容,修改前现在回滚段对记录修改该钱的状态做一个拷贝,然后才能修改该数据记录,这个拷贝用于以后的回滚,恢复,或者一致性读。
4、当其他用户并发修改该这条记录时,会根据记录头的ITL索引读取ITL表项内容。查看这个事务石头已经提交。
5、如果没有提交,则这个用户的TX锁会等待前一个用户的TX锁的释放。仅仅释放前一个用户的行锁是不行的,Oracle只检测TX,因为定期检测行锁资源消耗太大。
从上面的工作机制可以看出,无论一个事务修改多少个表的多少条记录,盖世五真正需要的只是一个TX锁,每个表一个的TM锁,内存开销非常小。而所谓的行级锁,其实只是数据块头,数据记录的一些字段,不会消耗额外的内存资源。
 
6.查找锁的SID和被锁的SQL
如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等
以下的语句可以查询到谁锁了表,而谁在等待。
结果第一行表示产生锁的,子节点表示被锁的
 
SQL> SELECT /*+ rule */ lpad(‘ ‘,decode(l.xidusn ,0,3,0))||l.oracle_username
  2  User_name,s.sid,s.sql_id,
  3  o.owner,o.object_name,o.object_type,s.sid,s.serial#
  4  FROM v$locked_object l,dba_objects o,v$session s
  5  WHERE l.object_id=o.object_id
  6  AND l.session_id=s.sid
  7  ORDER BY o.object_id,xidusn DESC;
 
USER_NAME   SID SQL_ID        OWNER      OBJECT_NAM OBJECT_TYP  SID    SERIAL#
---------- ---- ------------- ---------- ---------- ---------- ---- ----------
SYS          16 a18zt89tq6c0s SCOTT      EMP        TABLE        16        751
   BIDPRO   202 3f06gzyfyg1xj SCOTT      EMP        TABLE       202        793
 

从浅到深掌握Oracle的锁