October 11, 2007 at 8:57 am
Our application team suddenly getting lot of deadlocks.Nothing has changed at database side or application side.We are getting deadlocks for every fifteen minutes.
Node:1
2007-10-11 09:46:57.46 spid2 PAG: 5:1:6064201 CleanCnt:2 Mode: IX Flags: 0x2
2007-10-11 09:46:57.46 spid2 Grant List 3::
2007-10-11 09:46:57.46 spid2 Owner:0x579548c0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:66 ECID:0
2007-10-11 09:46:57.46 spid2 SPID: 66 ECID: 0 Statement Type: INSERT Line #: 300
2007-10-11 09:46:57.46 spid2 Input Buf: RPC Event: 1
2007-10-11 09:46:57.46 spid2 Requested By:
2007-10-11 09:46:57.46 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:2 Ec:(0x6C3C80C0) Value:0x62651ac0 Cost:(0/0)
2007-10-11 09:46:57.46 spid2
2007-10-11 09:46:57.46 spid2 Node:2
2007-10-11 09:46:57.46 spid2 PAG: 5:1:5877474 CleanCnt:2 Mode: S Flags: 0x2
2007-10-11 09:46:57.46 spid2 Grant List 0::
2007-10-11 09:46:57.46 spid2 Owner:0x60bfde80 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:59 ECID:2
2007-10-11 09:46:57.46 spid2 SPID: 59 ECID: 2 Statement Type: SELECT Line #: 15
2007-10-11 09:46:57.46 spid2 Input Buf: RPC Event: ;1
2007-10-11 09:46:57.46 spid2 Requested By:
2007-10-11 09:46:57.46 spid2 ResType:LockOwner Stype:'OR' Mode: IX SPID:66 ECID:0 Ec:(0x5E807598) Value:0x5ca8b1e0 Cost:(0/614)
2007-10-11 09:46:57.46 spid2 Victim Resource Owner:
2007-10-11 09:46:57.46 spid2 ResType:LockOwner Stype:'OR' Mode: S SPID:59 ECID:2 Ec:(0x6C3C80C0) Value:0x62651ac0 Cost:(0/0)
Deadlock is occuring between insert(simple insert) and select(does select count(*) from same table where we are doing insert).Could anybody know why deadlock issue suddenly popped up without any changes in code or configuration and how can we resolve this?
Thanks,
SR
October 11, 2007 at 10:11 am
My guess is that the table has grown enough to force the SELECT COUNT(*) to become a full scan. This escalates to a schema lock and the INSERT will be deadlocked.
If the SELECT COUNT(*) is not in a transaction, use the WITH (NOLOCK) locking hint to allow the inserts to happen without a deadlock issue.
October 11, 2007 at 11:02 am
Thanks for quick response Kevin.Nolock is not an option for us as its not acceptable from business perspective.
Thanks,
SR
October 11, 2007 at 1:06 pm
I would try changing the count(*) to a count(1). It is marganally faster. Also do you have any where conditions, groupings etc on the count? If so they should be optimized as much as possible.
And last but definatly not least do you have anything in the same transaction as either the insert or the select? If so try to get them into their own transaction .. or at least make the transactions as small as possible.
Unfortunatly its impossible to completly eliminate deadlocks .. all you can do is reduce them as much as possible.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply