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
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
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
WHERE
WHERE ExecutionState_Status = 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' select
s 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