Often see the problem of deadlocks, it is generally a kill process, but if you don't identify the cause of death lock, the deadlock will often take the operation of finding the deadlock, it can be convenient to solve the deadlock, now Summary of the method of daily solving problems, maybe help everyone
1 / deadlock occurs, by the following syntax, check out the operation of the deadlock
use mastergodeclare @spid int, @ bl intDECLARE s_cur CURSOR FOR select 0, blockedfrom (select * from sysprocesses where blocked> 0) a where not exists (select * from (select * from sysprocesses where blocked> 0) b where a.blocked = spid) union select spid, blocked from sysprocesses where blocked> 0OPEN s_curFETCH NEXT fROM s_cur INTO @ spid, @ blWHILE @@ FETCH_STATUS = 0begin if @spid = 0 select 'deadlock is caused database:' CAST (@bl AS VARCHAR (10)) 'process number, the SQL syntax, the SQL syntax is the following' else select 'process number spid:' cast (@SPID as varchar (10)) 'is' 'process number spid:' cast (@ Bl as varchar (10)) 'blocking, the SQL syntax executed by its current process is the following' DBCC INPUTBUFFER (@BL) Fetch next from s_cur @ spid, @ BlendClose S_Curdeallocate S_CUR
EXEC SP_WHO22 / Find Program / Database, Where is this T_SQL syntax to use 3 / analysis, and solve the problem
EG:
/ * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------- Causes the database deadlock is: 71 process number, the SQL syntax executed is as follows
EventType Parameters Eventinfo ---------------------- ----------------------- ------------------------- Language Event 0 Select * from testinsert test values (1, 2)
(The number of rows affects is 1 line)
DBCC is executed. If DBCC outputs an error message, contact your system administrator. -------------------------------------------------- ---------------------------- The process number SPID: 64 is blocked by the process number SPID: 71, the SQL syntax executed by the current process is as follows
EventType Parameters Eventinfo ---------------------- ----------------------- ------------------------- Language Event 0 Select * from testinsert test values (1, 2) (1 line of the number of rows affected)
DBCC is executed. If DBCC outputs an error message, contact your system administrator. -------------------------------------------------- ---------------------------- The process number SPID: 65 is blocked by the process number SPID: 64, the SQL syntax executed by its current process is as follows
EventType Parameters Eventinfo ---------------------- ----------------------- -------------------------------------------------- ------------------------- Language Event 0 Begin Transelectr * from test with (Holdlock) Waitfor Time '12: 00'select * from testcommit
(The number of rows affects is 1 line)
DBCC is executed. If DBCC outputs an error message, contact your system administrator. -------------------------------------------------- ---------------------------- The process number SPID: 73 is blocked by the process number SPID: 64, the SQL syntax executed in its current process is as follows
EventType Parameters Eventinfo ---------------------- ----------------------- -------------------------------------------------- ------------------------- Language Event 0 Begin Transelectr * from test with (Holdlock) Waitfor Time '12: 00'select * from testcommit
(The number of rows affects is 1 line)
DBCC is executed. If DBCC outputs an error message, contact your system administrator. * /
