A deadlock is an inevitable situation in the RDBMS architecture and very common in high-volume OLTP environments. A deadlock occurs when two (or more) processes attempt to access a resource that the other process holds a lock on. Because each process has a request for another resource, neither process can be completed. When a deadlock is detected, SQL Server rolls back the command that has the least processing time and returns error message 1205 to the client application. This error is not fatal and may not cause the batch to be terminated.
Here is the simple script to handle deadlock monitoring using T-SQL code
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Deadlock_Monitor.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
For another way, there is two method with script to handle deadlock in SQL Server, such as:
/*--
-- ,,
-- ,,
-- ,,,
-- 2004.4--
--
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --,1 , 0
@show_spid_if_nolock bit=1 --,,1 ,0
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),,
ID=spid,ID=kpid,ID=blocked,ID=dbid,
=db_name(dbid),ID=uid,=loginame,CPU=cpu,
=login_time,=open_tran, =status,
=hostname,=program_name,ID=hostprocess,
=nt_domain,=net_address
into #t from(
select ='',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|__>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select ='',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@ varchar(10)
while @i<=@count
begin
select @spid=ID,@= from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @='' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,SQL=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
SELECT
request_session_id as Spid,
Coalesce(s.name + '.' + o.name + isnull('.' + i.name,''),
s2.name + '.' + o2.name,
db.name) AS Object,
l.resource_type as Type,
request_mode as Mode,
request_status as Status
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
LEFT JOIN sys.objects o
ON p.object_id = o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN sys.objects o2
ON l.resource_associated_entity_id = o2.object_id
LEFT JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
LEFT JOIN sys.databases db
ON l.resource_database_id = db.database_id
WHERE resource_database_id = DB_ID()
ORDER BY Spid, Object, CASE l.resource_type
When 'database' Then 1
when 'object' then 2
when 'page' then 3
when 'key' then 4
Else 5 end
SQL Server 2012 Hosting is Here with FREE Trial!
Start your FREE Trial SQL 2012 Hosting hosting with us and get professional web hosting support! If the service does not meet your expectations simply cancel before the end of the free trial period. Risk FREE! Why wait longer?
You can start from as low as $5.00/month to start hosting your SQL 2012 on our environment. If you do not have a domain name, please do not worry as we will give you one FREE domain name (worth $14.99/year) if you register for any of our hosting plans for 12 months service(*). We will do our best to help you create your first web presence on the internet and we will continuously support the growth of your business.