March 14, 2011 at 4:40 pm
Guys
Recently I have come across a deadlock scenario on OLTP box (Sql server 2005) of a client
and found that it's caused by two stored procedures being called by 2 different threads.
1.Insert sp that inserts data in X table.
Insert Into X (col1 , col2 , col3 )
Values ('value 1' , 'value 2' , 'value 3' )
2.Delete sp that deletes data from X table.
DELETE X
FROM X T1 WITH (NOLOCK)
INNER JOIN Y T2 WITH (NOLOCK)
ON T1.[col2] = T2.[col2]
WHERE t2.date < 'date time value'
X table has one unique, clustered primary key and two non clustered, non unique indexes.
I have analysed the deadlock by setting t1222 tace flag on and the output is summarized below;
Insert sp acquired a IX lock on Non clustered index for Column 1.
Delete sp is waiting for X lock on the same Non clustered index for column 1 during this time.
Delete sp acquired a U lock on Non clustered index for Column 2.
Insert sp is waiting for a IX lock on the same Non clustered index for Column 2 during this time.
Any idea or suggestion to avoid deadlock would be really appreciated.
Thanks,
-Salman.
March 14, 2011 at 5:01 pm
Salman Shehbaz-340356 (3/14/2011)
GuysRecently I have come across a deadlock scenario on OLTP box (Sql server 2005) of a client
and found that it's caused by two stored procedures being called by 2 different threads.
1.Insert sp that inserts data in X table.
Insert Into X (col1 , col2 , col3 )
Values ('value 1' , 'value 2' , 'value 3' )
2.Delete sp that deletes data from X table.
DELETE X
FROM X T1 WITH (NOLOCK)
INNER JOIN Y T2 WITH (NOLOCK)
ON T1.[col2] = T2.[col2]
WHERE t2.date < 'date time value'
X table has one unique, clustered primary key and two non clustered, non unique indexes.
I have analysed the deadlock by setting t1222 tace flag on and the output is summarized below;
Insert sp acquired a IX lock on Non clustered index for Column 1.
Delete sp is waiting for X lock on the same Non clustered index for column 1 during this time.
Delete sp acquired a U lock on Non clustered index for Column 2.
Insert sp is waiting for a IX lock on the same Non clustered index for Column 2 during this time.
Any idea or suggestion to avoid deadlock would be really appreciated.
Thanks,
-Salman.
A note before we get into the gritty: You can't nolock the table you're going to delete from. That hint is being ignored. I also would strongly recommend against the NOLOCK on table Y, too.
For the IX/X locks, what level are they requesting (TAB, Row, or Page?). IX is Intent Exclusive. Are you sure Insert sp didn't acquire an U lock on NC Column1?
The insert proc definately shouldn't be acquiring more then a row lock, which I could see annoying the delete lock if it's got enough rows to want to escalate to a table lock, but shouldn't cause the deadlock unless the value that went in via the insert is about to be deleted as well by the deletion proc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 15, 2011 at 12:42 am
IX locks are at the page level. Moreover the Insert & delete statements always touches 2 different sets of data;
below is the output of trace file;
deadlock-list
deadlock victim=process3c77d68
process-list
process id=process3c12c58 taskpriority=0 logused=1044 waitresource=PAGE: 17:8:7726 waittime=1250 ownerId=5169682909 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.443 XDES=0xfe64d78b0 lockMode=IX schedulerid=2 kpid=9544 status=suspended spid=219 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:34:03.457 lastbatchcompleted=2011-02-03T03:34:03.453 clientapp=.Net SqlClient Data Provider hostname=HQMTSRV026 hostpid=3308 loginname=EASUser isolationlevel=read committed (2) xactid=5169682909 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtend=296 sqlhandle=0x0200000084ce2a1d0e95a5623fa3a9c0981d422e33cab999
(@1 int<c/>@2 varchar(8000)<c/>@3 nvarchar(4000))INSERT INTO [VB_Audit_TransactionDetail]([ItemID]<c/>[TransactionID]<c/>[ItemValue]) values(@1<c/>@2<c/>@3)
frame procname=adhoc line=1 stmtend=296 sqlhandle=0x02000000afcb1733f435fb93e13556600acf32bb32e10020
Insert Into VB_Audit_TransactionDetail (ItemID <c/> TransactionID <c/> ItemValue ) Values (4 <c/> '0255978c-f56e-477e-b361-8abe62433cff' <c/> N'HQOLB006' )
frame procname=EAS.dbo.SP_Insert line=13 stmtstart=482 stmtend=522 sqlhandle=0x03001100805efa5997d69400719600000100000000000000
exec (@CommandText)
inputbuf
Proc [Database Id = 17 Object Id = 1509580416]
process id=process3c77d68 taskpriority=0 logused=364 waitresource=PAGE: 17:6:334008 waittime=1234 ownerId=5169682116 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.053 XDES=0xa8e297cd0 lockMode=X schedulerid=12 kpid=10300 status=suspended spid=327 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:33:41.137 lastbatchcompleted=2011-02-03T03:33:41.133 clientapp=Microsoft SQL Server hostname=HQSSISSRV002 hostpid=7632 loginname=NBKDOM\SQLCSRVC isolationlevel=read committed (2) xactid=5169682116 currentdb=17 lockTimeout=4294967295 clientoption1=671350816 clientoption2=128056
executionStack
frame procname=EAS.dbo.PurgeAuditTransactionTables line=59 stmtstart=4202 stmtend=4728 sqlhandle=0x030011006354a2313d11ae00979a00000100000000000000
DELETE [dbo].[Audit_TransactionDetail]
FROM [dbo].[Audit_TransactionDetail] T1 WITH (NOLOCK)
INNER JOIN [dbo].[Audit_NBKTransaction] T2 WITH (NOLOCK)ON T1.[TransactionID] = T2.[TransactionID]
WHERE TransactionPostedDateTime < @LastReplicationDateTime
frame procname=adhoc line=1 sqlhandle=0x0100110096968c0560c430ff190000000000000000000000
EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'
inputbuf
EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870'
resource-list
pagelock fileid=8 pageid=7726 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock4f79500 mode=U associatedObjectId=886415243542528
owner-list
owner id=process3c77d68 mode=U
waiter-list
waiter id=process3c12c58 mode=IX requestType=wait
pagelock fileid=6 pageid=334008 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock846afca00 mode=IX associatedObjectId=604940266831872
owner-list
owner id=process3c12c58 mode=IX
waiter-list
waiter id=process3c77d68 mode=X requestType=wait
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply