February 6, 2012 at 5:17 am
I am getting deadlock but I am not able to understand the below deadlock graph.
I am not able to understand what is the cause of getting deadlock issue...
- <deadlock-list>
- <deadlock victim="process52fe8e0">
- <process-list>
- <process id="process52fe8e0" taskpriority="0" logused="0" waitresource="OBJECT: 6:751341741:0" waittime="1625" ownerId="30443" transactionname="UPDATE" lasttranstarted="2012-02-03T12:38:31.543" XDES="0x5461988" lockMode="X" schedulerid="1" kpid="464" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-03T12:38:31.543" lastbatchcompleted="2012-02-03T12:38:31.543" clientapp=".Net SqlClient Data Provider" hostname="35WEBSITE" hostpid="1856" loginname="PIOfficeMail" isolationlevel="serializable (4)" xactid="30443" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
<frame procname="PIOfficeMail.dbo.UpdateMailMergeStatus" line="6" stmtstart="128" stmtend="994" sqlhandle="0x030006003dd40a3c0c0f9b00ec9f00000100000000000000">Update MailMergeFile WITH (ROWLOCK) set MailMergeFile.Status=4,StatusUpdatedOn=CURRENT_TIMESTAMP where MailMergeFile.Status=3 And 0=(select COUNT(*) from MailMergeWorkItem WITH (NOLOCK) where MailMergeWorkItem.MailMergeID=MailMergeFile.MailMergeID and MailMergeWorkItem.Status!=3) AND 0 != ( select COUNT(*) from MailMergeWorkItem WITH (NOLOCK) where MailMergeWorkItem.MailMergeID=MailMergeFile.MailMergeID)</frame>
</executionStack>
<inputbuf>Proc [Database Id = 6 Object Id = 1007342653]</inputbuf>
</process>
- <process id="process52ff8e8" taskpriority="0" logused="0" waitresource="OBJECT: 6:751341741:0" waittime="1625" ownerId="30423" transactionname="user_transaction" lasttranstarted="2012-02-03T12:38:31.450" XDES="0x5435988" lockMode="X" schedulerid="1" kpid="5108" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-03T12:38:31.543" lastbatchcompleted="2012-02-03T12:38:31.530" clientapp=".Net SqlClient Data Provider" hostname="35WEBSITE" hostpid="1856" loginname="PIOfficeMail" isolationlevel="serializable (4)" xactid="30423" currentdb="6" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
+ <executionStack>
<frame procname="PIOfficeMail.dbo.UpdateMailMergeFileStatus" line="21" stmtstart="1486" stmtend="2024" sqlhandle="0x0300060092672e39d40e9b00ec9f00000100000000000000">Update MailMergeFile WITH (ROWLOCK) Set [Status]=@Status, [StatusUpdatedOn]=CURRENT_TIMESTAMP Where [MailMergeID] in ((select I.Value from fn_Split(@MailMergeIds,',') as I)) -- If status is being set to indexing, then set mail merge table status to Active</frame>
</executionStack>
<inputbuf>Proc [Database Id = 6 Object Id = 959342482]</inputbuf>
</process>
</process-list>
- <resource-list>
- <objectlock lockPartition="0" objid="751341741" subresource="FULL" dbid="6" objectname="PIOfficeMail.dbo.MailMergeFile" id="lock4ee1dc0" mode="IX" associatedObjectId="751341741">
- <owner-list>
<owner id="process52ff8e8" mode="IX" />
</owner-list>
- <waiter-list>
<waiter id="process52fe8e0" mode="X" requestType="convert" />
</waiter-list>
</objectlock>
- <objectlock lockPartition="0" objid="751341741" subresource="FULL" dbid="6" objectname="PIOfficeMail.dbo.MailMergeFile" id="lock4ee1dc0" mode="IX" associatedObjectId="751341741">
- <owner-list>
<owner id="process52fe8e0" mode="IX" />
</owner-list>
- <waiter-list>
<waiter id="process52ff8e8" mode="X" requestType="convert" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
February 6, 2012 at 5:30 am
Please post the code for the procedure UpdateMailMergeStatus and the definition of MailMergeFile with all indexes
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
February 6, 2012 at 5:57 am
Hi,
Please Find the Needed Info :
CREATE PROCEDURE [dbo].[UpdateMailMergeStatus]
AS
BEGIN
Update MailMergeFile set MailMergeFile.Status=4,StatusUpdatedOn=CURRENT_TIMESTAMP
where MailMergeFile.Status=3
And
0=(select COUNT(*) from MailMergeWorkItem
where MailMergeWorkItem.MailMergeID=MailMergeFile.MailMergeID
and MailMergeWorkItem.Status!=3);
update MailMerge set IsMailMerged='True', MergeStatusID=2, CompletedDate=CURRENT_TIMESTAMP
where MailMerge.IsMailMerged='False'
and 0 = (
select COUNT(*) from MailMergeWorkItem
where MailMergeWorkItem.MailMergeID=MailMerge.MailMergeID
and MailMergeWorkItem.Status!=3);
END
CREATE TABLE [dbo].[MailMergeFile](
[MailMergeID] [int] NOT NULL,
[TemplateFile] [varbinary](max) NOT NULL,
[TemplateFileName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CSVFile] [varbinary](max) NOT NULL,
[CSVFileName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AttachmentFile] [varbinary](max) NULL,
[AttachmentFileName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexedCSVFile] [varbinary](max) NULL,
[FieldDelimiter] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [int] NOT NULL,
[StatusUpdatedOn] [datetime] NULL
)
CREATE TABLE [dbo].[MailMergeWorkItem](
[MailMergeID] [int] NOT NULL,
[WorkItemID] [int] NOT NULL,
[StartIndex] [int] NOT NULL,
[EndIndex] [int] NULL,
[CurrentIndex] [int] NULL,
[LastUpdatedOn] [datetime] NULL,
[Status] [int] NOT NULL
)
February 6, 2012 at 6:06 am
Indexes on those tables? Primary key?
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
February 6, 2012 at 6:18 am
there is no Primary Key, No Index on any table
February 6, 2012 at 6:26 am
Well there's your problem right there.
You need to analyse and add primary keys. My guess would be MailMergeFile should be on MailMergeID and MailMergeWorkItem on MailMergeID and WorkItemID, but that's a guess. You need to look at the tables, identify the unique columns and add primary keys.
I also suggest an index on both tables on Status INCLUDE MailMergeID.
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
February 6, 2012 at 6:33 am
Hi GilaMaster,
Thanks for the help.
Can you please let me know what is the exact problem.
Which statement deadlock is being created on? (Select or Update)
and which table is causing deadlock?
Thanks & Regards,
Akash
February 6, 2012 at 6:39 am
They're in the deadlock graph (and you have no selects in that procedure, so it can't be a select)
- <deadlock-list>
- <deadlock victim="process52fe8e0">
- <process-list>
- <process id="process52fe8e0" taskpriority="0" logused="0" waitresource="OBJECT: 6:751341741:0" waittime="1625" ownerId="30443" transactionname="UPDATE" lasttranstarted="2012-02-03T12:38:31.543" XDES="0x5461988" lockMode="X" schedulerid="1" kpid="464" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-03T12:38:31.543" lastbatchcompleted="2012-02-03T12:38:31.543" clientapp=".Net SqlClient Data Provider" hostname="35WEBSITE" hostpid="1856" loginname="PIOfficeMail" isolationlevel="serializable (4)" xactid="30443" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
<frame procname="PIOfficeMail.dbo.UpdateMailMergeStatus" line="6" stmtstart="128" stmtend="994" sqlhandle="0x030006003dd40a3c0c0f9b00ec9f00000100000000000000">Update MailMergeFile WITH (ROWLOCK) set MailMergeFile.Status=4,StatusUpdatedOn=CURRENT_TIMESTAMP where MailMergeFile.Status=3 And 0=(select COUNT(*) from MailMergeWorkItem WITH (NOLOCK) where MailMergeWorkItem.MailMergeID=MailMergeFile.MailMergeID and MailMergeWorkItem.Status!=3) AND 0 != ( select COUNT(*) from MailMergeWorkItem WITH (NOLOCK) where MailMergeWorkItem.MailMergeID=MailMergeFile.MailMergeID)</frame>
</executionStack>
<inputbuf>Proc [Database Id = 6 Object Id = 1007342653]</inputbuf>
</process>
- <process id="process52ff8e8" taskpriority="0" logused="0" waitresource="OBJECT: 6:751341741:0" waittime="1625" ownerId="30423" transactionname="user_transaction" lasttranstarted="2012-02-03T12:38:31.450" XDES="0x5435988" lockMode="X" schedulerid="1" kpid="5108" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-02-03T12:38:31.543" lastbatchcompleted="2012-02-03T12:38:31.530" clientapp=".Net SqlClient Data Provider" hostname="35WEBSITE" hostpid="1856" loginname="PIOfficeMail" isolationlevel="serializable (4)" xactid="30423" currentdb="6" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
+ <executionStack>
<frame procname="PIOfficeMail.dbo.UpdateMailMergeFileStatus" line="21" stmtstart="1486" stmtend="2024" sqlhandle="0x0300060092672e39d40e9b00ec9f00000100000000000000">Update MailMergeFile WITH (ROWLOCK) Set [Status]=@Status, [StatusUpdatedOn]=CURRENT_TIMESTAMP Where [MailMergeID] in ((select I.Value from fn_Split(@MailMergeIds,',') as I)) -- If status is being set to indexing, then set mail merge table status to Active</frame>
</executionStack>
<inputbuf>Proc [Database Id = 6 Object Id = 959342482]</inputbuf>
</process>
</process-list>
- <resource-list>
- <objectlock lockPartition="0" objid="751341741" subresource="FULL" dbid="6" objectname="PIOfficeMail.dbo.MailMergeFile" id="lock4ee1dc0" mode="IX" associatedObjectId="751341741">
- <owner-list>
<owner id="process52ff8e8" mode="IX" />
</owner-list>
- <waiter-list>
<waiter id="process52fe8e0" mode="X" requestType="convert" />
</waiter-list>
</objectlock>
- <objectlock lockPartition="0" objid="751341741" subresource="FULL" dbid="6" objectname="PIOfficeMail.dbo.MailMergeFile" id="lock4ee1dc0" mode="IX" associatedObjectId="751341741">
- <owner-list>
<owner id="process52fe8e0" mode="IX" />
</owner-list>
- <waiter-list>
<waiter id="process52ff8e8" mode="X" requestType="convert" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
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
February 6, 2012 at 6:41 am
p.s. The procedure you posted and the code in the deadlock graph don't match.
Take those rowlock and nolock hints out. The rowlock's not going to help, without indexes SQL is going to have to read every row in the table hence will escalate the lock to the table. Nolock is ignored on the target of an update (and the other table is not the problem)
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 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply