查看当前死锁
SELECT request_session_id spid, OBJECT_NAME( resource_associated_entity_id ) tableName FROM sys.dm_tran_locks WHERE resource_type = ‘OBJECT‘
查看死锁进程
SELECT * FROM sys.dm_exec_connections; SELECT * FROM sys.dm_exec_sessions;
SELECT spid , blocked , DB_NAME (sp.dbid) AS DBName , program_name , waitresource , 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 WHERE spid > 50 ORDER BY blocked DESC, DB_NAME (sp.dbid) ASC, a.[text]
杀掉死锁进程
kill spid