Fast locating MS SQL Server database deadlock process

Recently in a large-scale project, because data design uses a discrete database design to facilitate requirements change and user-defined process elements, because the user-defined features, data integrity by code constraints become less realistic, can only rely on the relationship between the table constraints, resulting in data operations often produce X (exclusive) lock, Severely affect data performance.

The most straightforward solution to a database deadlock with no better workaround is to kill the database process that generated the deadlock.

If a deadlock occurs, the following code runs after you can see a large amount of deadlock information on the same process (SPID) that can kill a process depending on the situation.

Select P.spid
, P.[dbid]
, Db_name (P.[dbid]) [db_name]
, L.rsc_objid as [ObjID]
, object_name (L.rsc_objid) [object_name]
, L.rsc_indid as Indid
, v.name as [Type]
, U.name as Mode
, x.name as [Status]
, L.req_transactionid [Transaction]
, P.cmd
, P.hostname
, P.program_name
, P.last_batch
, P.open_tran
, l.rsc_text as [Resource]
--, p.*
From Master.dbo.syslockinfo (NOLOCK) L
INNER JOIN master.sys.sysprocesses (NOLOCK) p
On l.req_spid = P.spid
INNER JOIN master.dbo.spt_values (NOLOCK) v
On l.rsc_type = V.number
INNER JOIN master.dbo.spt_values (NOLOCK) x
On l.req_status = X.number
INNER JOIN master.dbo.spt_values (NOLOCK) u
On L.req_mode + 1 = u.number
where
V.type = ' LR '
and X.type = ' LS '
and U.type = ' L '
ORDER BY P.spid

--Kill 91

Fast locating MS SQL Server database deadlock process