SQL数据库死锁查询语句整理

2025-07-07 04:03:37 2102

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 DESC

4.状态说明

SQL 系统进程的运行(status)状态（Runnable、Running、Suspended、Sleeping、Pending、Dormant、Background、Spinlock）1pending（等待）代表这个process，既没有Thread可用，也没有CPU可用，正在同时等待这两项系统资源。2runnable代表这个process，有Thread可用，但没有CPU可用，所以它正在等待CPU这项系统资源。3runningprocess，有Thread可用，有CPU可用。4suspended（暂停）process，正在「等待」别的process执行，等待的系统资源可能是Disk I/O或数据库的Lock。（若这个process执行的SELECT没加上NOLOCK关键字，而别的process正在进行「交易」或写入（会加Lock），则这个SELECT的process就会呈现“suspended”的状态。）5sleepingprocess，目前没在做任何事，正在等待进一步的指令。（sleeping 代表建立了数据库连接，但程序没有发出SQL命令. 因为应用程序为了减少打开和关闭连接的开销，在完成数据库中的操作后，仍然保持数据库的连接，这些连接的最主要目的是重用。）6dormant（暂时搁置）SQL Server正在对这个process做reset。7backgroundprocess正在SQL Server背景执行。即使你看到有很多“background”process正在执行，也不必担心。8Spinlock（自旋锁）查询处于某种运行模式，它在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]

/****** Object: StoredProcedure [dbo].[SP_WCS_LOCK] Script Date: 2024/12/13 16:30:41 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SP_WCS_LOCK]

-- =============================================

-- WCS JOB自动处理-- Create by FISH

-- DATE 2024.12.12

-- MOD USER&DATE

-- =============================================

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 CATCH

7.汇总集合 ---恶意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

2024年如何推广产品：10种高效方法

装修用钢琴烤漆好看吗？你家适不适合用钢琴烤漆？

SQL数据库死锁查询语句整理

友情链接