Troubleshooting deadlocks in SQL2005

re: Troubleshooting deadlocks in SQL2005

Rachit Sinha — Wed, 14 Dec 2011 07:28:50 GMT

How we can findout that in which Port No, SQL serevr is running??

re: Troubleshooting deadlocks in SQL2005

Lalit — Tue, 08 Mar 2011 15:19:15 GMT

Is there any function on SQL which will break the dead lock situation or any procedure available which will check for any deadlock periodically and break such situation.

Thanks

re: Troubleshooting deadlocks in SQL2005

raan — Thu, 15 May 2008 02:10:18 GMT

Have you ever seen a situation when to processes block on each other, but the resolver does not identify the deadlock? I have that at a customer site. Two SPIDs pointing to each other. Quite static, but I had to kill one manually. This happens every few days. Does that ring a bell?

Thanks

-- Ran

re: Troubleshooting deadlocks in SQL2005

Sunil Agarwal — Sat, 08 Sep 2007 04:41:12 GMT

yes, you are correct. I updated it. Thanks for catching it. I must have been trying different things and pasted the wrong TF output from errorlog

re: Troubleshooting deadlocks in SQL2005

alpha1105omega — Fri, 07 Sep 2007 14:18:51 GMT

can I get your script that resulted in deadlock?

from your 1222 info..

transaction t2 <process id="process698d48"

update t_lock1 set c2 = 11 where c1 = 5 </inputbuf>

transaction t1 <process id="process699978"

update t_lock2 set c2 = 10 where c1 = 1 </inputbuf>

However, i think it should be this update which came from transaction T1..

update t_lock2 set c2 = 11

where c1 = 1

and this update came from transaction T2 (which correclty shown)

update t_lock1 set c2 = 11

where c1 = 5

unless your script actually says for transaction t1, the 2nd update stmt..

update t_lock2 set c2 = 10 where c1 = 1

Then it'd make sense however your example reflected the wrong script for transaction t1 though.

I think my script below closely matches your table and returned correct deadlock info..

CREATE DATABASE deadlocktest

USE deadlocktest

SET NOCOUNT ON

DBCC TRACEON (1222, -1)

IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1

IF OBJECT_ID ('t2') IS NOT NULL DROP TABLE t2

IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

CREATE TABLE t1 (c1 int, c2 int)

DECLARE @x int

SET @x = 1

WHILE (@x <= 10) BEGIN

INSERT INTO t1 VALUES (@x, @x+1)

SET @x = @x + 1

END

CREATE TABLE t2 (c1 int, c2 int)

DECLARE @x int

SET @x = 1

WHILE (@x <= 10) BEGIN

INSERT INTO t2 VALUES (@x, @x+1)

SET @x = @x + 1

END

-- transaction1

begin tran

update t1 set c2 = 10

where c1 = 5

WAITFOR DELAY '00:00:06'

update t2 set c2 = 11

where c1 = 1

--commit

--transaction2

begin tran

update t2 set c2 = 10

where c1 = 1

WAITFOR DELAY '00:00:08'

update t1 set c2 = 11

where c1 = 5

--commit

2007-09-07 03:00:45.98 spid15s deadlock-list

2007-09-07 03:00:45.98 spid15s deadlock victim=process89ab68

2007-09-07 03:00:45.98 spid15s process-list

2007-09-07 03:00:45.98 spid15s process id=process89a988 taskpriority=0 logused=216 waitresource=RID: 10:1:171:0 waittime=11234 ownerId=490528 transactionname=user_transaction lasttranstarted=2007-09-07T03:00:28.687 XDES=0x5af6d48 lockMode=U schedulerid=1 kpid=1508 status=suspended spid=53 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2007-09-07T03:00:28.590 lastbatchcompleted=2007-09-07T03:00:19.607 clientapp=Microsoft SQL Server Management Studio - Query hostname=host-06 hostpid=2876 loginname=host\bill isolationlevel=read committed (2) xactid=490528 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2007-09-07 03:00:45.98 spid15s executionStack

2007-09-07 03:00:45.98 spid15s frame procname=adhoc line=9 stmtstart=38 sqlhandle=0x0200000032d6641a604886e839c75eb498d42358c5c6911a

2007-09-07 03:00:45.98 spid15s UPDATE [t2] set [c2] = @1 WHERE [c1]=@2

2007-09-07 03:00:45.98 spid15s frame procname=adhoc line=9 stmtstart=168 sqlhandle=0x020000005e0c7e1c8cddc9f43edb54fd4f262e857aea4175

2007-09-07 03:00:45.98 spid15s update t2 set c2 = 11

2007-09-07 03:00:45.98 spid15s where c1 = 1

2007-09-07 03:00:45.98 spid15s inputbuf

2007-09-07 03:00:45.98 spid15s begin tran

2007-09-07 03:00:45.98 spid15s update t1 set c2 = 10

2007-09-07 03:00:45.98 spid15s where c1 = 5

2007-09-07 03:00:45.98 spid15s WAITFOR DELAY '00:00:06'

2007-09-07 03:00:45.98 spid15s update t2 set c2 = 11

2007-09-07 03:00:45.98 spid15s where c1 = 1

2007-09-07 03:00:45.98 spid15s process id=process89ab68 taskpriority=0 logused=216 waitresource=RID: 10:1:169:4 waittime=4578 ownerId=490536 transactionname=user_transaction lasttranstarted=2007-09-07T03:00:33.390 XDES=0x5af72e8 lockMode=U schedulerid=1 kpid=5528 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2007-09-07T03:00:33.390 lastbatchcompleted=2007-09-07T03:00:22.437 clientapp=Microsoft SQL Server Management Studio - Query hostname=host-06 hostpid=2876 loginname=host\bill isolationlevel=read committed (2) xactid=490536 currentdb=10 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2007-09-07 03:00:45.98 spid15s executionStack

2007-09-07 03:00:45.98 spid15s frame procname=adhoc line=9 stmtstart=38 sqlhandle=0x02000000c78ec102da3fe6a72a422a4f5672cacbea7eb738

2007-09-07 03:00:45.98 spid15s UPDATE [t1] set [c2] = @1 WHERE [c1]=@2

2007-09-07 03:00:45.98 spid15s frame procname=adhoc line=9 stmtstart=168 sqlhandle=0x020000000493c6023c8fe018ebf64eb439f353703a6c340b

2007-09-07 03:00:45.98 spid15s update t1 set c2 = 11

2007-09-07 03:00:45.98 spid15s where c1 = 5

2007-09-07 03:00:45.98 spid15s inputbuf

2007-09-07 03:00:45.98 spid15s begin tran

2007-09-07 03:00:45.98 spid15s update t2 set c2 = 10

2007-09-07 03:00:45.98 spid15s where c1 = 1

2007-09-07 03:00:45.98 spid15s WAITFOR DELAY '00:00:08'

2007-09-07 03:00:45.98 spid15s update t1 set c2 = 11

2007-09-07 03:00:45.98 spid15s where c1 = 5

2007-09-07 03:00:45.98 spid15s resource-list

2007-09-07 03:00:45.98 spid15s ridlock fileid=1 pageid=171 dbid=10 objectname=deadlocktest.dbo.t2 id=lock3ca4480 mode=X associatedObjectId=72057594038583296

2007-09-07 03:00:45.98 spid15s owner-list

2007-09-07 03:00:45.98 spid15s owner id=process89ab68 mode=X

2007-09-07 03:00:45.98 spid15s waiter-list

2007-09-07 03:00:45.98 spid15s waiter id=process89a988 mode=U requestType=wait

2007-09-07 03:00:45.98 spid15s ridlock fileid=1 pageid=169 dbid=10 objectname=deadlocktest.dbo.t1 id=lock3ca4680 mode=X associatedObjectId=72057594038517760

2007-09-07 03:00:45.98 spid15s owner-list

2007-09-07 03:00:45.98 spid15s owner id=process89a988 mode=X

2007-09-07 03:00:45.98 spid15s waiter-list

2007-09-07 03:00:45.98 spid15s waiter id=process89ab68 mode=U requestType=wait

re: Troubleshooting deadlocks in SQL2005

Sunil Agarwal — Thu, 06 Sep 2007 23:51:31 GMT

Can you please send me the deadlock graph with TF-1222 and also the schema of the two tables. Based on the above information, T1 should just cause blocking but NOT deadlock.

re: Troubleshooting deadlocks in SQL2005

alpha1105omega — Thu, 06 Sep 2007 14:01:31 GMT

Hi, Sunil

So it seems like these two updates involved in the deadlock..they all came from Transaction T2

update t_lock2 set c2 = 10

where c1 = 1

update t_lock1 set c2 = 11

where c1 = 5

However, i think it should be this update which came from transaction T1

update t_lock2 set c2 = 11

where c1 = 1

Thank you,

Bill

Troubleshooting deadlocks in SQL Server 2005

SQL Server Engine Tips — Tue, 13 Jun 2006 01:27:08 GMT

SQL Server 2005 adds new capabilities to troubleshoot deadlocks. I talked about the new trace flag #1222...

re: Troubleshooting deadlocks in SQL2005

Alex Sibilev — Sat, 10 Jun 2006 21:56:49 GMT

Thank you for the response.
The problem I have is that the second process involved in deadlock is a replication procedure and when it gets stuck - replication latency gets way to big with undistributed commands queue growing very quickly which leads to even to more issues.

I have posted this quesion in usergroup http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.sqlserver.fulltext&mid=e8d913a5-4756-44ed-922a-16dc556beec6 and would appreciate a lot if you can point me in the right direction.

Alex Sibilev
SQL Server MVP

re: Troubleshooting deadlocks in SQL2005

Sunil Agarwal — Sat, 10 Jun 2006 00:23:38 GMT

Info from full-text team>
Deadlock between ft work tasks or between ft work task and user statement is expected in some scenarios. Work task will always be killed and we will retry the task. You can safely ignore this kind of deadlock. Are you seeing server gets stuck?