January 22, 2013 at 8:40 am
In my production database I'm getting a few deadlocks between updates and selects. Where the selects are being killed off.
I'm trying to work out the best way to eliminate these.
After a bit of research I don't really want to use SNAPSHOT ISOLATION or READ_COMMITTED_SNAPSHOT as this approach would require downtime to alter the database and possibly loads of testing and might be overkill for my needs and unnecessary overload on my temp db.
I also don't really want to go down the (nolock) route.
What would be the best way to approach this? Exclusive locks on the updates?
Any insight into dealing with deadlocks would be greatly appreciated.
January 22, 2013 at 8:44 am
Do you have traceflag 1222 enabled?
If so can you post the deadlock graph from the error log
If not run
DBCC TRACEON (1222, -1)
and then post the graphs once a deadlock has occured.
January 22, 2013 at 8:58 am
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.
In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 9:02 am
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?If so can you post the deadlock graph from the error log
If not run
DBCC TRACEON (1222, -1)
and then post the graphs once a deadlock has occured.
Started profiler now. Will update once ive captured a deadlock
January 22, 2013 at 9:04 am
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.
January 22, 2013 at 9:04 am
bugg (1/22/2013)
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?If so can you post the deadlock graph from the error log
If not run
DBCC TRACEON (1222, -1)
and then post the graphs once a deadlock has occured.
Started profiler now. Will update once ive captured a deadlock
Profiler? That traceflag writes the deadlock graph into the SQL error log, not a trace event.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 9:06 am
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.
Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 9:07 am
bugg (1/22/2013)
...After a bit of research I don't really want to use SNAPSHOT ISOLATION or READ_COMMITTED_SNAPSHOT as this approach would require downtime to alter the database and possibly loads of testing and might be overkill for my needs and unnecessary overload on my temp db.
...
You do not need to take the database offline to enable SNAPSHOT ISOLATION, but you do need to for READ_COMMITTED_SNAPSHOT.
January 22, 2013 at 9:21 am
GilaMonster (1/22/2013)
bugg (1/22/2013)
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?If so can you post the deadlock graph from the error log
If not run
DBCC TRACEON (1222, -1)
and then post the graphs once a deadlock has occured.
Started profiler now. Will update once ive captured a deadlock
Profiler? That traceflag writes the deadlock graph into the SQL error log, not a trace event.
Ahh right, I'm running SQL profiler and have set it up to watch for deadlocks and produce deadlock graphs.
January 22, 2013 at 9:24 am
GilaMonster (1/22/2013)
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.
Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?
Nope I did not realize that! I thought it was specific to the row being updated :unsure:
January 22, 2013 at 9:29 am
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.
Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?
Nope I did not realize that! I thought it was specific to the row being updated :unsure:
Depends how many rows the update effects. If it's one row (and the stats estimate that), SQL would probably go for a row lock anyway. If it's several thousand rows, SQL would probably go for page locks, if you put a row lock hint on, SQL will start with row locks, if the number of locks held pass a threshold, it escalates to table locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 11:50 am
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?If so can you post the deadlock graph from the error log
If not run
DBCC TRACEON (1222, -1)
and then post the graphs once a deadlock has occured.
Hi Anthony
I have this trace on but cant find the deadlock graph anywhere in the errorlog? The deadlock has been recorded but thats all.
January 23, 2013 at 1:40 am
Do you have the deadlock XML in the error log, should be many many lines that looks like this
<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
January 23, 2013 at 2:34 am
anthony.green (1/23/2013)
Do you have the deadlock XML in the error log, should be many many lines that looks like this
<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.
01/23/2013 07:25:31,spid23s,Unknown,waiter id=process27317b4c8 mode=X requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=processdb54c8 mode=S
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057605790367744 dbid=4 objectname=dbo.order indexname=IX_status id=lock3d60c8e80 mode=S associatedObjectId=72057605790367744
01/23/2013 07:25:31,spid23s,Unknown,waiter id=processdb54c8 mode=S requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=process27317b4c8 mode=X
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057602492792832 dbid=4 objectname=dbo.order indexname=PK_order id=lock3d27aec80 mode=X associatedObjectId=72057602492792832
01/23/2013 07:25:31,spid23s,Unknown,resource-list
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000880e2612bdc1c85889b28ed1a83337436c23418e
01/23/2013 07:25:31,spid23s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [order] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [order]%%=%%@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=process27317b4c8 taskpriority=0 logused=292 waitresource=KEY: 9:72057605790367744 (2e001d1b37ce) waittime=1840 ownerId=18323630766 transactionname=UPDATE lasttranstarted=2013-01-23T07:25:29.427 XDES=0xe4e2b970 lockMode=X schedulerid=8 kpid=2092 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-23T07:25:29.420 lastbatchcompleted=2013-01-23T07:25:29.420 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630766 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,SELECT count(rsn) As Count FROM order WHERE status<3 AND shipopt <99 AND dateadded <= '2013-01-23 13:00:59'
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000712b952eb475b9b249be45c1513acca8b2b31ed1
01/23/2013 07:25:31,spid23s,Unknown,(@1 tinyint<c/>@2 tinyint<c/>@3 tinyint<c/>@4 varchar(8000))SELECT COUNT([rsn]) [Count] FROM [order] WHERE [status]<@1 AND [shipopt]<@3 AND [dateadded]<=@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000007c853201340a85cf70825b9c171de622351e36b1
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=processdb54c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (2c00849cfb3c) waittime=1840 ownerId=18323630729 transactionname=SELECT lasttranstarted=2013-01-23T07:25:29.423 XDES=0x1c384fb80 lockMode=S schedulerid=7 kpid=8128 status=suspended spid=51 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-01-23T07:25:29.423 lastbatchcompleted=2013-01-23T07:25:29.280 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630729 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,process-list
01/23/2013 07:25:31,spid23s,Unknown,deadlock victim=processdb54c8
01/23/2013 07:25:31,spid23s,Unknown,deadlock-list
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy:(0x000000035B1E3770) Value:0xce952bc0 Cost:(0/0)
01/23/2013 07:25:31,spid5s,Unknown,Victim Resource Owner:
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000E4E2B970 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy:(0x000000024AD44538) Value:0xbd6264c0 Cost:(0/292)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: No Event:
01/23/2013 07:25:31,spid5s,Unknown,SPID: 51 ECID: 4 Statement Type: SELECT Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x00000001BF286480 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:4 XactLockInfo: 0x00000001C384FBC0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 2:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057605790367744 (2e001d1b37ce) CleanCnt:2 Mode:S Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:2
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy:(0x000000035B1E3770) Value:0xce952bc0 Cost:(0/0)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: Language Event: update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x000000008164B2C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000E4E2B9B0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 1:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057602492792832 (2c00849cfb3c) CleanCnt:2 Mode:X Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:1
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,Wait-for graph
01/23/2013 07:25:31,spid5s,Unknown,Deadlock encountered .... Printing deadlock information
January 23, 2013 at 6:18 am
bugg (1/23/2013)
Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.
That's a deadlock graph. 2 actually, since you enabled both traceflags 1204 and 1222 resulting an a messed up combination of old style of old and new deadlock graphs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply