July 5, 2009 at 1:37 am
I'm using SQL Server 2005. I have used sql trace flag 1204 to trace the deadlock. Based on the trace, I have encountered there are 2 queries that cause the deadlock.
1. Simple insert statement
INSERT INTO TABLE1 (FIELD1, FIELD2, FIELD3) VALUES (@INTVAL, @NTEXTVAL, @CHARVAL)
- Provide IX lock
2. Simple select statement with READPAST
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 WITH READPAST WHERE FIELD1 > @VALUE
- Provide S lock
Could anyone please help me on how to solve this deadlock issue? I'm wondering this deadlock happen is it because of FIELD2 is an ntext field?
If I'm using NOLOCK in the SELECT statement, would it be possible that I may miss to retrieve FIELD2 info since its ntext field that keep data in separate page?
July 5, 2009 at 5:29 am
That shouldn't be enough to deadlock.
Can you switch traceflag 1222 on, instead of 1204. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
July 6, 2009 at 9:32 am
Dear GilaMonster,
Based on your suggestion, I have turn on 1222 trace flag. The following is the result I received.
spid5s Node:1
spid5s PAGE: 17:1:102945 CleanCnt:3 Mode:S Flags: 0x2
spid5s Grant List 0:
spid5s Owner:0x0577BD40 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0 XactLockInfo: 0x069BEFB4
spid5s SPID: 55 ECID: 0 Statement Type: SELECT Line #: 5
spid5s Input Buf: RPC Event: Proc [Database Id = 17 Object Id = 1807761943]
spid5s Requested By:
spid5s ResType:LockOwner Stype:'OR'Xdes:0x115A0250 Mode: IX SPID:53 BatchID:0 ECID:0 TaskProxy:(0x05E08378) Value:0x577b680 Cost:(0/3112)
spid5s
spid5s Node:2
spid5s PAGE: 17:1:102971 CleanCnt:2 Mode:IX Flags: 0x2
spid5s Grant List 0:
spid5s Owner:0x0577BEE0 Mode: IX Flg:0x0 Ref:0 Life:04000000 SPID:53 ECID:0 XactLockInfo: 0x115A0274
spid5s SPID: 53 ECID: 0 Statement Type: INSERT Line #: 6
spid5s Input Buf: RPC Event: Proc [Database Id = 17 Object Id = 1775761829]
spid5s Requested By:
spid5s ResType:LockOwner Stype:'OR'Xdes:0x069BEF90 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x069C6378) Value:0x577be00 Cost:(0/0)
spid5s
spid5s Victim Resource Owner:
spid5s ResType:LockOwner Stype:'OR'Xdes:0x069BEF90 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x069C6378) Value:0x577be00 Cost:(0/0)
spid19s deadlock-list
spid19s deadlock victim=process2e191f8
spid19s process-list
spid19s process id=processd3ae38 taskpriority=0 logused=3112 waitresource=PAGE: 17:1:102945 waittime=2511 ownerId=87000 transactionname=INSERT lasttranstarted=2009-07-06T23:04:25.410 XDES=0x115a0250 lockMode=IX schedulerid=1 kpid=3488 status=suspended spid=53 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-07-06T23:04:25.410 lastbatchcompleted=2009-07-06T23:04:25.410 clientapp=.Net SqlClient Data Provider hostname=N0479 hostpid=0 loginname=AppUser isolationlevel=read committed (2) xactid=87000 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
spid19s executionStack
spid19s frame procname=dmIRULES_TEST.dbo.ESY_INS_EUTMPCAS line=6 stmtstart=380 sqlhandle=0x03001100a5f9d76944990001f49900000100000000000000
spid19s INSERT INTO EUTMPCAS (
spid19s TCAACTID,TCAQUEID,TCAMSG,TCATRXID,TCAMODID,TCAVERID,TCATRUHL,TCASTAT,TCASTDT,TCAIFLD
spid19s )
spid19s VALUES(
spid19s @ActID, @QID, @STRMSG, @TRXID, @MODELID, @VERID,@TRUEHL,'P', GETDATE(),@IFIELD
spid19s )
spid19s inputbuf
spid19s Proc [Database Id = 17 Object Id = 1775761829]
spid19s process id=process2e191f8 taskpriority=0 logused=0 waitresource=PAGE: 17:1:102971 waittime=2527 ownerId=86988 transactionname=SELECT lasttranstarted=2009-07-06T23:04:25.410 XDES=0x69bef90 lockMode=S schedulerid=2 kpid=4056 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2009-07-06T23:04:25.410 lastbatchcompleted=2009-07-06T23:04:25.410 clientapp=.Net SqlClient Data Provider hostname=N0479 hostpid=0 loginname=AppUser isolationlevel=read committed (2) xactid=86988 currentdb=17 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
spid19s executionStack
spid19s frame procname=dmIRULES_TEST.dbo.ESY_GetCaseQueue line=5 stmtstart=132 sqlhandle=0x030011001742c06b54bd0a01f49900000100000000000000
spid19s SELECT A.TCAID,TCAACTID,TCAQUEID,TCAMSG,TCATRXID,TCAMODID,TCAVERID,TCATRUHL,TCAIFLD
spid19s FROM EUTMPCAS A WITH(READPAST)
spid19s WHERE A.TCAID > (SELECT PSSVAL FROM EUPRMSSV WITH(READPAST) WHERE PSSVNM = 'CasePrevID')
spid19s inputbuf
spid19s Proc [Database Id = 17 Object Id = 1807761943]
spid19s resource-list
spid19s pagelock fileid=1 pageid=102971 dbid=17 objectname=dmIRULES_TEST.dbo.EUTMPCAS id=lock5714e40 mode=IX associatedObjectId=293321807822848
spid19s owner-list
spid19s owner id=processd3ae38 mode=IX
spid19s waiter-list
spid19s waiter id=process2e191f8 mode=S requestType=wait
spid19s pagelock fileid=1 pageid=102945 dbid=17 objectname=dmIRULES_TEST.dbo.EUTMPCAS id=lock5716380 mode=S associatedObjectId=293321807822848
spid19s owner-list
spid19s owner id=process2e191f8 mode=S
spid19s waiter-list
spid19s waiter id=processd3ae38 mode=IX requestType=wait
Please advice anything I can do to resolve the deadlock. Thanks
July 7, 2009 at 5:29 am
The graph in a Simplified way
---------------------------------
Node:1
---------
Owned By :
------------
Owner SPID: 55
Owner:0x0577BD40
Life:00000000 <- 0 seconds
XactLockInfo: 0x069BEFB4
PAGE: 17:1:102945 <-- databse 17 file 1 page 102945
Statement Type: SELECT
Mode: S
Object Id = 1807761943
Requested By:
-----------------
SPID:53
Cost 0/3112 <-- some cost
Mode: IX Intent exclusive lock.
Node:2
------
PAGE: 17:1:102971 [this page might be in the same extent even though the difference of 2 pages is more than 16 .But I am not very sure and a bit confused .We need to do DBCC Page]
Owner:0x0577BEE0
Life:04000000 <-- much more Undo than SPID 55.So looks like this SPID started first .
XactLockInfo: 0x115A0274
Mode:IX
SPID:53
Statement Type: INSERT
Object Id = 1775761829
Requested By:
SPID:55
Cost0/0 (SELECT PSSVAL FROM EUPRMSSV WITH(READPAST) WHERE PSSVNM = 'CasePrevID')
pageid=102971
dbid=17
objectname=dmIRULES_TEST.dbo.EUTMPCAS
I am not repeating anything as you might be aware of everything .
As far as readpast is concernred MS boasted initially that if we use readpast then we will be able to see all the rows that are not locked by other transactions .This means that there should not be the Page lock if we use READPAST .But the reality is that one spid takes a lock on the last page where as other SPID waits for the lock on that last page .Microsoft says "Under most circumstances, the same is true for pages" but does not clear what this MOST Circumstances means .I had raised this bug in 2008 SP1 beta this year ,as its carries the same to 2008 as well .But It was turned down as Doc Bug .That is no engine changes will be made but they will change the BOL contents .
This kind of situation occures when there are a large number of rows in readpast.
The other thing is , you are using Read-Committed isolation .
Try using Snapshot isolation and use the benefit of row -versioning .
Also , make sure that you do the basic homework like :having proper insexes , Indexes are not fragmented and Stats are updated regularly .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 9, 2009 at 10:06 am
hi_abhay78,
Thank you so much for your details explanation.
Yes you are right, I seldom run any index fragmentation and stat. Will consider to study more about it, because I'm receiving transaction in online basis (24 hours and 7 days a week). Not sure how much would it impact the transaction when running fragmentation and stat. Currently this table have only one clustered index (TCAID).
One more thing I'm not sure is, can I replace READPAST with NOLOCK (which will not cause any lock on pages)? Would it be a possibility that I will get incomplete record if use NOLOCK because one of the field in the table is ntext data type.
Hope to get more hint about it. Thank you.
July 9, 2009 at 10:07 am
hi_abhay78,
Thank you so much for your details explanation.
Yes you are right, I seldom run any index fragmentation and stat. Will consider to study more about it, because I'm receiving transaction in online basis (24 hours and 7 days a week). Not sure how much would it impact the transaction when running fragmentation and stat. Currently this table have only one clustered index (TCAID).
One more thing I'm not sure is, can I replace READPAST with NOLOCK (which will not cause any lock on pages)? Would it be a possibility that I will get incomplete record if use NOLOCK because one of the field in the table is ntext data type.
Hope to get more hint about it. Thank you.
July 9, 2009 at 10:09 am
hi_abhay78,
Thank you so much for your details explanation.
Yes you are right, I seldom run any index fragmentation and stat. Will consider to study more about it, because I'm receiving transaction in online basis (24 hours and 7 days a week). Not sure how much would it impact the transaction when running fragmentation and stat. Currently this table have only one clustered index (TCAID).
One more thing I'm not sure is, can I replace READPAST with NOLOCK (which will not cause any lock on pages)? Would it be a possibility that I will get incomplete record if use NOLOCK because one of the field in the table is ntext data type.
Hope to get more hint about it. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply