x

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
more ▼

asked Oct 21, 2009 at 10:15 AM in Default

avatar image

Bob Balok
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Oct 21, 2009 at 12:02 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

runtime error 2147217865 (80040e37) timezone 12.0

more ▼

answered Aug 24, 2017 at 02:40 PM

avatar image

realtime
1

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...

Aug 24, 2017 at 06:23 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x64
x50
x5

asked: Oct 21, 2009 at 10:15 AM

Seen: 1498 times

Last Updated: Aug 24, 2017 at 06:23 PM

Copyright 2018 Redgate Software. Privacy Policy