SQL Server detects deadlock SQL statement

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