如何查询oracle死锁,Oracle死锁查看和解决办法汇总

由于生产的tomcat 经常有假死问题，困扰很久，最后发现有死锁，解决办法分享

1、查看死锁

1)用dba用户执行以下语句select username,lockwait,status,machine,program from v$session where sid in

(select session_id from 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))

2、死锁的解决方法

1)查找死锁的进程：SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,

l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS

FROM V$LOCKED_OBJECT l,V$SESSION S WHERE 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替换:

--ORACLE 就是查表，需要SYSTEM，SYS(有相应权限的用户)执行下面的SQL语句就可以了查看锁代码SELECT sn.username,

m.SID,

sn.SERIAL#,

m.TYPE,

DECODE(m.lmode,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl.',

4,

'Share',

5,

'S/Row Excl.',

6,

'Exclusive',

lmode,

LTRIM(TO_CHAR(lmode, '990'))) lmode,

DECODE(m.request,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl.',

4,

'Share',

5,

'S/Row Excl.',

6,

'Exclusive',

request,

LTRIM(TO_CHAR(m.request, '990'))) request,

m.id1,

m.id2

FROM v$session sn, v$lock m

WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞

OR (sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定

AND m.request = 0 AND lmode != 4 AND

(id1, id2) IN (SELECT s.id1, s.id2

FROM v$lock s

WHERE request != 0

AND s.id1 = m.id1

AND s.id2 = m.id2))

ORDER BY id1, id2, m.request;

--解锁代码alter system kill session 'sid,SERIAL#';

--查看死锁信息SELECT bs.username "Blocking User", bs.username "DB User",

ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",

bs.serial# "Serial#", bs.sql_address "address",

bs.sql_hash_value "Sql hash", bs.program "Blocking App",

ws.program "Waiting App", bs.machine "Blocking Machine",

ws.machine "Waiting Machine", bs.osuser "Blocking OS User",

ws.osuser "Waiting OS User", bs.serial# "Serial#",

ws.serial# "WSerial#",

DECODE (wk.TYPE,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'USER Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL USER LOCK',

'DX', 'Distributed Xaction',

'CF', 'Control FILE',

'IS', 'Instance State',

'FS', 'FILE SET',

'IR', 'Instance Recovery',

'ST', 'Disk SPACE Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

'LS', 'LOG START OR Switch',

'RW', 'ROW Wait',

'SQ', 'Sequence Number',

'TE', 'Extend TABLE',

'TT', 'Temp TABLE',

wk.TYPE

) lock_type,

DECODE (hk.lmode,

0, 'None',

1, 'NULL',

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (hk.lmode)

) mode_held,

DECODE (wk.request,

0, 'None',

1, 'NULL',

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (wk.request)

) mode_requested,

TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,

DECODE

(hk.BLOCK,

0, 'NOT Blocking', /**//* Not blocking any other processes */

1, 'Blocking', /**//* This lock blocks other processes */

2, 'Global', /**//* This lock is global, so we can't tell */

TO_CHAR (hk.BLOCK)

) blocking_others

FROM v$lock hk, v$session bs, v$lock wk, v$session ws

WHERE hk.BLOCK = 1

AND hk.lmode != 0

AND hk.lmode != 1

AND wk.request != 0

AND wk.TYPE(+) = hk.TYPE

AND wk.id1(+) = hk.id1

AND wk.id2(+) = hk.id2

AND hk.SID = bs.SID(+)

AND wk.SID = ws.SID(+)

AND (bs.username IS NOT NULL)

AND (bs.username <> 'SYSTEM')

AND (bs.username <> 'SYS')

ORDER BY 1;

由最代码官方编辑于2013-12-13 22:58:42