Oracle 死锁的检测查询及处理

<div id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New',courier,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">
<pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New',monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">-- 死锁查询语句

    bs.username "Blocking ",bs.username "DB ",

",bs.SID "SID",ws.SID "WSID",

 hash",bs.program "Blocking App",monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">          ws.program "Waiting App",bs.machine "Blocking Machine",monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">          ws.machine "Waiting Machine",bs.osuser "Blocking OS ",monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">          ws.osuser "Waiting OS ",bs.serial# "Serial#",monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">          ws.serial# "WSerial#",monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">          DECODE (wk.TYPE,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  ,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  wk.TYPE

,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  1,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  2,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  3,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  4,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  5,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  6,,monospace; direction: ltr; border-top-style: none; color: black; font-size: 10pt; border-left-style: none; overflow: visible; padding-top: 0px">                  TO_CHAR (hk.lmode)

,
,
,
 v$lock hk,v$ bs,v$lock wk,v$ ws
 hk.BLOCK = 1
 hk.lmode != 0
 hk.lmode != 1
 wk.request != 0
 wk.TYPE(+) = hk.TYPE
 wk.id1(+) = hk.id1
 wk.id2(+) = hk.id2
 hk.SID = bs.SID(+)
 wk.SID = ws.SID(+)
 (bs.username   )
 (bs.username <> )
 (bs.username <> )
  1;



查询发生死锁的select语句


 sql_text  v$  hash_value  (
 sql_hash_value  v$  sid  ( session_id  v$locked_object)




? 

?

一、数据库死锁的现象程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。


二、死锁的原理当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。


三、死锁的定位方法通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。


1）用dba用户执行以下语句
 username,lockwait,status,machine,program  v$  sid 

 ( session_id  v$locked_object)



如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：Username：死锁语句所用的数据库用户；Lockwait：死锁的状态，如果有内容表示被死锁。Status： 状态，active表示被死锁Machine： 死锁语句所在的机器。Program： 产生死锁的语句主要来自哪个应用程序。


2）用dba用户执行以下语句，可以查看到被死锁的语句select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in(select session_id from v$locked_object))


四、死锁的解决方法???? 一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。 

　　

? 

???? 1）查找死锁的进程： 

 sysdba" (sys/change_on_install)

 s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
 V$LOCKED_OBJECT l,V$ S 
 l.SESSION_ID=S.SID;



　　2）kill掉这个死锁的进程： 

　　alter system kill session ‘sid,serial#’; （其中sid=l.session_id） 

? 
　　3）如果还不能解决： 

????????? select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

?? 其中sid用死锁的sid替换: 

?????? exit?????? ps -ef|grep spid

?????? 其中spid是这个进程的进程号，kill掉这个Oracle进程。

转自：
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!