April 29, 2015 at 12:10 pm
Hello,
deadlock trace in sqlserver log by turning on T1222 flag. I see that the lock is happening on a IX lock vs S lock, Please provide help in analyzing the deadlock.
from what i see the deadlock occurred over the select vs update,
spid=91 process id=process24080af868 taskpriority=0 logused=576 waitresource=PAGE: 11:1:10088721
spid=203 process id=process1fe9625498 taskpriority=0 logused=0 waitresource=PAGE: 11:1:8215776
both are accessing different pageid's but still end up in deadlock,
please can anyone let me know what i am missing.
Thanks for the help
04/29/2015 09:12:35,spid28s,Unknown,waiter id=process24080af868 mode=IX requestType=wait
04/29/2015 09:12:35,spid28s,Unknown,waiter-list
04/29/2015 09:12:35,spid28s,Unknown,owner id=process1fe9625498 mode=S
04/29/2015 09:12:35,spid28s,Unknown,owner-list
04/29/2015 09:12:35,spid28s,Unknown,pagelock fileid=1 pageid=10088721 dbid=11 subresource=FULL objectname=EDXDataStore2.tsadm.DOCUMENTJDO id=lock26b8724080 mode=S associatedObjectId=72057606277693440
04/29/2015 09:12:35,spid28s,Unknown,waiter id=process1fe9625498 mode=S requestType=wait
04/29/2015 09:12:35,spid28s,Unknown,waiter-list
04/29/2015 09:12:35,spid28s,Unknown,owner id=process24080af868 mode=IX
04/29/2015 09:12:35,spid28s,Unknown,owner-list
04/29/2015 09:12:35,spid28s,Unknown,pagelock fileid=1 pageid=8215776 dbid=11 subresource=FULL objectname=EDXDataStore2.tsadm.DOCUMENTJDO id=lock2260ad7400 mode=IX associatedObjectId=72057606277693440
04/29/2015 09:12:35,spid28s,Unknown,resource-list
04/29/2015 09:12:35,spid28s,Unknown,(@P0 nvarchar(4000),@P1 datetime,@P2 nvarchar(4000),@P3 int,@P4 nvarchar(4000),@P5 bigint,@P6 int)UPDATE tsadm.DOCUMENTJDO SET ACCEPTCODE = @P0, ACKDATE = @P1, DOCUMENTREFERENCENUMBER = @P2, JDOVERSION = @P3, FUNCTIONALGROUPCONTROLNUMBER997 = @P4 WHERE JDOID = @P5 AND JDOVERSION = @P6
04/29/2015 09:12:35,spid28s,Unknown,inputbuf
04/29/2015 09:12:35,spid28s,Unknown,unknown
04/29/2015 09:12:35,spid28s,Unknown,frame procname=unknown line=1
sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
04/29/2015 09:12:35,spid28s,Unknown,UPDATE tsadm.DOCUMENTJDO SET ACCEPTCODE = @P0, ACKDATE = @P1, DOCUMENTREFERENCENUMBER = @P2, JDOVERSION = @P3, FUNCTIONALGROUPCONTROLNUMBER997 = @P4 WHERE JDOID = @P5 AND JDOVERSION = @P6
04/29/2015 09:12:35,spid28s,Unknown,frame procname=adhoc line=1 stmtstart=196 sqlhandle=0x0200000018145a1d3b4853d10adcdefc93ad0448ca24c2de0000000000000000000000000000000000000000
04/29/2015 09:12:35,spid28s,Unknown,executionStack
04/29/2015 09:12:35,spid28s,Unknown,process id=process24080af868 taskpriority=0 logused=576 waitresource=PAGE: 11:1:10088721 waittime=3473 ownerId=3448523576 transactionname=implicit_transaction lasttranstarted=2015-04-29T09:12:32.393 XDES=0x2bc3454d28 lockMode=IX schedulerid=10 kpid=71972 status=suspended spid=91 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-04-29T09:12:32.393 lastbatchcompleted=2015-04-29T09:12:32.393 lastattention=1900-01-01T00:00:00.393 hostname=usl3prodexapp15 hostpid=0 loginname=tsadm isolationlevel=read committed (2) xactid=3448523576 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
04/29/2015 09:12:35,spid28s,Unknown,(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000))SELECT t0.JDOID, t0.JDOCLASS, t0.JDOVERSION, t0.ACCEPTCODE, t0.ACKDATE, t0.CHARACTERCOUNT, t0.COMMENTS, t0.CREATEDBY, t0.DIRECTION, t0.DOCUMENTREFERENCENUMBER, t0.ERRORS, t0.FUNCTIONALGROUPCONTROLNUMBER, t0.FUNCTIONALGROUPCONTROLNUMBER997, t0.FUNCTIONALGROUPEXTERNALID, t0.FUNCTIONALGROUPINTERNALID, t0.FUNCTIONALGROUPNAME, t0.GROUPID, t0.HASHTOTAL, t0.INDEXED, t0.INTERCHANGE_JDOID, t0.LINECOUNT, t0.MODIFIEDBY, t0.MODIFYDATE, t0.NETWORKID, t0.ORIGIN, t0.PARENT_JDOID, t0.PARTNER, t0.PROCESSDATE, t0.SEGMENTCOUNT, t0.SENTDATE, t0.SETID, t0.STANDARDVERSION, t0.STATUS, t0.SUBSET, t0.TRADINGPARTNERPAIRID, t0.TRANSACTIONSETCONTROLNUMBER, t0.TRANSACTIONSETCONTROLNUMBER997, t0.TURNED, t0.UTILITY, t0.VIEWED FROM tsadm.DOCUMENTJDO t0 WHERE (t0.DOCUMENTREFERENCENUMBER = @P0 AND t0.FUNCTIONALGROUPEXTERNALID = @P1 AND t0.FUNCTIONALGROUPINTERNALID = @P2 AND t0.SETID = @P3 AND t0.DIRECTION = @P4) ORDER BY t0.ACKDATE DESC
04/29/2015 09:12:35,spid28s,Unknown,inputbuf
04/29/2015 09:12:35,spid28s,Unknown,unknown
04/29/2015 09:12:35,spid28s,Unknown,frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
04/29/2015 09:12:35,spid28s,Unknown,SELECT t0.JDOID, t0.JDOCLASS, t0.JDOVERSION, t0.ACCEPTCODE, t0.ACKDATE, t0.CHARACTERCOUNT, t0.COMMENTS, t0.CREATEDBY, t0.DIRECTION, t0.DOCUMENTREFERENCENUMBER, t0.ERRORS, t0.FUNCTIONALGROUPCONTROLNUMBER, t0.FUNCTIONALGROUPCONTROLNUMBER997, t0.FUNCTIONALGROUPEXTERNALID, t0.FUNCTIONALGROUPINTERNALID, t0.FUNCTIONALGROUPNAME, t0.GROUPID, t0.HASHTOTAL, t0.INDEXED, t0.INTERCHANGE_JDOID, t0.LINECOUNT, t0.MODIFIEDBY, t0.MODIFYDATE, t0.NETWORKID, t0.ORIGIN, t0.PARENT_JDOID, t0.PARTNER, t0.PROCESSDATE, t0.SEGMENTCOUNT, t0.SENTDATE, t0.SETID, t0.STANDARDVERSION, t0.STATUS, t0.SUBSET, t0.TRADINGPARTNERPAIRID, t0.TRANSACTIONSETCONTROLNUMBER, t0.TRANSACTIONSETCONTROLNUMBER997, t0.TURNED, t0.UTILITY, t0.VIEWED FROM tsadm.DOCUMENTJDO t0 WHERE (t0.DOCUMENTREFERENCENUMBER = @P0 AND t0.FUNCTIONALGROUPEXTERNALID = @P1 AND t0.FUNCTIONALGROUPINTERNALID = @P2 AND t0.SETID = @P3 AND t0.DIRECTION = @P4) ORDER BY t0.ACKDATE DESC
04/29/2015 09:12:35,spid28s,Unknown,frame procname=adhoc line=1 stmtstart=192
sqlhandle=0x020000001d9e3017be0b40902a95cbf3b309f4b905963abc0000000000000000000000000000000000000000
04/29/2015 09:12:35,spid28s,Unknown,executionStack
04/29/2015 09:12:35,spid28s,Unknown,process id=process1fe9625498 taskpriority=0 logused=0 waitresource=PAGE: 11:1:8215776 waittime=3403 ownerId=3448521445 transactionname=DECLARE CURSOR lasttranstarted=2015-04-29T09:12:30.437 XDES=0x1d925b1b30 lockMode=S schedulerid=20 kpid=74456 status=suspended spid=203 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2015-04-29T09:12:30.437 lastbatchcompleted=2015-04-29T09:12:30.433 lastattention=1900-01-01T00:00:00.433 hostname=usl3prodexapp16 hostpid=0 loginname=tsadm isolationlevel=read committed (2) xactid=3448521445 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/29/2015 09:12:35,spid28s,Unknown,process-list
04/29/2015 09:12:35,spid28s,Unknown,deadlock victim=process1fe9625498
04/29/2015 09:12:35,spid28s,Unknown,deadlock-list
April 29, 2015 at 12:46 pm
You're on the right track.
Both processes have requested locks on both pages, but in different orders.
The SELECT first requested an S lock on page 10088721, and that request was granted.
Meanwhile, the UPDATE requested an IX lock on page 8215776, and that request was also granted.
Now we get to the part that results in a deadlock.
With those locks still held, the SELECT then requested another S lock, this time one on page 8215776. It had to wait, since the UPDATE already had a granted IX lock on that page, and S and IX locks are incompatible.
The UPDATE also requested another lock, but now on page 10088721. It also had to wait, since the SELECT already had a granted S lock on that page, and again, S and IX locks are incompatible.
Now each process was waiting for the other to release a lock before moving forward. At some point shortly thereafter, the lock monitor thread woke up, detected the deadlock, and chose the SELECT as the victim.
To mitigate this, you'll want to 1) make sure your processes are not holding locks longer than they have to, and 2) as much as possible make sure that locks on a given object are taken out in the same order by any code that accesses them.
On the point about holding locks longer than you need to, the first thing that jumps out here is that the SELECT is part of a DECLARE CURSOR. I'd definitely investigate whether you really need to use a cursor, as those tend to cause more problems than they solve.
On the point about order of locks, in this case, if the locks had been taken out on the pages in the same order by both processes, then one would have blocked the other for a time, but there wouldn't have been a deadlock. Checking execution plans for the queries involved will help with understanding what changes could be made to help here.
Without more details about the data and code involved, that's about as much help as I can offer.
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/ is a nice article by Gail Shaw that introduces some common deadlock patterns and some methods for addressing them, if you want to look a little more at this.
I hope this helps!
April 29, 2015 at 2:18 pm
Thank you very much! that is a start for me- I will validate both the points mentioned.!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply