Deadlocks - Help

[topub]

We have a DB that is heavly hit. Recently, we started to notice deadlocks on this machine. Now its up to one deadlock every 5 minutes.
For the most part, deadlock occurs between a select query (either inside a Stored Proc or just straight select query) and an update statement.

Some DB info:

Most of the data is stored in just one table with 120 columns. [don't ask me why...] All necessary columns are indexed (about 16 columns).

One example of deadlock is between:

select query returns exactly 1000 records (select top 1000)
Update statement updates exactly one row (update.... where ID = 1)

Any ideas are greatly appreciated.

Following is the Deadlock Log:

Deadlock encountered .... Printing deadlock information
2007-10-03 09:08:53.08 spid3     
2007-10-03 09:08:53.08 spid3     Wait-for graph
2007-10-03 09:08:53.08 spid3     
2007-10-03 09:08:53.08 spid3     Node:1
2007-10-03 09:08:53.08 spid3     PAG: 6:6:84082                 CleanCnt:2 Mode: S Flags: 0x2
2007-10-03 09:08:53.08 spid3      Grant List 1::
2007-10-03 09:08:53.08 spid3        Owner:0x3ecab6a0 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:67 ECID:2
2007-10-03 09:08:53.08 spid3        SPID: 67 ECID: 2 Statement Type: INSERT Line #: 60
2007-10-03 09:08:53.08 spid3        Input Buf: RPC Event: pr_PendingReportListing;1
2007-10-03 09:08:53.08 spid3      Requested By: 
2007-10-03 09:08:53.08 spid3        ResType:LockOwner Stype:'OR' Mode: IX SPID:323 ECID:0 Ec:(0x55BEF550) Value:0x578c46c0 Cost:(0/268)
2007-10-03 09:08:53.08 spid3     
2007-10-03 09:08:53.08 spid3     Node:2
2007-10-03 09:08:53.08 spid3     PAG: 6:6:302                   CleanCnt:2 Mode: IX Flags: 0x2
2007-10-03 09:08:53.08 spid3      Grant List 0::
2007-10-03 09:08:53.08 spid3        Owner:0x2b343540 Mode: IX       Flg:0x0 Ref:1 Life:02000000 SPID:323 ECID:0
2007-10-03 09:08:53.08 spid3        SPID: 323 ECID: 0 Statement Type: UPDATE Line #: 19
2007-10-03 09:08:53.08 spid3        Input Buf: RPC Event: sp_executesql;1
2007-10-03 09:08:53.08 spid3      Requested By: 
2007-10-03 09:08:53.08 spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:67 ECID:2 Ec:(0x2FFD40C0) Value:0x72be5020 Cost:(0/0)
2007-10-03 09:08:53.08 spid3     Victim Resource Owner:
2007-10-03 09:08:53.08 spid3      ResType:LockOwner Stype:'OR' Mode: S SPID:67 ECID:2 Ec:(0x2FFD40C0) Value:0x72be5020 Cost:(0/0)
2007-10-03 09:08:53.08 spid67    Error: 1205, Severity: 13, State: 61
2007-10-03 09:08:53.08 spid67    Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
2007-10-03 09:09:40.63 spid3     
Deadlock encountered .... Printing deadlock information
2007-10-03 08:42:37.22 spid3     
2007-10-03 08:42:37.22 spid3     Wait-for graph
2007-10-03 08:42:37.22 spid3     
2007-10-03 08:42:37.22 spid3     Node:1
2007-10-03 08:42:37.22 spid3     PAG: 6:6:84817                 CleanCnt:2 Mode: S Flags: 0x2
2007-10-03 08:42:37.22 spid3      Grant List 1::
2007-10-03 08:42:37.22 spid3        Owner:0x7bda92a0 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:113 ECID:4
2007-10-03 08:42:37.22 spid3        SPID: 113 ECID: 4 Statement Type: SELECT Line #: 1
2007-10-03 08:42:37.22 spid3        Input Buf: RPC Event: dbo.pr_SearchResults;1
2007-10-03 08:42:37.22 spid3      Requested By: 
2007-10-03 08:42:37.22 spid3        ResType:LockOwner Stype:'OR' Mode: IX SPID:100 ECID:0 Ec:(0x35FF7598) Value:0x69484220 Cost:(0/33C)
2007-10-03 08:42:37.22 spid3     
2007-10-03 08:42:37.22 spid3     Node:2
2007-10-03 08:42:37.22 spid3     PAG: 6:5:84948                 CleanCnt:2 Mode: IX Flags: 0x2
2007-10-03 08:42:37.22 spid3      Grant List 0::
2007-10-03 08:42:37.22 spid3        Owner:0x69484420 Mode: IX       Flg:0x0 Ref:1 Life:02000000 SPID:100 ECID:0
2007-10-03 08:42:37.22 spid3        SPID: 100 ECID: 0 Statement Type: UPDATE Line #: 109
2007-10-03 08:42:37.22 spid3        Input Buf: RPC Event: dbo.pr_RoundRobinAssignment;1
2007-10-03 08:42:37.22 spid3      Requested By: 
2007-10-03 08:42:37.22 spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:113 ECID:4 Ec:(0x3106C0C0) Value:0x2ab4d160 Cost:(0/0)
2007-10-03 08:42:37.22 spid3     Victim Resource Owner:
2007-10-03 08:42:37.22 spid3      ResType:LockOwner Stype:'OR' Mode: S SPID:113 ECID:4 Ec:(0x3106C0C0) Value:0x2ab4d160 Cost:(0/0)
2007-10-03 08:42:37.22 spid113   Error: 1205, Severity: 13, State: 61
2007-10-03 08:42:37.22 spid113   Transaction (Process ID 113) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
2007-10-03 08:50:28.47 spid3

thanks,

 

[onpnt]

Of course the hard part is finding the deadlocks. you've already done that. Are these SP's new to the instance? Is it possible to do a (NOLOCK) on the SELECT's in them to prevent the deadlock?

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[topub]

Thanks for the response.

I've tried "READPAST" and "NOLOCK" on some select statements and it seems to have prevented some deadlocks. But I still have a lot of deadlocks happenning with other select statements ( with update). I am not sure if I could just keep adding this LOCK-hints to all the select statements or not.

As far as I know, NOLOCK reads dirty pages and READPAST skips locked records, both seem like a work-around rather than a long term solution.

Is there a long term solution "as such" for deadlocks

thanks,

 

[evilDBA]

I cant help without looking at the souce code of stored procedures.

 

[onpnt]

There is no long term solution other than you getting the system corrected. There is no special "This is how to fix it" sense we have no idea what is going on from the table level and the transactions. You've done the work to find the bad apples causing the deadlocks and now you just need to fix those one by one.

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[SQLDenis]

can you show the code of

pr_SearchResults
and
pr_RoundRobinAssignment

also what is the fragmentation level of the tables in question
is there a trigger on the table that updates the same table?

set the lock priority to LOW on the select proc so that at least the update proc succeeds




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

one more thing, are the stats up to date?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Antzz]

Hi Guys,
I am trying to resolve some deadlock issues and trying to figure out what resource the following key might indicate:
Message
KEY: 8:72057594155368448 (0a00d91b10ff) CleanCnt:2 Mode:X Flags: 0x0

Is there any utility to figure out [KEY: 8:72057594155368448]? I know the no. 8 before the ":" represents the database but able to figure out the long no. after that. I assume it is some sort of identifier for an index?

R

 

[onpnt]

Refer to

http://www.tek-tips.com/viewthread.cfm?qid=1416340&page=1

Not to post but for the answer to what 8:72057594155368448 is

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[Antzz]

I already tried the Object_name sql but it seems like the number object identifiers are only integers and the above number gives me a run-time error saying this is not a valid int.(overflow).

 

[Ron06]

First find out uisng profile tsql statement causing the deadlock. You can use locks option in profiler. Tell me how it goes

 

[SQLDenis]

run trace flag 1204, this should capture more information (proc name and SQL statement) this will be visible in the sql logs

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions