October 31, 2011 at 4:27 pm
Our application is sometimes experiencing conversion deadlocks. Two transactions seem to be deadlocking while trying to write to the same table.
The first transaction is an update of a single row using a clustered primary key in the where clause.
The second transaction is an insert of a single row.
Both transactions are generated by stored procedures.
Both transactions are getting S locks on the table, then both try to convert to IX locks and they deadlock.
The first transaction does a SELECT from the table using the primary key, then does an UPDATE of the same row just selected.
The second transaction is a simple INSERT statement into the same table.
The first transaction looks something like this (@val is passed into the SP as a parameter):
begin try
select @curCol1 from table where key = x;
begin tran
update table
set col1 = CASE WHEN @val IS NULL THEN @curCol1 ELSE @val END
where key = x;
end tran
end try
begin catch
...
end catch
I don't know what the intent of this code was - perhaps the programmer thought that selecting zero rows would cause his catch block to fire. Question is, am I likely to fix this deadlock by removing the select statement from this procedure?
This is tricky to troubleshoot - the deadlocking is very intermittent, and we can only catch evidence of it while running a trace for several days running at a customer site, which makes it hard to see what kind of locking behavior is happening, much less attempt to fix the problem using trial and error methods.
Here is the pasted deadlock graph info:
<deadlock-list>
<deadlock victim="processc4ee38">
<process-list>
<process id="processc4ee38" taskpriority="0" logused="0" waitresource="OBJECT: 7:1250103494:0 " waittime="2164" ownerId="611506410" transactionname="user_transaction" lasttranstarted="2011-10-27T13:06:22.740" XDES="0x4e39bb8" lockMode="IX" schedulerid="2" kpid="3036" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="4" lastbatchstarted="2011-10-27T13:06:22.943" lastbatchcompleted="2011-10-27T13:06:22.943" clientapp=".Net SqlClient Data Provider" hostname="xxxHILL01" hostpid="2432" loginname="WAMApp" isolationlevel="serializable (4)" xactid="611506410" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="xxx.dbo.TestObvReqRslt_Upd" line="48" stmtstart="4854" stmtend="8910" sqlhandle="0x030007005275942b4325ef00e19c00000100000000000000">
UPDATE dbo.TestObvReqRslt
SET
TestObvSetID = CASE WHEN(@TestObvSetID IS NULL) THEN @curTestObvSetID ELSE @TestObvSetID END,
SlideID = CASE WHEN(@SlideID IS NULL) THEN @curSlideID ELSE @SlideID END,
TestObvMsgCustID = CASE WHEN(@TestObvMsgCustID IS NULL) THEN @curTestObvMsgCustID ELSE @TestObvMsgCustID END,
HostID = CASE WHEN(@HostID IS NULL) THEN @curHostID ELSE @HostID END,
HostVersion = CASE WHEN(@HostVersion IS NULL) THEN @curHostVersion ELSE @HostVersion END,
StainerSerialNumber = CASE WHEN(@StainerSerialNumber IS NULL) THEN @curStainerSerialNumber ELSE @StainerSerialNumber END,
StainerEffectiveType = CASE WHEN(@StainerEffectiveType IS NULL) THEN @curStainerEffectiveType ELSE @StainerEffectiveType END,
RunNbr = CASE WHEN(@RunNbr IS NULL) THEN @curRunNbr ELSE @RunNbr END,
RunStartStatus = CASE WHEN(@RunStartStatus IS NULL) THEN @curRunStartStatus ELSE @RunStartStatus END,
RunEstimatedT </frame>
<frame procname="xxx.dbo.TestObvReqRsltStatus_Upd" line="20" stmtstart="2708" stmtend="4088" sqlhandle="0x030007008b99882c4825ef00e19c00000100000000000000">
EXEC @Return = dbo.TestObvReqRslt_Upd
@TestObvReqRsltID = @TestObvReqRsltID ,
@HostID=@HostID ,
@HostVersion=@HostVersion ,
@StainerSerialNumber=@StainerSerialNumber ,
@StainerEffectiveType=@StainerEffectiveType ,
@RunNbr=@RunNbr ,
@RunStartStatus=@RunStartStatus ,
@RunEstimatedTime=@RunEstimatedTime ,
@SlidePosition=@SlidePosition ,
@RunStartStatusUpdTime=@RunStartStatusUpdTime ,
@RunStartDateTime=@RunStartDateTime ,
@RunComplStatus=@RunComplStatus ,
@RunComplStatusUpdTime=@RunComplStatusUpdTime ,
@RunComplDateTime=@RunComplDateTime ,
@ActualRunTime =@ActualRunTime ,
@RowCount =@RowCount OUTPUT </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 747149707] </inputbuf>
</process>
<process id="process324018e8" taskpriority="0" logused="276" waitresource="OBJECT: 7:1250103494:0 " waittime="2080" ownerId="611506330" transactionname="user_transaction" lasttranstarted="2011-10-27T13:06:22.727" XDES="0x5b3b010" lockMode="IX" schedulerid="3" kpid="6004" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2011-10-27T13:06:23.020" lastbatchcompleted="2011-10-27T13:06:23.020" clientapp=".Net SqlClient Data Provider" hostname="xxxHILL01" hostpid="1576" loginname="WAMApp" isolationlevel="serializable (4)" xactid="611506330" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="xxx.dbo.TestObvReqRslt_Ins" line="10" stmtstart="1470" stmtend="3036" sqlhandle="0x030007001951a02a4325ef00e19c00000100000000000000">
INSERT INTO dbo.TestObvReqRslt(TestObvSetID, SlideID, TestObvMsgCustID, HostID, HostVersion,
StainerEffectiveType, RunNbr, RunStartStatus, RunEstimatedTime, SlidePosition,
RunStartStatusUpdTime, RunComplStatus, RunComplStatusUpdTime, RunComplDateTime,
RunStartDateTime, ActualRunTime)
VALUES(@TestObvSetID, @SlideID, @TestObvMsgCustID, @HostID, @HostVersion, @StainerEffectiveType, @RunNbr, @RunStartStatus,
@RunEstimatedTime, @SlidePosition, @RunStartStatusUpdTime, @RunComplStatus, @RunComplStatusUpdTime, @RunComplDateTime,
@RunStartDateTime, @ActualRunTime) </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 715149593] </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="1250103494" subresource="FULL" dbid="7" objectname="xxx.dbo.TestObvReqRslt" id="lock7a3cbc80" mode="S" associatedObjectId="1250103494">
<owner-list>
<owner id="process324018e8" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="processc4ee38" mode="IX" requestType="convert"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1250103494" subresource="FULL" dbid="7" objectname="xxx.dbo.TestObvReqRslt" id="lock7a3cbc80" mode="S" associatedObjectId="1250103494">
<owner-list>
<owner id="processc4ee38" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process324018e8" mode="IX" requestType="convert"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
Thanks in advance for any insight.
October 31, 2011 at 4:32 pm
BaldNomad (10/31/2011)
Question is, am I likely to fix this deadlock by removing the select statement from this procedure?
Very likely.
It's trying to convert an S lock (which the select would take) to an IX, so that the update can get the X locks it needs to make the change. Removing the taker of the S lock should fix this.
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
October 31, 2011 at 4:53 pm
GilaMonster (10/31/2011)
BaldNomad (10/31/2011)
Question is, am I likely to fix this deadlock by removing the select statement from this procedure?Very likely.
It's trying to convert an S lock (which the select would take) to an IX, so that the update can get the X locks it needs to make the change. Removing the taker of the S lock should fix this.
This makes sense, but begs the question: if the update is taking an S lock because of the preceding SELECT, why does the INSERT need an S lock? It has no SELECT statement. I assumed that both the INSERT and the UPDATE will try to take a shared lock prior to an X lock as a matter of routine, regardless of the presence of the select.
November 1, 2011 at 3:00 am
BaldNomad (10/31/2011)
This makes sense, but begs the question: if the update is taking an S lock because of the preceding SELECT, why does the INSERT need an S lock?
No idea, I can't see what's before the insert in the procedure, nor where the transactions are
It has no SELECT statement. I assumed that both the INSERT and the UPDATE will try to take a shared lock prior to an X lock as a matter of routine, regardless of the presence of the select.
No. Update starts with U then converts to X, insert will take X straight away. Taking locks they don't need then converting is a waste of resources and a risk of deadlocks.
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
November 1, 2011 at 9:47 am
one more thing I just noticed - is it normal for these kinds of transactions to be serializable? You might have to scroll right on the trace xml, but both of these transactions seem to be flagged as such. I remember reading somewhere in BOL about serializable transactions using shared locks...
November 1, 2011 at 10:05 am
The .Net data access defaults to serialisable, that's probably why the isolation level is so high. If the isolation level was SQL's default (read committed) then likely this deadlock wouldn't happen as the shared locks would be released once the select completes.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply