oracle 死锁

最新推荐文章于 2025-03-07 15:25:09 发布

原创最新推荐文章于 2025-03-07 15:25:09 发布

· 110 阅读

0 ·

版权

文章标签：

#Oracle #SQL

本文介绍了一种通过查询Oracle数据库来诊断死锁的方法。主要包括：1.查找持有锁和等待锁的会话；2.确定这些会话正在执行的SQL语句；3.获取这些SQL语句的具体内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询发生死锁的select语句

select sql_text from v$sql t1
inner join v$session t2
on t1.hash_value = t2.SQL_HASH_VALUE
inner join v$locked_object t3
on t2.SID = t3.session_id

是否有死锁
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

1.Finding the Sessions Holding the Lock

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request

SID ID1 ID2 LMODE REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237 196705 200493 6 0 TX <- Lock Holder
1256 196705 200493 0 6 TX <- Lock Waiter
1176 196705 200493 0 6 TX <- Lock Waiter
938 589854 201352 6 0 TX <- Lock Holder
1634 589854 201352 0 6 TX <- Lock Waiter
2.Finding the Statements being Executed by These Sessions
SELECT sid,sql_address, sql_hash_value
FROM V$SESSION
WHERE SID IN (1237,1256,1176,938,1634);

SID SQL_HASH_VALUE
----- --------------
938 2078523611 <-Holder
1176 1646972797 <-Waiter
1237 3735785744 <-Holder
1256 1141994875 <-Waiter
1634 2417993520 <-Waiter

3.Finding the Text for These SQL Statements
SELECT * FROM v$sqltext t
WHERE t.ADDRESS=''--代入上一步查到的sql_address
AND t.HASH_VALUE=''--代入上一步查到的sql_hash_value
ORDER BY t.ADDRESS,t.HASH_VALUE,t.COMMAND_TYPE,t.PIECE;