--模拟死锁 /* 查锁和杀锁,使用 ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; */ set linesize 200; SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME, T3.SQL_TEXT FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3 WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS ORDER BY T2.LOGON_TIME; ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; --实例程序的执行结果如下: SQL> set linesize 200; SQL> SELECT T2.USERNAME, 2 T2.SID, 3 T2.SERIAL#, 4 T2.LOGON_TIME, 5 T3.SQL_TEXT 6 FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3 7 WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS 8 ORDER BY T2.LOGON_TIME; USERNAME SID SERIAL# LOGON_TIME SQL_TEXT ------------------------------ ---------- ---------- -------------- -------------------------------- XAXNB 137 481 17-4月 -11 update emp set empname='chenzzaz' where empid=2 SQL> alter system kill session '137,481'; 系统已更改 --对应产生死锁的会话 SQL> update emp set empname='chenzzaz' where empid=2; update emp set empname='chenzzaz' where empid=2 * 第 1 行出现错误: ORA-00028: 您的会话己被终止 /*20110418陈字文宝鸡项目死锁增加*/ SELECT T2.SID, T2.SERIAL# FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3 WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS GROUP BY T2.SID,T2.SERIAL#; ALTER SYSTEM KILL SESSION '493,4'; ALTER SYSTEM KILL SESSION '470,4'; ALTER SYSTEM KILL SESSION '510,4'; ALTER SYSTEM KILL SESSION '471,3';
事务和锁定-通过结束会话方式解除死锁
最新推荐文章于 2020-12-24 06:39:04 发布