First, a scalar-valued function, Diglock, is used to recursively detect if there is a lock loop for each session in SQL Server, and if the function eventually returns 1, the lock loop is detected (that is, a deadlock is detected), and if the final return of 0 indicates that no lock loop is detected.
1 CREATE FUNCTION [dbo].[Diglock]2 (3 @spid int,4 @orginSpid int5 )6 RETURNS bit7 as8 BEGIN9 Declare @blockedSpid int=NULL;Ten One Select @blockedSpid=spid fromsysprocesseswhereBlocked<>0 andBlocked=@spid A - if @blockedSpid=@orginSpid - return 1;--deadlock detected. the - if @blockedSpid is not NULL - begin - returnDbo. Diglock (@blockedSpid,@orginSpid); + End - + return 0;--no deadlock detected A END
Then define a view v_deadlock_process, call the function created above, if the query results indicate that there is a deadlock in the current SQL Server
1 CREATE VIEW [dbo].[v_deadlock_process]2 as3 SELECTspid, kpid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, CPU, physical_io, Memusage, login_t IME,4 Last_batch, Ecid, Open_tran, status, SID, Hostname, program_name, hostprocess, cmd, Nt_domain, Nt_u Sername,5 net_address, Net_Library, Loginame, Context_info, sql_handle, Stmt_start , Stmt_end, request_id6 fromSys.sysprocesses asSP17 WHERE(Blocked<> 0) and(dbo. Diglock (spid, spid)= 1)
Query view v_deadlock_process, if there is a deadlock in the current SQL Server, the query is displayed to the record
Select * from [dbo]. [v_deadlock_process]
Shows that session number 53rd is locked for session 54th, and session 54th is locked for session 53rd, so there is a deadlock in SQL Server currently.
You can then use the DBCC INPUTBUFFER statement to pass in the session SPID queried by the above view to find the SQL statement that caused the deadlock
DBCC InputBuffer--input session spid, which queries the SQL statement that is being executed by the conversation, to know what SQL statement was executed by the session in which the deadlock occurred
SQL Server detects deadlock SQL statement