Oracle DML和DDL锁的解决方法

超、凢脫俗 2022-08-20 11:21 305阅读 0赞

普通的开发人员经常会遇到删除，修改数据遇到锁，还有修改或删除存储过程产生锁。请看下面的例子：

**一、DML锁解决办法**

假设锁在会话1上  
session1:  
drop table test;  
create table test as select \* from dba\_objects;  
select \* from test for update;  
  
系统现在无法完成DML操作，这个时候要人工造成一个锁等待冲突的现象  
session2:  
select sid from v$mystat where rownum=1; --204  先查出被堵塞会话id  
select \* from test for update;  
  
排除，解决  
session3:  
\--找到被堵塞会话中，可以看到行锁的等待事件enq: TX - row lock contention，还可以看到制造堵塞的源头  
select w.EVENT,w.BLOCKING\_INSTANCE,w.BLOCKING\_SESSION from v$session w where w.sid=204;  
EVENT                          BLOCKING\_INSTANCE BLOCKING\_SESSION  
\------------------------------ ----------------- ----------------  
enq: TX - row lock contention                  1              134  
  
查找到源头的会话信息  
select s.INST\_ID,s.SID,s.SERIAL\# from gv$session s where s.INST\_ID =1 and s.SID =134;  
   INST\_ID        SID    SERIAL\#  
\---------- ---------- ----------  
         1        134       3559  
  
现在实例1上杀会话：  
alter system kill session '134,3559';  
  
有的时候不一定能杀掉，则需要在操作系统层面上杀：  
select spid  
  from gv$process p, gv$session s  
 where p.INST\_ID = s.INST\_ID  
   and p.INST\_ID = 1  
   and s.SID = 134  
   and s.SERIAL\# = 3559  
   and p.ADDR = s.PADDR;  
SPID  
\-----  
27004  
     
登陆实例1执行

kill -9  27004

**二、DDL锁解决办法**  
假设锁在会话1上  
session1:  
create or replace procedure p\_test is  
begin  
  dbms\_lock.sleep(1000);  
end;  
  
  
call p\_test();  
  
  
系统现在无法完成DML操作，这个时候要人工造成一个锁等待冲突的现象  
session2:  
select sid from v$mystat where rownum=1; --134  先查出被堵塞会话id  
drop procedure p\_test;  
  
  
排除，解决  
session3:  
\--找到被堵塞会话中，可以看到行锁的等待事件enq: TX - row lock contention，还可以看到制造堵塞的源头  
select w.EVENT,w.BLOCKING\_INSTANCE,w.BLOCKING\_SESSION from v$session w where w.sid=134;  
EVENT                                                            BLOCKING\_INSTANCE BLOCKING\_SESSION  
\---------------------------------------------------------------- ----------------- ----------------  
library cache pin                                                    1              202  
  
  
查找到源头的会话信息  
select s.INST\_ID,s.SID,s.SERIAL\# from gv$session s where s.INST\_ID =1 and s.SID =202;  
   INST\_ID        SID    SERIAL\#  
\---------- ---------- ----------  
         1        202       105  
  
  
现在实例1上杀会话：  
alter system kill session '202,105';  
  
  
有的时候不一定能杀掉，则需要在操作系统层面上杀：  
select spid  
  from gv$process p, gv$session s  
 where p.INST\_ID = s.INST\_ID  
   and p.INST\_ID = 1  
   and s.SID = 202  
   and s.SERIAL\# = 105  
   and p.ADDR = s.PADDR;  
SPID  
\-----  
27008  
     
登陆实例1执行  
kill -9 27008