sqlserver在ssms里面开始事务不提交事务模拟复现死锁和盲查死锁

简书链接:sqlserver在ssms里面开始事务不提交事务模拟复现死锁和盲查死锁
文章字数：373,阅读全文大约需要1分钟
假如我在ssm里面就不进行提交事务
c#执行代码时出现错误：
内容

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\r\nOperation cancelled by user.\r\nThe statement has been terminated

怎么定位呢
使用如下查看sessionid

session_id, blocking_session_id, wait_type, wait_time, wait_resource, text

FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);

但是这个是没用的

sELECT
    request_session_id AS SessionID,
    resource_type AS ResourceType,
    resource_database_id AS DatabaseID,
    resource_associated_entity_id AS AssociatedEntityID,
    request_mode AS LockMode,
    request_status AS LockStatus
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT' OR resource_type = 'PAGE';

强行杀死

告知了原因。

其他命令

DBCC TRACEON (1222, -1);  -- 1222 追踪标记会输出更详细的死锁信息
DBCC TRACEOFF (1222, -1);

SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.dm_exec_requests

SELECT cntr_value AS NumOfDeadLocks ,*  
FROM sys.dm_os_performance_counters   
WHERE object_name  LIKE  '%Locks%'   

最后还是这个语句吧

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

所以对于这种事务锁直接

COMMIT TRAN

不需要写事务名