Oracle DML和DDL锁的解决方法

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

一、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