June 10, 2010 at 4:58 am
Hi We are seeing frequent deadlocks in our application. They seem to be happening when two stored procedures run an update statement at the same time. Seems like The two updates are locked on the PrimaryKey and the Non Clustered Index. We used the non clustered index to help other queries but deadlock is really hurting our app now.
Any information on how to resolve is appreciated:
This is the deadlock graph:
<deadlock-list>
<deadlock>
<victim-list>
<victimProcess id="process5a3a988" />
</victim-list>
<process-list>
<process id="process5a3a988" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039631872 (2b004849815d)" waittime="3903" ownerId="5891617" transactionname="user_transaction" lasttranstarted="2010-06-09T18:54:42.133" XDES="0x118c89970" lockMode="U" schedulerid="6" kpid="6000" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-06-09T18:54:42.133" lastbatchcompleted="2010-06-09T18:54:42.133" clientapp=".Net SqlClient Data Provider" hostname="SRVA" hostpid="2948" loginname="XXXX" isolationlevel="read committed (2)" xactid="5891617" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="66" stmtstart="3502" stmtend="3814" sqlhandle="0x03000600efd422424e07f300909d00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1109578991] </inputbuf>
</process>
<process id="process5a44988" taskpriority="0" logused="312" waitresource="KEY: 6:72057594039697408 (7701ea47eeb3)" waittime="3903" ownerId="5891616" transactionname="user_transaction" lasttranstarted="2010-06-09T18:54:42.133" XDES="0x80bc4780" lockMode="X" schedulerid="7" kpid="788" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-06-09T18:54:42.133" lastbatchcompleted="2010-06-09T18:54:42.133" clientapp=".Net SqlClient Data Provider" hostname="SRVB" hostpid="4380" loginname="XXXX" isolationlevel="read committed (2)" xactid="5891616" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="18" stmtstart="756" stmtend="1630" sqlhandle="0x03000600b6b02e412bb611018a9d00000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1093578934] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594039631872" dbid="6" objectname="" indexname="" id="lock5d06780" mode="X" associatedObjectId="72057594039631872">
<owner-list>
<owner id="process5a44988" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process5a3a988" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594039697408" dbid="6" objectname="" indexname="" id="lock5bdf380" mode="U" associatedObjectId="72057594039697408">
<owner-list>
<owner id="process5a3a988" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process5a44988" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
AssociatedObjectId(72057594039631872):PK_Nbr
process5a44988, Procedure/query:1109578991
update dbo.TEST with (rowlock)
set ProcessStatus = (CASE WHEN ((Status != 'I')
OR (@Status = 'S'))
THEN @Status
ELSE Status
END),
LM = ISNULL(@Parm_LM, @CurrentDate),
StartTime = ISNULL(@StartTime, @CurrentDate),
EndTime = ISNULL(@EndTime, @CurrentDate),
Attempts = @Attempts
where Nbr = @Nbr
AssociatedObjectId(72057594039697408):IDX_status_LM
process5a3a988, Procedure/query:1093578934,
update dbo.TableA with (rowlock)
set LastModified = @Date, ProcessStatus = 'I'
where Id = @Id and Status = 'P'
INDEXES ON the table:
PK_Nbr (Nbr IDentity (1,1) Primary Key)
[IDX_ID_Status] ([Id] ASC)INCLUDE ( [Status])
IDX_status_LM:(Status char(1)LM datetime) Includes (Attempts ,Nbr )
Thanks
June 10, 2010 at 7:01 am
Based on the information provided, it's hard to know for sure what's causing your deadlock. It looks like two different queries, but it's hard to say. The victim is on line 66 and the other on line 18 of the respective queries when the deadlock occurs. The text at the bottom shows two different processes. So the question is, in these queries, are you accessing the tables using a different order? Meaning, one is getting a read, then an update, while the other is getting an update then a read, or any other combination of this pattern? If so, there's your problem. You can either fix it so that they're accessing the data in the same order, or you can try putting a lock hint on the initial read of each query so that it takes the lock immediately (this has repercussions on performance).
If you post the entire query involved, not simply the statement that is deadlocking, it would be easier to see where the issue might be.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 10, 2010 at 10:48 am
there's likely more to this picture....
- is this in a transaction where other work is occuring?
- have you tried removing the rowlock hint ?
- is this only happening under load or can you easily reproduce this issue?
GAJ
Gregory A Jackson MBA, CSM
June 10, 2010 at 11:15 am
Thanks for the response. We haven't been able to reprodue the problem at all.
I didn't try removing the ROWLOCK HINT, will it help?
These are the procedures getting deadlocked.
CREATEPROCEDURE [dbo].[PROC_A]---- VICTIM GETTING DEADLOCKED AT the Update Part ObjectID: 1109578991
@Nbr int,
@Id varchar(64),
@OFFId varchar(10),
@sid varchar(25) = NULL,
@Data varbinary(max)
AS
SET NOCOUNT ON
declare @CurrentDate datetime
set @CurrentDate = getdate()
declare @ErrorMessage nvarchar(400),
@ErrorNumber int
Begin try
Begin transaction
insert into dbo.TABLEA(Id,
NBr,OFFID,SID-------)
values (@Nbr,@OFFID,@SID,---);
Commit
End try
Begin catch
Rollback
ERROR HANDLING GOES HERE......
End catch
Begin try
Begin transaction
update dbo.TABLEA with (rowlock)
set LM = @CurrentDate, [Status] = 'I'
where Id = @Id and [Status] = 'P'
Commit
End try
Begin catch
Rollback
ERROR HANDLING......
End catch
Return @@Error
This is the Second stored procedure which seem to be getting deadlocked at the update part(which is the only thing its doing):
CREATE PROCEDURE dbo.PROC_B
@NBr int,
@Statuschar(1),
@LMdatetime = null,
@StartTimedatetime = null,
@EndTimedatetime = null,
@Attemptsint = 0
AS
SET NOCOUNT ON
declare @CurrentDate datetime
set @CurrentDate = getdate()
Begin try
Begin transaction
update dbo.TABLEA with (rowlock)
set Status = (CASE WHEN ((Status != 'I')
OR (@Status = 'S'))
THEN @Status
ELSE Status
END),
LM = ISNULL(@LM, @CurrentDate),
StartTime = ISNULL(@StartTime, @CurrentDate),
EndTime = ISNULL(@PEndTime, @CurrentDate),
Attempts = @Attempts
where Nbr = @NBR
Commit
End try
Begin catch
Rollback
ERROR HANDLING----
raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorNumber, @ErrorLine)
End catch
Return @@Error
June 10, 2010 at 11:24 am
nothing looks dreadfully wrong to me but "try" this.....
1) remove the rowlock hints (worth a shot)
2) remove the explicit begin transaction and committ\rollback (they are only wrapping single statements anyway)
update us on progress.....
GAJ
Gregory A Jackson MBA, CSM
June 10, 2010 at 12:30 pm
That process with an INSERT and UPDATE, I'm not shocked that it's deadlocking. You need to wrap it in a single transaction, not with two, which are both wrapped by an implicit transaction. I suspect that's the issue.
Also, I'd get rid of the ROWLOCK.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 10, 2010 at 12:37 pm
Thanks, We will remove the rowlock hint and also wrap the insert and Update in one transaction, So how having the insert and update in separate transaction within the same procedure is involved in the deadlock?
Thanks
June 10, 2010 at 12:46 pm
Again, not seeing everything, I suspect it's the two updates that are causing the issue. The INSERT & UPDATE is going to step on multiple pages or rows, in two steps, that are already being locked by the UPDATE. It's not too different from the classic "deadly embrace" of two processes accessing the same data from different directions.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply