背景:执行某个查询功能,无返回无报错,再次请求时出现死锁异常。导致后台和其他相关的后台服务无法对该表进行任何操作。
1、查询导致死锁的session和OBJECT_NAME(被锁死的表)
select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;
2、select s.username,s.sid,s.serial#,s.logon_time
from v$locked_object l,v$session s
where l.session_id=s.sid
order by s.logon_time;
此时可以通过“alter system kill session 'sid,serial#';”命令kill掉死锁进程;
3、若步骤2无效:执行select pro.spid from v$session ses,v$process pro where ses.sid='xxx'and ses.paddr=pro.addr;命令获取进程号SPID。
执行orakill orcl SPID;命令杀死进程。
查询导致死锁的SQL:
SELECT SQL_TEXT
FROM V$SQLAREA,V$SESSION,V$PROCESS
WHERE V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESS
AND V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE
AND V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID ='XXXX';