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:

  • method 1

/*--
-- ,,
-- ,,
-- ,,,
-- 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 @[email protected]@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 @[email protected]@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<[email protected]
                begin
                    select @spid=ID,@= from #t where [email protected]
                    insert #t1 exec('dbcc inputbuffer('[email protected]+')')
                    if @='' exec('kill '[email protected])
                    set @[email protected]+1
                end
            end
        else
            while @i<[email protected]
            begin
                select @s='dbcc inputbuffer('+cast(ID as varchar)+')' from #t where [email protected]
                insert #t1 exec(@s)
                set @[email protected]+1
            end
        select a.*,SQL=b.EventInfo
        from #t a join #t1 b on a.id=b.id
    end
go

  • method 2


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.