Analysis of SQL Server deadlock

The server SQL Server database does not automatically generate a trace file as Oracle does when a deadlock occurs. Blocking information can sometimes be seen in [admin]->[Current Activity] (sometimes SQL Server Enterprise Manager will not respond because there are too many locks).

Set Track 1204:

Use MASTER
DBCC Traceon (1204,-1)

Displays the status of all trace flags that are currently enabled:

DBCC Tracestatus (-1)

Cancel Trace 1204:

DBCC Traceoff (1204,-1)

After setting trace 1204, some information is displayed in the database's log file when the SQL Server database deadlock occurs. But that information is hard to understand and needs to be looked at with SQL Server Books Online. According to the PAG lock to find the relevant database table method:

DBCC Traceon (3604)
DBCC PAGE (Db_id,file_id,page_no)
DBCC Traceoff (3604)

Please refer to sqlservercentral.com for more detailed explanation. But from Csdn learned a way to find the cause of the deadlock. I slightly modified, removed the cursor operation and added some hint information, wrote a system stored procedure sp_who_lock.sql. The code is as follows:

if exists (SELECT * from dbo.sysobjects
WHERE id = object_id (N ' [dbo].[ Sp_who_lock] ')
and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [Sp_who_lock]
Go
/********************************************************
To learn and rewrite
Description: View the blocking and deadlock situation in the database
********************************************************/
Use master
Go
CREATE PROCEDURE Sp_who_lock
As
Begin
declare @spid int, @bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
CREATE TABLE #tmp_lock_who (
ID int identity (1,1),
SPID smallint,
BL smallint)
IF @ @ERROR <>0 return @ @ERROR
Insert into #tmp_lock_who (SPID,BL) Select 0, blocked
From (SELECT * from sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sysprocesses
where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sysprocesses where blocked>0
IF @ @ERROR <>0 return @ @ERROR
--Find the number of records in a temporary table
Select @intCountProperties = Count (*), @intCounter = 1
From #tmp_lock_who
IF @ @ERROR <>0 return @ @ERROR
If @intCountProperties =0
Select ' now has no blocking and deadlock information ' as message
--Cycle start
While @intCounter <= @intCountProperties
Begin
--Take the first record
Select @spid = spid, @bl = bl
From #tmp_lock_who where Id = @intCounter
Begin
If @spid =0
Select ' Cause database deadlock is: ' + CAST (@bl as VARCHAR (10))
+ ' process number, whose execution SQL syntax is as follows '
Else
Select ' Process number spid: ' + CAST (@spid as VARCHAR (10)) + ' is '
+ ' Process # spid: ' + CAST (@bl as VARCHAR (10)) + ' blocked, its current process executes the SQL syntax as follows '
DBCC InputBuffer (@bl)
End
--The loop pointer moves down
Set @intCounter = @intCounter + 1
End
drop table #tmp_lock_who
return 0
End

Call directly when needed:

Sp_who_lock

You can find the processes and SQL statements that are causing the deadlock.

SQL Server's own system stored procedures sp_who and sp_lock can also be used to find blocking and deadlocks, but the methods described here are not easy to use. If you want to know the meaning of other tracenum parameters, please see http://www.sqlservercentral.com/article

We can also set the lock timeout (in milliseconds) to shorten the time that deadlocks can affect:

For example:

Use master
SEELCT @ @lock_timeout
Set Lock_timeout 900000
--15 minutes
SEELCT @ @lock_timeout