SQLSERVER 查询死锁以及常用解决方案

MESLisnter


select 
    request_session_id spid,   
    OBJECT_NAME(resource_associated_entity_id) tableName    
from 
    sys.dm_tran_locks   
where 
    resource_type='OBJECT'



    -- 解锁表
declare @spid int
Set @spid = 95 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

sp_who2


exec sp_who2

dbcc inputbuffer(102)

kill 102

dbcc inputbuffer(60)

 

select t1.resource_type                                [资源锁定类型]
     , DB_NAME(resource_database_id)                as 数据库名
     , t1.resource_associated_entity_id                锁定对象
     , t1.request_mode                              as 等待者请求的锁定模式
     , t1.request_session_id                           等待者SID
     , t2.wait_duration_ms                             等待时间
     , (select TEXT
        from sys.dm_exec_requests r
               cross apply
             sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id) as 等待者要执行的SQL
     , t2.blocking_session_id                          [锁定者SID]
     , (select TEXT
        from sys.sysprocesses p
               cross apply
             sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
)                                                      锁定者执行语句
from sys.dm_tran_locks t1,
     sys.dm_os_waiting_tasks t2
where t1.lock_owner_address = t2.resource_address

 或者

SELECT 
    t1.resource_type AS [资源锁定类型],
    DB_NAME(t1.resource_database_id) AS [数据库名],
    t1.resource_associated_entity_id AS [锁定对象],
    t1.request_mode AS [等待者请求的锁定模式],
    t1.request_session_id AS [等待者SID],
    -- 修复：通过 sys.dm_exec_connections 关联获取IP（更可靠）
    ISNULL(c1.client_net_address, '本地连接/无IP') AS [等待者IP地址],
    ISNULL(s1.host_name, '未知计算机名') AS [等待者计算机名],
    t2.wait_duration_ms AS [等待时间(ms)],
    -- 等待者执行的SQL（处理NULL）
    ISNULL((SELECT TEXT
            FROM sys.dm_exec_requests r
            CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
            WHERE r.session_id = t1.request_session_id), '无') AS [等待者要执行的SQL],
    t2.blocking_session_id AS [锁定者SID],
    -- 修复：锁定者IP/计算机名
    ISNULL(c2.client_net_address, '本地连接/无IP') AS [锁定者IP地址],
    ISNULL(s2.host_name, '未知计算机名') AS [锁定者计算机名],
    -- 锁定者执行的SQL（处理NULL）
    ISNULL((SELECT TEXT
            FROM sys.sysprocesses p
            CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
            WHERE p.spid = t2.blocking_session_id), '无') AS [锁定者执行语句]
FROM sys.dm_tran_locks t1
INNER JOIN sys.dm_os_waiting_tasks t2 
    ON t1.lock_owner_address = t2.resource_address
-- 等待者：先关联会话，再关联连接（获取IP）
LEFT JOIN sys.dm_exec_sessions s1 
    ON t1.request_session_id = s1.session_id
LEFT JOIN sys.dm_exec_connections c1 
    ON s1.session_id = c1.session_id
-- 锁定者：先关联会话，再关联连接（获取IP）
LEFT JOIN sys.dm_exec_sessions s2 
    ON t2.blocking_session_id = s2.session_id
LEFT JOIN sys.dm_exec_connections c2 
    ON s2.session_id = c2.session_id;

 

常见死锁解决方法：采用行锁(ROWLOCK)，不锁表

UPDATE A WITH (ROWLOCK)
SET [Status] = @Status
WHERE 
    SN = @FSN 
    AND Station = @Station