SQL Server query - why am I getting deadlock?

Question

I have the following code:

set transaction isolation level read committed; --this is for clarity only



DECLARE @jobName nvarchar(128);



BEGIN TRAN

    SELECT @jobName = JobName

    FROM dbo.JobDetails

    WHERE ExecutionState_Status = 1



    WAITFOR DELAY '00:00:10'



    UPDATE dbo.JobDetails

    SET ExecutionState_Status = 10

    WHERE JobName = @jobName



COMMIT

And second piece that's almost the same:

set transaction isolation level read committed;



DECLARE @jobName nvarchar(128);



BEGIN TRAN

    SELECT @jobName = JobName

    FROM dbo.JobDetails

    WHERE ExecutionState_Status = 1



    WAITFOR DELAY '00:00:15'



    UPDATE dbo.JobDetails

    SET ExecutionState_Status = 20

    WHERE JobName = @jobName



COMMIT

The difference is in the status to which we're setting (10 vs 20) and delay (10s vs 15s).

I'm executing them in parallel in Management Studio - two tabs. Now the problem - with read committed transaction isolation level it works as expected - the last modification is applied and both scripts execute successfully .

However that's not what I want - I want to execute just one and the second should do nothing. That's why I tried to change the level to REPEATABLE READ. According to my knowledge (which I want to challenge right now) it should behave like this:

first transaction starts and locks the rows it reads

first transaction is then waiting for 10 seconds

second transaction starts in the meantime and cannot execute the select since it's locked by the first one

first transaction finishes the wait, updates the table & commits

second transaction can then proceed and does nothing since all the rows with status = 1 were already updated

Unfortunately the results that I'm seeing are far from that - the transactions are deadlocked and one of them is killed by SQL Server. I don't really understand why this is happening since they are accessing resources in the same order.

Here are scripts necessary for testing:

CREATE TABLE [dbo].[JobDetails](

    [JobName] [nvarchar](128) NOT NULL,

    [ExecutionState_Status] [int] NULL DEFAULT ((0)),

 CONSTRAINT [PK_dbo.JobDetails] PRIMARY KEY CLUSTERED 

(

    [JobName] ASC

)) 

GO



INSERT INTO JobDetails VALUES( 'My Job', 1)

UPDATE JobDetails SET ExecutionState_Status = 1

Additional notes:

I'm testing this with only one row in the table.

Changing the level to serializable also results in deadlock.

The reason why this code looks like this is because I'm trying to simulate what ORM is going to do - first get the entity, then check in code if the status is 1 and then send the update with
WHERE
based on PK. I know I could write that code without ORM having the update with
WHERE ExecutionState_Status = 1

Answer 1

This assumption is wrong:

second transaction starts in the meantime and cannot execute the select since it's locked by the first one

Both repeatable read transactions' selects aquire and hold S locks on key till commit. S locks are compatible. They are deadlocked when update is trying to get X lock which is incompatible with S lock. Contrary to that, select in the read commited transaction immediatley releases S lock.

Use exec sp_lock , to see locks, e.g.

DECLARE @jobName nvarchar(128);

BEGIN TRAN
    SELECT @jobName = JobName
    FROM dbo.JobDetails
    WHERE ExecutionState_Status = 1

    WAITFOR DELAY '00:00:10'

    exec sp_lock  58,57

    UPDATE dbo.JobDetails
    SET ExecutionState_Status = 10
    WHERE JobName = @jobName

COMMIT

SQL Server query - why am I getting deadlock?

Sign up