kubal5003 kubal5003 - 3 months ago 16
SQL Question

SQL Server query - why am I getting deadlock?

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

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 
Comments