记一次Oracle数据库死锁

1. 查询活动会话信息

前言

本文不讲什么是锁，主要记录一次针对实际场景进行的分析判断。

一、简介

应用的人找来说某重要系统功能再次出现了问题，请求杀掉堵塞的会话。

二、分析处理过程

1. 查询活动会话信息

查询sql见 变更支持中的二、查询活动会话信息
输出如下：

死锁.png

在4号节点上，堵塞sid为23041的会话的会话sid为10625，堵塞sid为10625的会话的会话sid为23041，是死锁无疑了。
也可用以下sql直接查询有无死锁

SELECT G1.INST_ID,
       G1.SID
       || ','
       || G1.SERIAL#                SID#,
       G1.MACHINE,
       G1.USERNAME,
       G1.TYPE                      EVENT,
       G1.STATE,
       G1.WAIT_TIME_MICRO,
       G1.BLOCKING_INSTANCE
       || ( CASE
              WHEN G1.BLOCKING_INSTANCE IS NULL THEN NULL
              ELSE ','
            END )
       || G1.BLOCKING_SESSION       BL_SESS,
       G1.FINAL_BLOCKING_INSTANCE
       || ( CASE
              WHEN G1.FINAL_BLOCKING_INSTANCE IS NULL THEN NULL
              ELSE ','
            END )
       || G1.FINAL_BLOCKING_SESSION FI_BL_SESS,
       G1.FINAL_BLOCKING_SESSION_STATUS,
       G1.STATUS,
       G1.COMMAND,
       G1.SQL_ID
FROM   GV$SESSION G1,
       GV$SESSION G2
WHERE  G1.BLOCKING_INSTANCE = G2.INST_ID
       AND G1.BLOCKING_SESSION = G2.SID
       AND G2.BLOCKING_INSTANCE = G1.INST_ID
       AND G2.BLOCKING_SESSION = G1.SID
       AND G1.WAIT_CLASS# <> 6
       AND G2.WAIT_CLASS# <> 6;

若有输出，则存在死锁。

2. 找出具体sql给应用

在我们的活动会话及死锁查询sql中，有一个sql_id列，根据sql_id列获得sql_id，通过视图gv$sql获得sql文本，查询语句如下：

SELECT SQL_ID,
       SQL_TEXT
FROM   GV$SQL
WHERE  SQL_ID = '&SQL_ID';

找出sql文本如下：

UPDATE
  XXX
SET
  A= :B1,
  B = SYSDATE
WHERE
  1 = 1
  AND C = :B5
  AND D = TO_NUMBER(:B4)
  AND E = :B3
  AND F = :B2;

sql中使用了:B1、:B2之类的绑定变量，应用想进一步知道这些绑定变量的值，以便定位到是哪个模块导致了死锁。查询视图gv$sql_bind_capture获取绑定变量的具体值，

SELECT *
FROM   GV$SQL_BIND_CAPTURE
WHERE  SQL_ID = '&SQL_ID';

三、结尾

应用将相关模块停掉重启应用，数据库上查询活动会话无死锁出现，连堵塞的会话都没有了。