question

Bob Balok avatar image
Bob Balok asked

Deadlock Problem With UPDATE

Here is a typical UPDATE T-SQL we are having deadlock issues with. Just two users attempting to save data that is not related to each other, causes a deadlock. I suspect some things but I am not a DBA and know enough SQL as a programmer to get me by. Any help would be appreciated, I have done a lot of reading on deadlock issues but am still unsure.

The deadlock is a KEY type for a TRANSACTION that is X- exclusive.

WHERE WULaborHoursId = @WULaborHoursId                    
AND LastChanged = @LastChanged;                    

WULaborHoursId is the primary key but LastChanged is not part of any index. Would this cause a table scan? Can a Timestamp that changes be part of the primary key? This does not sound like a good idea. Would creating an index on the LastChanged Timestamp eliminate a table scan?

SELECT @LastChanged = LastChanged                    
FROM dbo.WorkUnitLaborHours                    
WHERE WULaborHoursId = @WULaborHoursId                    

This SELECT right after the UPDATE is something new to me that was introduced by my new boss and is part of some Framework they used in previous developments. I am not used to performing a SELECT right after an UPDATE and it seems it would share the transaction scope of the UPDATE which may not be desirable?

Here is the UPDATE stored proc:

ALTER PROCEDURE [dbo].[UpdateWorkUnitLaborHours] @WULaborHoursId UNIQUEIDENTIFIER, @WULaborResId UNIQUEIDENTIFIER, @LaborHours DECIMAL(12,2), @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @DistCurve VARCHAR(20), @UserId UNIQUEIDENTIFIER, @LastChanged Timestamp OUT AS /******************************************** Purpose: Update a Work Unit Labor record for a particular Work Unit Maintenance Logs: Developer Date Description --------- -------- ----------- bbalok 09/19/08 Created *********************************************/ UPDATE WorkUnitLaborHours set LaborHours = @LaborHours, StartDate = @StartDate, EndDate = @EndDate, DistCurve = @DistCurve, LastUpdatedBy = @UserId, LastUpdatedDate=GetDate() WHERE WULaborHoursId = @WULaborHoursId AND LastChanged = @LastChanged; IF @@ROWCOUNT = 0 --handling of concurrency issue based on @lastChanged RAISERROR('Row has been edited by another user', 16, 1) SELECT @LastChanged = LastChanged FROM dbo.WorkUnitLaborHours WHERE WULaborHoursId = @WULaborHoursId
deadlocklockingsql2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

Though your description doesn't say it I assume that when you SELECT the row for review/edit you are retrieving the LastChanged value. And then passing that value into the UPDATE procedure.

Your code, as it is, shouldn't cause any deadlocks. I suspect that you must be doing two things from your calling code whether that is from your application or from another sql process:

one is setting a transaction isolation level to something more restrictive than the default "read committed". The other thing is I suspect you are creating an explicit transaction. I can create a deadlock with some test code only under those two conditions using a WAITFOR 5 second delay between selecting the LastChanged value and execing the Update proc then running the same code from two different windows.

Here was may sample calling code:

set transaction isolation level serializable            
            
--This makes my deadlock go away:            
--set transaction isolation level read committed            
            
begin tran            
       declare @lc timestamp            
       select @lc = lastChanged from t where pk = 1            
            
       --emulate collision from concurrent calls            
       waitfor delay '00:00:05.000'            
            
       exec upd @pk = 1, @i = 2, @lastChanged = @lc output            
commit tran            
select @lc            
set transaction isolation level read committed            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

realtime avatar image
realtime answered
runtime error 2147217865 (80040e37) timezone 12.0
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry, do you have a question? If so, I suggest asking it as a fresh question rather than a cryptic response to a question that was asked 8 years ago...
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.