Deadlock Problem With UPDATE

Question

question

Bob Balok asked Oct 21, '09

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

deadlock locking sql2005

10 |1200

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

Answer 1 · 2009-10-21T12:02:36Z

TG answered Oct 21, '09

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

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

Answer 2 · 2017-08-24T14:40:01Z

realtime answered Aug 24, '17

runtime error 2147217865 (80040e37) timezone 12.0

1

10 |1200

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

ThomasRushton ♦♦ commented · Aug 24, 2017 at 06:23 PM

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 ·

question