Oracle LOCK内部机制及最佳实践系列(一)分别模拟insert|update|delete造成阻塞及说明-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle LOCK内部机制及最佳实践系列(一)分别模拟insert|update|delete造成阻塞及说明

简介:
引言:Oracle 锁在我一开始接触的时候会有一种高深莫测的感觉,就像是遥远的外星人看不见摸不着但是能感觉到,我在实际的工作中就遇到过ORA-00054: resource busy acquire with nowait specified错误不能插入表,当时知道是被锁定了,根据V$LOCK也定位出阻塞的会话了,但不知道如何长久的解决它,究其原因就是不清楚内部机制与释放原理,下面根据例子来揭开锁的面纱,走进Oracle锁的世界。

分别模拟insert,update和delete造成阻塞的示例,并对v$lock中的相应的信息进行说明,给出SQL演示。

LEO1@LEO1> create user leo2 identified by leo2 default tablespace leo1;       新创建一个LEO2用户
User created.
LEO1@LEO1> grant connect,resource to leo2;                                             授予基本权限
Grant succeeded.
LEO1@LEO1> select owner,table_name,tablespace_name from dba_tables where owner='LEO1';
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
LEO1                               LEO_WAGE                       LEO1

LEO1@LEO1> create table t1 (id int primary key);     创建t1表,设置id列为主键
Table created.
LEO1@LEO1> insert into t1 values(1);                  
1 row created.
LEO1@LEO1> select * from t1;
        ID
----------
         1
LEO2@LEO1> insert into leo1.t1 values(1);             当没有提交,在插入同样的values时就发生了对会话的阻塞,hang在这里不能前进
LEO1@LEO1> commit;                                          必须提交后,阻塞才终止,也就是说commit可以释放阻塞
Commit complete.
LEO2@LEO1> insert into leo1.t1 values(1);             因为已经有了1值,故违反了主键约束
insert into leo1.t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (LEO1.SYS_C0010817) violated

update 锁阻塞

LEO1@LEO1> select * from t1;
        ID
----------
         1
         2
       100
LEO1@LEO1> update t1 set id=200 where id=100;       更新一行没有提交,没有提交的事物对别人是不可见的,但在物理块上真真切切的修改了,他人只能访问undo回滚段中镜像
1 row updated.
LEO2@LEO1> update leo1.t1 set id=300 where id=100;   我们在会话leo2上也更新同一个表里的同一行,此时hang住了不动了,因为2个会话在争用同一条记录的修改权
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;        一般影响业务性能的就 TM and TX 锁
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73449          0          3          0          0
       138 TX     196615      912          6          0          1
       156 TM      73449          0          3          0          0
       156 TX     196615      912          0          6          0

SID:会话id号
TYPE:锁的类型
ID1:会话操作对象的id号
ID2:ID1+ID2 定位回滚段上的一个地址(即修改之前数据镜像地址),由于138和156会话是一样的说明指向的是同一个地址,换句话说操作的是同一行数据
LMODE:锁模式,不同的数字代表不同的锁模式  例如  0 现在没有申请到锁   3  共享锁模式(段级共享锁)   6  排他锁模式   锁的级别越高限制越多
REQUEST:目前会话没有锁,正在申请的锁模式  例如  0 没有正在申请的锁,说明已经有锁了   6  现在正在申请6号锁,目前因为没有才申请
BLOCK:当前正在阻塞几个会话  例如  1  当前正在阻塞一个会话  2  当前正在阻塞两个会话
锁的实质:是维护一个事务完整性的,锁的信息是数据块的一个属性,是物理的,并不是逻辑上属于某个表或者某几行的。

LEO1@LEO1> select distinct sid from v$mystat;        这个会话当前id是138,我们怎么区分呢?=> LEO1用户=138     LEO2用户=156
       SID
----------
       138
LEO1@LEO1> select object_name from dba_objects where object_id=73449;                     138会话操作的对象是T1表
OBJECT_NAME
--------------------------------------------------------------------------------
T1
说明:138会话在T表上加了TM和TX锁,TM锁模式为3(共享锁) TX锁模式6(排他锁),目前TX锁正在阻塞一个会话(就是156会话)。
156会话就是当前被阻塞的会话,156会话操作对象也是T1表(ID1都一样的),TM锁模式也为3(共享锁就是有几个会话就可以创建几个共享锁,同时存在),TX现在还没有申请到锁,正在申请6号锁,而这个6号锁就是138会话所持有的(因为2个会话操作的是同一行数据)
LEO1@LEO1> select sid,event from v$session_wait where sid in (138,156);         从会话等待视图上可以看出,有哪些会话由于什么原因导致等待事件不能前进
       SID EVENT
---------- ----------------------------------------------------------------
       138 SQL*Net message to client
       156 enq: TX - row lock contention
