面试官：发生死锁时，如何知道会话被哪个会话堵塞的相关信息？

概述

先看一个数据库监控图：

这上面的信息中，怎么判断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死锁的一些命令总结，感兴趣的朋友可以关注下~