概述

先看一个数据库监控图：

这上面的信息中，怎么判断XX会话被另一个会话堵塞呢？

如果用网上的判断死锁sql去查询，类似如下：

思考了一段时间，决定从gv$session入手，下面先看第一条sql

1、查看活动会话信息

--查看活动会话信息
SELECT INST_ID,
 SID,
 SERIAL#,
 PROCESS,
 USERNAME,
 LOCKWAIT,
 SCHEMANAME,
 OSUSER,
 MACHINE,
 PROGRAM,
 SQL_ID,
 MODULE,
 ACTION,
 LOGON_TIME,
 BLOCKING_SESSION_STATUS,
 BLOCKING_INSTANCE,
 BLOCKING_SESSION,
 EVENT,
 WAIT_CLASS,
 SECONDS_IN_WAIT,
 STATE
 FROM GV$SESSION
 WHERE USERNAME IS NOT NULL
 AND STATUS = 'ACTIVE'
 ORDER BY LOGON_TIME, SID;

2、查看当前被阻塞lock信息(waiter)

--注： 如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
--如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
--如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
--如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
SELECT INST_ID "实例ID",
 SID,
 SERIAL#,
 PROCESS,
 USERNAME,
 SCHEMANAME,
 OSUSER,
 MACHINE,
 PROGRAM,
 SQL_ID,
 MODULE,
 ACTION,
 LOCKWAIT,
 BLOCKING_SESSION_STATUS,
 BLOCKING_INSTANCE "阻塞会话实例",
 BLOCKING_SESSION "阻塞会话SID",
 EVENT "等待事件",
 WAIT_CLASS,
 LOGON_TIME,
 CASE
 WHEN STATE = 'WAITING' THEN
 SECONDS_IN_WAIT
 WHEN STATE = 'WAITING KNOWN TIME' THEN
 WAIT_TIME
 END AS "等待时间(s)",
 STATE
 FROM GV$SESSION
 WHERE USERNAME IS NOT NULL
 AND STATUS = 'ACTIVE'
 AND LOCKWAIT IS NOT NULL;

因为想尽量放在一条sql把需要的信息都展示出来，所以字段会比较多。

3、查看阻塞会话SID信息（holder）

--考虑到多实例会存在sid一致的情况，所以加了实例区分
SELECT INST_ID "实例ID",
 SID,
 SERIAL#,
 PROCESS,
 USERNAME,
 SCHEMANAME,
 OSUSER,
 MACHINE,
 PROGRAM,
 SQL_ID,
 MODULE,
 ACTION,
 LOCKWAIT,
 BLOCKING_SESSION_STATUS,
 BLOCKING_INSTANCE,
 BLOCKING_SESSION "阻塞会话SID",
 EVENT "等待事件",
 WAIT_CLASS,
 LOGON_TIME,
 CASE
 WHEN STATE = 'WAITING' THEN
 SECONDS_IN_WAIT
 WHEN STATE = 'WAITING KNOWN TIME' THEN
 WAIT_TIME
 END AS "等待时间(s)",
 STATE
 FROM GV$SESSION
 WHERE INST_ID = '&INSTANCE'
 and SID = '&SID';

4、查看会话ID在执行什么sql

select sid, sql_text
 from v$session a, v$sql b
 where sid in (&sid)
 and (b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);

5、查看SQL_ID相关sql

select listagg(sql_text,' ') within group (order by piece)
 from v$sqltext
 where sql_id = '&sqlid'
 group by sql_id

这个基本上包括了Oracle在发生堵塞锁时的一些查询命令了，后面抽空在整理一下mysql死锁的一些命令总结，感兴趣的朋友可以关注下~