156会话由于TX锁争用原因导致hang住不能前进,enq=enqueues队列锁(通常和业务有关,为了保护业务的锁)
小结:现在我们应该很晴朗的看出138会话阻塞156会话,以及阻塞的原因和会话数和锁类型

insert 锁阻塞

LEO1@LEO1> select * from leo1.t1;
        ID
----------
         1
         2
       200
LEO1@LEO1> insert into leo1.t1 values(3);       插入一行但没有提交,这是一个未决状态,还不清楚是否真正插入
1 row created.
LEO2@LEO1> insert into leo1.t1 values(3);       我们在会话leo2上也插入同样的数据,此时hang住了不动了,这里实际上是插入了2条独立的记录,不能认为是同一条记录,只是值一样
LEO1@LEO1> 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
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73449          0          3          0          0
       138 TX      65555        681          6          0          1        138正在阻塞另一个会话
       156 TM      73449          0          3          0          0
       156 TX     458766       669          6          0          0        这也显示了在插入第二条记录时没有被阻塞
       156 TX      65555        681          0          4          0        而在插入的修改值相同后被阻塞了,锁的级别是4
insert时v$lock视图里面多了一个TX锁(就是最后一行),首先说明一下insert和update delete 操作的不同,后两者都是对同一条记录的修改权争用产生阻塞(这里不涉及修改值的问题),而insert操作实际上插入了2条不同的记录,由于这2条不同的记录的修改值一样违反了主键约束从而产生阻塞,实际是对修改值的相同产生了阻塞。锁的级别为4,这种锁比update的锁级别要低,锁的级别越低限制越少。

delete 锁阻塞

LEO1@LEO1> select * from leo1.t1;                  t1表中有5条记录,我们计划删除的是最后1条
        ID
----------
         1
         2
         4
         5
       200
LEO1@LEO1> delete from leo1.t1 where id=200;       138会话正在删除id=200的记录,但是没有提交,此时就是加上一个TM TX锁
1 row deleted.
LEO2@LEO1> delete from leo1.t1 where id=200;       这时158会话也做同样的动作,就被hang住了不能动了,下面我们来看看锁定情况
LEO1@LEO1> 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
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73449          0          3          0          0
       138 TX     524316      935          6          0          1               138会话持有一个6级排他锁,正在阻塞一个会话
       156 TM      73449          0          3          0          0
       156 TX     524316      935          0          6          0               156会话被阻塞住了没有获得锁,正在申请一个6级锁
LEO1@LEO1> select object_name from dba_objects where object_id=73449;              现在可知锁定的就是t1表
OBJECT_NAME
--------------------------------------------------------------------------------
T1
LEO1@LEO1> select sid,event from v$session_wait where sid in (138,156);            从会话等待视图上也可以看出138阻塞了156会话
       SID EVENT
---------- ----------------------------------------------------------------
       138 SQL*Net message from client
       156 enq: TX - row lock contention
156会话由于TX锁争用原因导致hang住不能前进,enq=enqueues队列锁
小结:我们在了解锁的同时,也要在业务设计的流程上尽量去避免它们的发生,比如说2个人的工作没有协调好,在同一时间去做了同一件事,这就有可能产生锁。

select...for update 锁阻塞                                     这是一种对结果集修改的保护机制

场景:一次性修改多条记录的时候会用到这个命令,起到锁定结果集的效果,这也是结果集修改引起的阻塞
LEO1@LEO1> select * from leo1.t1;       
        ID
----------
         1
         2
         3
       200
LEO1@LEO1> select * from leo1.t1 where id<=3 for update;        如果我们想对查询出的结果集进行独占,并且此时不允许其他会话进行修改,可以这么来写
        ID
----------
         1
         2
         3
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
       138 TM              73449          0          3          0          0
       138 TX             589839      915          6          0          0           这3行记录都已经被TX锁锁定了,在没有提交之前别人不能修改
LEO2@LEO1> update leo1.t1 set id=4 where id=1;
LEO2@LEO1> update leo1.t1 set id=4 where id=2;
LEO2@LEO1> update leo1.t1 set id=4 where id=3;                   我们在会话leo2上测试更新结果集中的每条记录,都会hang住了不能前进,说明这
个结果集已经整体被锁定
LEO1@LEO1> 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
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73449          0          3          0          0
       138 TX     589839      915          6          0          1      138正在阻塞另一个会话
       156 TM      73449          0          3          0          0
       156 TX     589839      915          0          6          0

LEO1=138会话   LEO2=156会话,我们可以看出138会话阻塞156会话,156会话TX在请求一个6号排他锁,因为2个会话都在修改同一个结果集。这种方法可以一次性锁定n行记录。

重点:一个表上只能有一个6号锁




 本文转自 leonarding151CTO博客,原文链接:http://blog.51cto.com/leonarding/1073545,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章