SQL数据库死锁查询语句整理
·
1.查询数据库
Select name, --数据库名
dbid --数据ID
from master.sys.sysdatabases
2.查询死锁语句及内容
SELECT * FROM sys.sysprocesses
where spid >50 and blocked <> 0
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName,*
from sys.dm_tran_locks
where resource_type='OBJECT' 说明 死锁必定是blocked <>0
block 输出的ID是当前死锁的ID：假设ID是1991，所以要解决死锁就是要KILL掉即 【 kill 1991 】
spid --SQL Server会话ID
kpid --Windows线程ID。
blocked,--正在阻塞其他会话的会话ID。如果此列为Null，则表示请求未被阻塞。
waittype,--当前连接的等待资源编号，0或Null表示不需要等待任何资源。
waittime,--当前等待时间，单位为毫秒，0表示没有等待。
dbid,--当前正由进程使用的数据库ID。
uid,--执行命令的用户ID。
login_time,--客户端进程登录到服务器的时间。
last_batch,--上次执行存储过程或Execute语句的时间。对于系统进程，将存储Sql Server的启动时间。
open_tran,--进程的打开事务个数。如果有嵌套事务，就会大于1。
status,--进程状态，如dormant（正在重置会话）、running（会话正在运行一个或多个批处理）、background（会话正在运行一个后台任务）、rollback（会话正在处理事务回滚）、pending（会话正在等待工作线程变为可用）、runnable（会话中的任务在等待获取Scheduler来运行的可执行队列中）、spinloop（会话中的任务正在等待自旋锁变为可用）、suspended（会话正在等待事件完成）3。
hostname,--建立链接的客户端工作站的名称。
program_name,--应用程序的名称，即连接字符串中配置的应用程序名称。
hostprocess,--建立连接的应用程序在客户端工作站里的进程ID号。
cmd,--当前正在执行的命令。
loginame--登录名。
3.查询CPU消耗时间
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
dbname=db_name(qt.dbid),
object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC4.状态说明
| SQL 系统进程的运行(status)状态（Runnable、Running、Suspended、Sleeping、Pending、Dormant、Background、Spinlock） | ||
| 1 | pending（等待） | 代表这个process，既没有Thread可用，也没有CPU可用，正在同时等待这两项系统资源。 |
| 2 | runnable | 代表这个process，有Thread可用，但没有CPU可用，所以它正在等待CPU这项系统资源。 |
| 3 | running | process，有Thread可用，有CPU可用。 |
| 4 | suspended（暂停） | process，正在「等待」别的process执行，等待的系统资源可能是Disk I/O或数据库的Lock。（若这个process执行的SELECT没加上NOLOCK关键字，而别的process正在进行「交易」或写入（会加Lock），则这个SELECT的process就会呈现“suspended”的状态。） |
| 5 | sleeping | process，目前没在做任何事，正在等待进一步的指令。（sleeping 代表建立了数据库连接，但程序没有发出SQL命令. 因为应用程序为了减少打开和关闭连接的开销，在完成数据库中的操作后，仍然保持数据库的连接，这些连接的最主要目的是重用。） |
| 6 | dormant（暂时搁置） | SQL Server正在对这个process做reset。 |
| 7 | background | process正在SQL Server背景执行。即使你看到有很多“background”process正在执行，也不必担心。 |
| 8 | Spinlock（自旋锁） | 查询处于某种运行模式，它在CPU中忙于等待自己的轮询。 |
5.查询超时语句及记录
SELECT Status, --进程ID 状态:dormant(重置)，running(有CPU可用)....
--1.dormant = 正在重置回话（（暂时搁置），代表SQL Server正在对这个process做reset） ;
--2.running = 回话正在运行一个或多个批处理 ;
--3.background = 回话正在运行一个后台任务 ;
--4.rollback = 会话正在处理事务回滚 ;
--6.pending = 回话正在等待工作现成变为可用 ;
--7.runnable = 会话中的任务在等待获取 Scheduler 来运行的可执行队列中 ;
--8.spinloop = 会话中的任务正在等待自旋锁变为可用 ;
--9.suspended = 会话正在等待事件完成
login_time,--客户端进程登录到服务器的时间
last_batch,--上次执行存储过程或Execute语句的时间。对于系统进程，将存储Sql Server的启动时间。
spid, --SQL Server会话ID
blocked, --正在阻塞其他会话的会话ID
object_name(C.resource_associated_entity_id) as TABLENAME,
sp.dbid, --进程使用的数据库ID
DB_NAME(sp.dbid) AS DBName,--进程使用的数据库名
program_name,--应用程序的名称，就是 连接字符串中配的 Application Name
waitresource, --当前连接的等待资源编号，标示是否等待资源，0 或 Null表示不需要等待任何资源
lastwaittype,
sp.loginame, --登入数据库名
sp.hostname, --使用主机名
a.[Text] AS [TextData],--查询语句
SUBSTRING ( A. TEXT,sp.stmt_start / 2,(CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd] --当前命令
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
WHERE spid > 50 AND
blocked >0 AND --查询blocked字段来找出正在阻塞其他会话的进程
request_session_id =spid AND resource_type = 'OBJECT'
--ORDER BY blocked DESC,
--DB_NAME(sp.dbid) ASC,
--a.[text];
6.创建表格记录及存储过程
USE [KingFa_SH]
GO
/****** Object: StoredProcedure [dbo].[SP_WCS_LOCK] Script Date: 2024/12/13 16:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_WCS_LOCK]
-- =============================================
-- WCS JOB自动处理-- Create by FISH
-- DATE 2024.12.12
-- MOD USER&DATE
-- =============================================
AS
BEGIN TRY
BEGIN TRAN -----开始事务
INSERT INTO A_LOCK_LOG(Status,login_time,last_batch,spid,blocked,TABLENAME,dbid,DBName,program_name,waitresource,lastwaittype,loginame,hostname,TextData,current_cmd)
SELECT Status, --进程ID 状态:dormant(重置)，running(有CPU可用)....
--1.dormant = 正在重置回话（（暂时搁置），代表SQL Server正在对这个process做reset） ;
--2.running = 回话正在运行一个或多个批处理 ;
--3.background = 回话正在运行一个后台任务 ;
--4.rollback = 会话正在处理事务回滚 ;
--6.pending = 回话正在等待工作现成变为可用 ;
--7.runnable = 会话中的任务在等待获取 Scheduler 来运行的可执行队列中 ;
--8.spinloop = 会话中的任务正在等待自旋锁变为可用 ;
--9.suspended = 会话正在等待事件完成
login_time,--客户端进程登录到服务器的时间
last_batch,--上次执行存储过程或Execute语句的时间。对于系统进程，将存储Sql Server的启动时间。
spid, --SQL Server会话ID
blocked, --正在阻塞其他会话的会话ID
object_name(C.resource_associated_entity_id) as TABLENAME,
sp.dbid, --进程使用的数据库ID
DB_NAME(sp.dbid) AS DBName,--进程使用的数据库名
program_name,--应用程序的名称，就是 连接字符串中配的 Application Name
waitresource, --当前连接的等待资源编号，标示是否等待资源，0 或 Null表示不需要等待任何资源
lastwaittype,
sp.loginame, --登入数据库名
sp.hostname, --使用主机名
a.[Text] AS [TextData],--查询语句
SUBSTRING ( A. TEXT,sp.stmt_start / 2,(CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd] --当前命令
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
WHERE spid > 50 AND
blocked >0 AND --查询blocked字段来找出正在阻塞其他会话的进程
request_session_id =spid AND resource_type = 'OBJECT'
AND SPID <>blocked --2024 ADD 有时候存在两个ID一样，还是记录很多的资料！！！
--ORDER BY blocked DESC,
--DB_NAME(sp.dbid) ASC,
--a.[text];
COMMIT TRAN
RETURN(0)
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
RETURN(1)
END CATCH7.汇总集合 ---恶意TEST模拟长期锁定 WAITFOR DELAY '00:05:30'的时候 block 不会>0
恶意TEST模拟长期锁定 WAITFOR DELAY '00:05:30'的时候 block 不会>0!!!!!!!
所以上述5的 blocked>0 无法查询出来！
如果有PAGE 或KEY 会导致 OBJECT_NAME(resource_associated_entity_id) AS LOCK_NAME -- 锁定对象名 会报错！！！！！！
所以在WHERE语句加入 resource_type = ‘OBJECT’ ，否则就不要 OBJECT_NAME(resource_associated_entity_id) AS LOCK_NAME
应该使用如下
select distinct
b.blocked,
c.resource_type -- as [资源锁定类型]
--object --表，可以用 OBJECT_NAME(resource_associated_entity_id)
--key 或page 有行锁 或 页锁，所以影响不大，但是如果有行/页
--- OBJECT_NAME(resource_associated_entity_id) 会报错！！！！！
--DATABASE
, b.loginame --登入数据库名
, b.hostname --使用主机名
, DB_NAME(resource_database_id) AS DB_NAME -- 数据库名
, OBJECT_NAME(resource_associated_entity_id) AS LOCK_NAME -- 锁定对象名
, c.resource_associated_entity_id AS LOCK_ID -- 锁定对象
, c.request_mode AS LOCK_MODE -- 等待者请求的锁定模式
, c.request_session_id AS WAIT_ID -- 等待者SID，表示持有锁的会话 ID，也就是 SPID。
, a.wait_duration_ms AS WAIT_TIME -- 等待时间
-- , b.waittime -- 等待时间-X
, b.status --当前等待状态
, b.last_batch --最后更新时间
, (select TEXT
from sys.dm_exec_requests r
cross apply
sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = a.session_id) as WAIT_DO_SQL-- 等待者要执行的SQL
, (select TEXT
from sys.sysprocesses p
cross apply
sys.dm_exec_sql_text(p.sql_handle)
where p.spid = a.session_id
) as LOCK_DO_SQL --锁定者执行语句
from sys.dm_os_waiting_tasks A,sys.sysprocesses(NOLOCK) b,sys.dm_tran_locks c
where b.spid =A.session_id --and b.waittime >10000
and c.request_session_id =a.session_id
and c.resource_type ='object ' ---请加上！！！谢谢！！！！
要解锁就是： kill 77
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
27
0- 0
已为社区贡献4条内容
所有评论(0)