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 

Answer 2

I got a link to an answer what's happening here. The example is virtually the same as mine so I'm not copying it here.

Now the quote with explanation:

A second type of deadlock can occur with the isolation level Repeatable Read if you read data with the intention to update it later. Let’s have a look at the T-SQL code of a simple transaction.

To cause this type of deadlock you just need to run the transaction across multiple sessions. You even don’t need to access different data ranges as you can see from the code. Let’s try to explain what happens here. When this transaction runs across multiple sessions concurrently, all sessions can acquire the Shared Locks for reading the data.

Because you hold the Shared Locks until the end of the transaction (COMMIT or ROLLBACK) in Repeatable Read, the following UPDATE statement can’t acquire the necessary Update Locks, because they are already blocked by the Shared Locks acquired in the different session. Deadlock!

And the solution - add WITH (UPDLOCK) to the first select:

SELECT @jobName = JobName
FROM dbo.JobDetails  WITH (UPDLOCK)
WHERE ExecutionState_Status = 1

Now I need to think how to apply that solution to an ORM..