判断数据库死锁的Sql语句

最新推荐文章于 2025-09-14 22:47:30 发布

最新推荐文章于 2025-09-14 22:47:30 发布 · 106 阅读

文章标签：

#SQL

本文介绍了一个用于检测数据库中是否发生死锁的SQL语句。通过使用syslockinfo表结合多个spt_values表来获取锁定信息，并展示如何查看锁定请求的状态、类型及模式等详细信息。

判断数据库死锁的Sql语句

sp_lock

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,db.name as DBName,
rsc_objid As ObjId,object_name(rsc_objid) as ObjectName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status

from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysdatabases db

where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L' and db.dbid=rsc_dbid and rsc_objid>0
order by spid