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

╰半橙微兮° 2022-02-03 08:11 168阅读 0赞

引言： [Oracle][]  锁在我一开始接触的时候会有一种高深莫测的感觉，就像是遥远的外星人看不见摸不着但是能感觉到，我在实际的工作中就遇到过ORA-00054: resource busy acquire with nowait specified错误不能插入表，当时知道是被锁定了，根据V$LOCK也定位出阻塞的会话了，但不知道如何长久的解决它，究其原因就是不清楚内部机制与释放原理，下面根据例子来揭开锁的面纱，走进Oracle锁的世界。  
  
**分别模拟insert，update和delete造成阻塞的示例，并对v$lock中的相应的信息进行说明，给出SQL演示。**  
  
[LEO1@LEO1][LEO1_LEO1] > create user leo2 identified by leo2 default tablespace leo1; 新创建一个LEO2用户  
User created.  
[LEO1@LEO1][LEO1_LEO1] > grant connect,resource to leo2; 授予基本权限  
Grant succeeded.  
[LEO1@LEO1][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][LEO1_LEO1] > create table t1 (id int primary key); 创建t1表，设置id列为主键  
Table created.  
[LEO1@LEO1][LEO1_LEO1] > insert into t1 values(1);   
1 row created.  
[LEO1@LEO1][LEO1_LEO1] > select \* from t1;  
 ID  
\----------  
 1  
[LEO2@LEO1][LEO2_LEO1] > insert into leo1.t1 values(1); 当没有提交，在插入同样的values时就发生了对会话的阻塞，hang在这里不能前进  
[LEO1@LEO1][LEO1_LEO1] > commit; 必须提交后，阻塞才终止，也就是说commit可以释放阻塞  
Commit complete.  
[LEO2@LEO1][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][LEO1_LEO1] > select \* from t1;  
 ID  
\----------  
 1  
 2  
 100  
[LEO1@LEO1][LEO1_LEO1] > update t1 set id=200 where id=100; 更新一行没有提交，没有提交的事物对别人是不可见的，但在物理块上真真切切的修改了，他人只能访问undo回滚段中镜像  
1 row updated.  
[LEO2@LEO1][LEO2_LEO1] > update leo1.t1 set id=300 where id=100; 我们在会话leo2上也更新同一个表里的同一行，此时hang住了不动了，因为2个会话在争用同一条记录的修改权  
[LEO1@LEO1][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][LEO1_LEO1] > select distinct sid from v$mystat; 这个会话当前id是138，我们怎么区分呢？=> LEO1用户=138 LEO2用户=156  
 SID  
\----------  
 138  
[LEO1@LEO1][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][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][LEO1_LEO1] > select \* from leo1.t1;  
 ID  
\----------  
 1  
 2  
 200  
[LEO1@LEO1][LEO1_LEO1] > insert into leo1.t1 values(3); 插入一行但没有提交，这是一个未决状态，还不清楚是否真正插入  
1 row created.  
[LEO2@LEO1][LEO2_LEO1] > insert into leo1.t1 values(3); 我们在会话leo2上也插入同样的数据，此时hang住了不动了，这里实际上是插入了2条独立的记录，不能认为是同一条记录，只是值一样  
[LEO1@LEO1][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][LEO1_LEO1] > select \* from leo1.t1; t1表中有5条记录，我们计划删除的是最后1条  
 ID  
\----------  
 1  
 2  
 4  
 5  
 200  
[LEO1@LEO1][LEO1_LEO1] > delete from leo1.t1 where id=200; 138会话正在删除id=200的记录，但是没有提交，此时就是加上一个TM TX锁  
1 row deleted.  
[LEO2@LEO1][LEO2_LEO1] > delete from leo1.t1 where id=200; 这时158会话也做同样的动作，就被hang住了不能动了，下面我们来看看锁定情况  
[LEO1@LEO1][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][LEO1_LEO1] > select object\_name from dba\_objects where object\_id=73449; 现在可知锁定的就是t1表  
OBJECT\_NAME  
\--------------------------------------------------------------------------------  
T1  
[LEO1@LEO1][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][LEO1_LEO1] > select \* from leo1.t1;   
 ID  
\----------  
 1  
 2  
 3  
 200  
[LEO1@LEO1][LEO1_LEO1] > select \* from leo1.t1 where id<=3 for update; 如果我们想对查询出的结果集进行独占，并且此时不允许其他会话进行修改，可以这么来写  
 ID  
\----------  
 1  
 2  
 3  
[LEO1@LEO1][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][LEO2_LEO1] > update leo1.t1 set id=4 where id=1;  
[LEO2@LEO1][LEO2_LEO1] > update leo1.t1 set id=4 where id=2;  
[LEO2@LEO1][LEO2_LEO1] > update leo1.t1 set id=4 where id=3; 我们在会话leo2上测试更新结果集中的每条记录，都会hang住了不能前进，说明这  
个结果集已经整体被锁定  
[LEO1@LEO1][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，如需转载请自行联系原作者

[Oracle]: http://www.itpub.net/pubtree/?node=1
[LEO1_LEO1]: mailto:LEO1@LEO1
[LEO2_LEO1]: mailto:LEO2@LEO1