\u4e00\u3001DML\u9501\u89e3\u51b3\u529e\u6cd5
\u5047\u8bbe\u9501\u5728\u4f1a\u8bdd1\u4e0a
session1:
?drop table test;
?create table test as select * from dba_objects;
?select * from test for update;<\/p>
\u7cfb\u7edf\u73b0\u5728\u65e0\u6cd5\u5b8c\u6210DML\u64cd\u4f5c\uff0c\u8fd9\u4e2a\u65f6\u5019\u8981\u4eba\u5de5\u9020\u6210\u4e00\u4e2a\u9501\u7b49\u5f85\u51b2\u7a81\u7684\u73b0\u8c61
session2:
?select sid from v$mystat where rownum=1; --204? \u5148\u67e5\u51fa\u88ab\u5835\u585e\u4f1a\u8bddid
?select * from test for update;<\/p>
\u6392\u9664\uff0c\u89e3\u51b3
session3:
--\u627e\u5230\u88ab\u5835\u585e\u4f1a\u8bdd\u4e2d\uff0c\u53ef\u4ee5\u770b\u5230\u884c\u9501\u7684\u7b49\u5f85\u4e8b\u4ef6enq: TX - row lock contention\uff0c\u8fd8\u53ef\u4ee5\u770b\u5230\u5236\u9020\u5835\u585e\u7684\u6e90\u5934
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<\/p>
\u67e5\u627e\u5230\u6e90\u5934\u7684\u4f1a\u8bdd\u4fe1\u606f
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<\/p>
\u73b0\u5728\u5b9e\u4f8b1\u4e0a\u6740\u4f1a\u8bdd\uff1a
alter system kill session '134,3559';<\/p>
\u6709\u7684\u65f6\u5019\u4e0d\u4e00\u5b9a\u80fd\u6740\u6389\uff0c\u5219\u9700\u8981\u5728\u64cd\u4f5c\u7cfb\u7edf\u5c42\u9762\u4e0a\u6740\uff1a
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
? ?
\u767b\u9646\u5b9e\u4f8b1\u6267\u884c
?
kill -9? 27004<\/p>
\u4e8c\u3001DDL\u9501\u89e3\u51b3\u529e\u6cd5
?\u5047\u8bbe\u9501\u5728\u4f1a\u8bdd1\u4e0a
session1:
?create or replace procedure p_test is
?begin
? dbms_lock.sleep(1000);
?end;<\/p>
?call p_test();<\/p>
\u7cfb\u7edf\u73b0\u5728\u65e0\u6cd5\u5b8c\u6210DML\u64cd\u4f5c\uff0c\u8fd9\u4e2a\u65f6\u5019\u8981\u4eba\u5de5\u9020\u6210\u4e00\u4e2a\u9501\u7b49\u5f85\u51b2\u7a81\u7684\u73b0\u8c61
session2:
?select sid from v$mystat where rownum=1; --134? \u5148\u67e5\u51fa\u88ab\u5835\u585e\u4f1a\u8bddid
?drop procedure p_test;<\/p>
\u6392\u9664\uff0c\u89e3\u51b3
session3:
--\u627e\u5230\u88ab\u5835\u585e\u4f1a\u8bdd\u4e2d\uff0c\u53ef\u4ee5\u770b\u5230\u884c\u9501\u7684\u7b49\u5f85\u4e8b\u4ef6enq: TX - row lock contention\uff0c\u8fd8\u53ef\u4ee5\u770b\u5230\u5236\u9020\u5835\u585e\u7684\u6e90\u5934
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<\/p>
\u67e5\u627e\u5230\u6e90\u5934\u7684\u4f1a\u8bdd\u4fe1\u606f
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<\/p>
\u73b0\u5728\u5b9e\u4f8b1\u4e0a\u6740\u4f1a\u8bdd\uff1a
alter system kill session '202,105';<\/p>
\u6709\u7684\u65f6\u5019\u4e0d\u4e00\u5b9a\u80fd\u6740\u6389\uff0c\u5219\u9700\u8981\u5728\u64cd\u4f5c\u7cfb\u7edf\u5c42\u9762\u4e0a\u6740\uff1a
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
? ?
\u767b\u9646\u5b9e\u4f8b1\u6267\u884c
kill -9 27008<\/p>