# 各类数据库死锁、阻塞问题排查
# 一、Sqlserver数据库
1、sqlserver头锁检查：
DECLARE @TRACK_DATETIME VARCHAR(23);
IF object_id(N'tempdb..#TAB_TRACK_EXECSQL') IS NOT NULL
BEGIN
EXEC('DROP TABLE #TAB_TRACK_EXECSQL');
END;
CREATE TABLE #TAB_TRACK_EXECSQL(
HEAD_LOCK INT, -- 头锁
SQL_TEXT NVARCHAR(MAX), -- SQL语句
FETCH_CURSOR INT, -- fetch_cursor状态
CPU_TIME INT, -- 运行时间
SESSION_ID INT, -- 会话ID
PROC_COUNT INT, -- 运行线程数量
EXEC_COMMAND NVARCHAR(50), -- 命令
BLOCKING_SESSION_ID INT, -- 阻塞其会话的会话ID
DATABASE_NAME NVARCHAR(128), -- 数据库名称
REQUEST_ID INT, -- 请求ID
START_TIME NVARCHAR(25), -- 开始时间
EXEC_STATUS NVARCHAR(50), -- 状态
PHY_READS BIGINT, -- 物理读次数
PHY_WRITES BIGINT, -- 写次数
LOGICAL_READS BIGINT, -- 逻辑读次数
ROW_COUNT BIGINT, -- 返回结果行数
WAIT_TYPE NVARCHAR(80), -- 等待资源类型
WAIT_TIME INT, -- 等待时间
WAIT_RESOURCE NVARCHAR(256), -- 等待的资源
CLIENT_NET_ADDRESS NVARCHAR(80), -- 客户端地址
P_LOGINAME NVARCHAR(80), -- 登录用户名
QUERY_HASH binary(8), -- SQL的HASH
TRACK_DATETIME NVARCHAR(25), -- 采集时刻
QUERY_PLAN NVARCHAR(MAX), -- SQL执行计划
SQL_PARAM NVARCHAR(MAX), -- SQL变量绑定参数
TOTAL_ELAPSED_TIME INT, -- 总的经过时间
TRANSACTION_ID BIGINT, -- 执行此请求的事务的 ID
OPEN_TRANSACTION_COUNT INT, --为此请求打开的事务数
OPEN_RESULTSET_COUNT INT, --为此请求打开的结果集的个数
LOCAL_NET_ADDRESS NVARCHAR(80), --提供服务的本地ip
TRACK_JOB_NAME NVARCHAR(50) --采集job名称
);
SET @TRACK_DATETIME=convert(char(23),getdate(),121);
with t_proc AS (SELECT spid,count(*) proc_count,max(loginame) loginame from sys.sysprocesses group by spid)
INSERT INTO #TAB_TRACK_EXECSQL
(HEAD_LOCK
,SQL_TEXT
,FETCH_CURSOR
,CPU_TIME
,SESSION_ID
,PROC_COUNT
,EXEC_COMMAND
,BLOCKING_SESSION_ID
,DATABASE_NAME
,REQUEST_ID
,START_TIME
,EXEC_STATUS
,PHY_READS
,PHY_WRITES
,LOGICAL_READS
,ROW_COUNT
,WAIT_TYPE
,WAIT_TIME
,WAIT_RESOURCE
,CLIENT_NET_ADDRESS
,P_LOGINAME
,QUERY_HASH
,TRACK_DATETIME
,QUERY_PLAN
,SQL_PARAM
,TOTAL_ELAPSED_TIME
,TRANSACTION_ID
,OPEN_TRANSACTION_COUNT
,OPEN_RESULTSET_COUNT
,LOCAL_NET_ADDRESS
,TRACK_JOB_NAME)
SELECT
0 as HEAD_LOCK, --头锁
(case when substring(dest.text,1,16)='FETCH API_CURSOR' then
(SELECT top 1 t.text
FROM sys.dm_exec_cursors (der.session_id ) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t)
else dest.text END) AS SQL_TEXT, --SQL语句
(case when substring(dest.text,1,16)='FETCH API_CURSOR' then 1 else 0 END) AS FETCH_CURSOR, --fetch_cursor状态
der.cpu_time, --运行时间
der.session_id, --会话ID
t_proc.proc_count, --运行线程数
der.command, --命令
der.blocking_session_id, --阻塞其会话的会话ID
DB_NAME(der.database_id) AS DATABASE_NAME, --数据库名称
der.request_id, --请求ID
convert(char(23),der.start_time,121) AS start_time,--开始时间
der.status, --状态
der.reads, --物理读次数
der.writes, --写次数
der.logical_reads, --逻辑读次数
der.row_count, --返回结果行数
der.wait_type, --等待资源类型
der.wait_time, --等待时间
der.wait_resource, --等待的资源
conn.client_net_address, --客户端地址
t_proc.loginame, --登录用户名
der.query_hash, --SQL的HASH
@TRACK_DATETIME as TRACK_DATETIME, --采集时刻
dest_plan.query_plan, -- '执行计划XML'
(case when charindex('<ParameterList>',dest_plan.query_plan)>0 then
substring(dest_plan.query_plan,charindex('<ParameterList>',dest_plan.query_plan),
charindex('</ParameterList>',dest_plan.query_plan)-charindex('<ParameterList>',dest_plan.query_plan)+16
)
else '' end) AS SQL_PARAM, -- SQL变量绑定参数
der.total_elapsed_time, --总经过时间
der.transaction_id,-- 请求的事务id
der.open_transaction_count,--打开的事务数
der.open_resultset_count, --结果集的个数
conn.local_net_address, --服务端本地地址
'TRACK_EXECSQLTEXT_JOB024' as TRACK_JOB_NAME --采集job名称
FROM sys.dm_exec_requests der
left join sys.dm_exec_connections conn on (conn.session_id = der.session_id)
left join t_proc on (t_proc.spid=der.session_id)
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_text_query_plan(der.plan_handle,DEFAULT,DEFAULT) AS dest_plan
WHERE der.session_id<> @@spid; -- 不取本数据库的操作
-- 写入执行完sql但是头锁的连接最近执行sql内容
with blocked_sid AS (SELECT blocking_session_id,MAX(TRACK_JOB_NAME) AS TRACK_JOB_NAME,MAX(TRACK_DATETIME) AS TRACK_DATETIME
from #TAB_TRACK_EXECSQL
where TRACK_DATETIME=@TRACK_DATETIME and blocking_session_id>0 and blocking_session_id<>session_id
GROUP BY blocking_session_id)
INSERT INTO #TAB_TRACK_EXECSQL(HEAD_LOCK,BLOCKING_SESSION_ID,SESSION_ID,SQL_TEXT,SQL_PARAM,FETCH_CURSOR,CLIENT_NET_ADDRESS,TRACK_DATETIME,TRACK_JOB_NAME,LOCAL_NET_ADDRESS,P_LOGINAME)
SELECT 0 AS HEAD_LOCK,0 AS BLOCKING_SESSION_ID,C.SESSION_ID,S.TEXT AS SQL_TEXT,'最近执行内容' AS SQL_PARAM,-1 AS FETCH_CURSOR,C.CLIENT_NET_ADDRESS,A.TRACK_DATETIME,A.TRACK_JOB_NAME
,LOCAL_NET_ADDRESS,(SELECT max(loginame) loginame from sys.sysprocesses WHERE SPID=A.blocking_session_id) AS P_LOGINAME
from blocked_sid a,sys.dm_exec_connections C --执行连接，最近执行的查询信息
cross apply sys.dm_exec_sql_text(C.most_recent_sql_handle) S
where C.SESSION_ID=a.blocking_session_id and not exists (select 1 from #TAB_TRACK_EXECSQL where TRACK_DATETIME=A.TRACK_DATETIME and session_id=a.blocking_session_id);
-- 改写头锁标志
UPDATE #TAB_TRACK_EXECSQL SET HEAD_LOCK=1
WHERE SESSION_ID IN (select blocking_session_id
FROM #TAB_TRACK_EXECSQL
WHERE TRACK_DATETIME=@TRACK_DATETIME AND blocking_session_id>0 group by blocking_session_id)
AND blocking_session_id=0 AND TRACK_DATETIME=@TRACK_DATETIME;
-- 返回查询记录
WITH CX_ROWS AS (SELECT * FROM #TAB_TRACK_EXECSQL WHERE TRACK_DATETIME=@TRACK_DATETIME)
,NOCX_ROWS AS (
SELECT BLOCKING_SESSION_ID,MAX(TRACK_DATETIME) AS TRACK_DATETIME
FROM CX_ROWS A WHERE BLOCKING_SESSION_ID>0 AND NOT EXISTS (SELECT 1 FROM CX_ROWS WHERE SESSION_ID=A.BLOCKING_SESSION_ID)
GROUP BY BLOCKING_SESSION_ID
)
,CX_TAB AS (
SELECT * FROM CX_ROWS
UNION ALL
SELECT 1,'无SQL',0,0,BLOCKING_SESSION_ID,0,'',0,'',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,TRACK_DATETIME,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM NOCX_ROWS
)
,CTE AS (
select a.*,rowNumber = ROW_NUMBER() OVER(PARTITION BY a.TRACK_DATETIME ORDER BY a.HEAD_LOCK DESC,a.SESSION_ID) from CX_TAB a
WHERE BLOCKING_SESSION_ID=0
union all
select a.*,rowNumber = ROW_NUMBER() OVER(ORDER BY a.SESSION_ID) from CX_TAB a
join cte b on (a.BLOCKING_SESSION_ID = b.SESSION_ID AND A.TRACK_DATETIME=B.TRACK_DATETIME )
)
,CTE_ALL AS (
SELECT A.*, locks_path=CONVERT(VARCHAR(100), session_id), sortOrder = CONVERT(VARCHAR(100), RIGHT(CONCAT('00000', rowNumber), 5)) FROM CTE A
WHERE A.BLOCKING_SESSION_ID=0
UNION ALL
SELECT S.*, locks_path=CONVERT(VARCHAR(100), P.locks_path +'->' + cast(S.session_id as varchar)) ,sortOrder = CONVERT(VARCHAR(100), P.sortOrder + RIGHT(CONCAT('00000', S.rowNumber), 5))
FROM CTE S INNER JOIN CTE_ALL P ON S.BLOCKING_SESSION_ID = P.SESSION_ID AND S.TRACK_DATETIME=P.TRACK_DATETIME
)
SELECT
HEAD_LOCK AS N'头锁',
locks_path AS N'锁树',
'"'+SQL_TEXT+'"' AS N'SQL语句',
FETCH_CURSOR AS N'fetch状态',
'"'+SQL_PARAM+'"' AS N'变量参数',
TOTAL_ELAPSED_TIME AS N'总经过时间',
CPU_TIME AS N'CPU时间',
SESSION_ID AS N'会话ID',
PROC_COUNT AS N'线程数量',
EXEC_COMMAND AS N'命令',
BLOCKING_SESSION_ID AS N'阻塞其会话ID',
DATABASE_NAME AS N'数据库名称',
REQUEST_ID AS N'请求ID',
START_TIME AS N'开始时间',
EXEC_STATUS AS N'状态',
PHY_READS AS N'物理读次数',
PHY_WRITES AS N'写次数',
LOGICAL_READS AS N'逻辑读次数',
ROW_COUNT AS N'返回结果行数',
WAIT_TYPE AS N'等待资源类型',
WAIT_TIME AS N'等待时间',
WAIT_RESOURCE AS N'等待的资源',
CLIENT_NET_ADDRESS AS N'客户端地址',
P_LOGINAME AS N'登录用户名',
QUERY_HASH AS N'SQL的HASH',
TRACK_DATETIME AS N'采集时刻',
TRANSACTION_ID AS N'请求的事务id',
OPEN_TRANSACTION_COUNT AS N'打开的事务数',
OPEN_RESULTSET_COUNT AS N'结果集的个数',
LOCAL_NET_ADDRESS AS N'提供服务的本地ip',
sortOrder
FROM CTE_ALL
union all
select
HEAD_LOCK AS N'头锁',
cast(SESSION_ID as varchar) AS N'锁树',
'"'+SQL_TEXT+'"' AS N'SQL语句',
FETCH_CURSOR AS N'fetch状态',
'"'+SQL_PARAM+'"' AS N'变量参数',
TOTAL_ELAPSED_TIME AS N'总经过时间',
CPU_TIME AS N'CPU时间',
SESSION_ID AS N'会话ID',
PROC_COUNT AS N'线程数量',
EXEC_COMMAND AS N'命令',
BLOCKING_SESSION_ID AS N'阻塞其会话ID',
DATABASE_NAME AS N'数据库名称',
REQUEST_ID AS N'请求ID',
START_TIME AS N'开始时间',
EXEC_STATUS AS N'状态',
PHY_READS AS N'物理读次数',
PHY_WRITES AS N'写次数',
LOGICAL_READS AS N'逻辑读次数',
ROW_COUNT AS N'返回结果行数',
WAIT_TYPE AS N'等待资源类型',
WAIT_TIME AS N'等待时间',
WAIT_RESOURCE AS N'等待的资源',
CLIENT_NET_ADDRESS AS N'客户端地址',
P_LOGINAME AS N'登录用户名',
QUERY_HASH AS N'SQL的HASH',
TRACK_DATETIME AS N'采集时刻',
TRANSACTION_ID AS N'请求的事务id',
OPEN_TRANSACTION_COUNT AS N'打开的事务数',
OPEN_RESULTSET_COUNT AS N'结果集的个数',
LOCAL_NET_ADDRESS AS N'提供服务的本地ip',
'自身或父类存在交叉无法生成' AS sortOrder
from CX_TAB cc
where not exists (select 1 from CTE_ALL where session_id=cc.session_id)
ORDER BY TRACK_DATETIME DESC,sortOrder ;
# 二、达梦数据库
1、查询当前sql
select datediff(ss,last_recv_time,sysdate) ss,dbms_lob.substr(sf_get_session_sql(sess_id)),sess_id,substr(clnt_ip,8,13) from v$sessions where state='ACTIVE' order by 1 desc;
2、查询阻塞
SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,
'被阻塞的信息' WT,S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
'引起阻塞的信息' FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
FROM V$SESSIONS S1,V$SESSIONS S2,V$TRXWAIT W
WHERE S1.TRX_ID=W.ID
AND S2.TRX_ID=W.WAIT_FOR_ID;
3、查询死锁涉及的事务信息
select dh.trx_id,sh.sess_id,wm_concat(top_sql_text) from V$DEADLOCK_HISTORY dh,V$SQL_HISTORY sh where dh.trx_id=sh.trx_id and dh.sess_id=sh.sess_id group by dh.trx_id,sh.sess_id;
# 三、Oracle数据库
1、查询会话、活动会话
select * from v$session where username is not null and status = 'ACTIVE' order by logon_time, sid;
select * from v$session where username is not null order by logon_time, sid;
2、查oracle各种锁源头
SELECT
SUBSTR(sys_connect_by_path(s.INST_ID||'#'||s.SID, '-->'),4) AS TREE_SID,
S.INST_ID, -- 对于RAC的节点
S.SID, -- 会话ID
S.SERIAL# ,
S.STATUS, -- 会话状态
S.MACHINE, -- 客户端机器名
S.PROGRAM, -- 客户端运行程序
S.SQL_ID, -- 执行sql的id
B.SQL_TEXT, -- 执行sql的文本
(CASE WHEN LENGTHB(B.SQL_TEXT)<3990 THEN TO_CLOB('') ELSE B.SQL_FULLTEXT END) SQL_FULLTEXT, -- sql长度超过3990字节才取fulltext，否则返回空
S.WAIT_CLASS, -- 等待类型
S.EVENT, -- 等待事件
S.SECONDS_IN_WAIT, --等待时间（秒)
TO_CHAR(S.SQL_EXEC_START,'YYYY-MM-DD HH24:MI:SS') SQL_EXEC_START, -- SQL执行开始时间
TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME, -- 会话登录时间
( select LISTAGG（B.object_name,','||CHR(13)) WITHIN group (ORDER BY B.OWNER) from GV$LOCKED_OBJECT A,DBA_OBJECTS B
where A.inst_id=S.inst_id AND A.session_id =S.SID
AND B.owner=S.username AND B.object_id=A.object_id) LOCK_OBJECT_NAME -- 锁定对象名称
FROM GV$SESSION S
LEFT JOIN GV$SQL B ON (B.INST_ID=S.INST_ID AND B.SQL_ID=S.SQL_ID)
WHERE S.TYPE='USER'
and S.sid<>(select sys_context('userenv','sid') from dual) -- 不含本次查询连接会话
AND (S.BLOCKING_SESSION IS NOT NULL
OR (S.INST_ID||'#'||S.SID) IN(SELECT DISTINCT BLOCKING_INSTANCE||'#'||BLOCKING_SESSION FROM GV$SESSION)
OR S.status='ACTIVE'
OR (S.status='INACTIVE' AND S.TADDR IS NOT NULL)
)
START WITH (s.BLOCKING_INSTANCE||'#'||s.BLOCKING_SESSION) = '#'
CONNECT BY PRIOR (S.INST_ID||'#'||S.SID) = (s.BLOCKING_INSTANCE||'#'||s.BLOCKING_SESSION)
order by TREE_SID;
3、锁表检查
-- 最右边是锁的源头
select * from (select a.sid, a.serial#,a.sql_id,a.event,a.status,a.username,a.machine,a.program,a.logon_time,connect_by_isleaf as isleaf,sys_connect_by_path(SID, '<-') tree,level as tree_level from v$session a start with a.blocking_session is not null connect by nocycle a.sid = prior a.blocking_session) where isleaf = 1 order by tree_level asc;
4、查慢sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum <= 50
# 四、Mysql数据库
# 4.1 查询锁阻塞
# mysql 8.0
SELECT
-- b.trx_id blocking_trx_id,
b.trx_mysql_thread_id 阻塞会话id,
b.trx_query 阻塞sql,
-- r.trx_id waiting_trx_id,
r.trx_mysql_thread_id 被阻塞会话id,
r.trx_query 被阻塞sql
FROM
PERFORMANCE_SCHEMA .data_lock_waits w,
information_schema.innodb_trx r,
information_schema.innodb_trx b
WHERE
b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
AND r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;
# mysql 5.7
SELECT
-- b.trx_id blocking_trx_id,
b.trx_mysql_thread_id 阻塞会话id,
b.trx_query 阻塞sql,
-- r.trx_id waiting_trx_id,
r.trx_mysql_thread_id 被阻塞会话id,
r.trx_query 被阻塞sql
FROM
information_schema.innodb_lock_waits w,
information_schema.innodb_trx b,
information_schema.innodb_trx r
WHERE
b.trx_id = w.blocking_trx_id
AND r.trx_id = w.requesting_trx_id
# 4.2 其他查询方式
1、查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;
2、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4、查询是否锁表
SHOW OPEN TABLES where In_use > 0;
在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。
5、查看最近死锁的日志
show engine innodb status
解除死锁
如果需要解除死锁，有一种最简单粗暴的方式，那就是找到进程id之后，直接干掉。
查看当前正在进行中的进程
show full processlist;
// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;
这两个命令找出来的进程id 是同一个。
杀掉进程对应的进程 id
kill id
验证（kill后再看是否还有锁）
SHOW OPEN TABLES where In_use > 0;
# 五、人大金仓数据库
正在执行的sql
select state,query from pg_stat_activity where query not in ('COMMIT','ROLLBACK');
查慢sql记录
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '5 s' order by query_start ;
select query,calls,round(mean_exec_time,2)as mean_exec_time from sys_stat_statements order by mean_exec_time desc;
死锁
select * from sys_locks where granted = 'f';
编撰人：yangtao、wangyxyf、het、zhangshuang、admin
快速跳转
