SQL Server query - why am I getting deadlock? -

i have following code:

set transaction isolation level read committed; --this clarity  declare @jobname nvarchar(128);  begin tran     select @jobname = jobname     dbo.jobdetails     executionstate_status = 1      waitfor delay '00:00:10'      update dbo.jobdetails     set executionstate_status = 10     jobname = @jobname  commit  

and second piece that's same:

set transaction isolation level read committed;  declare @jobname nvarchar(128);  begin tran     select @jobname = jobname     dbo.jobdetails     executionstate_status = 1      waitfor delay '00:00:15'      update dbo.jobdetails     set executionstate_status = 20     jobname = @jobname  commit  

the difference in status we're setting (10 vs 20) , delay (10s vs 15s).

i'm executing them in parallel in management studio - 2 tabs. problem - read committed transaction isolation level works expected - last modification applied , both scripts execute .

however that's not want - want execute 1 , second should nothing. that's why tried change level repeatable read. according knowledge (which want challenge right now) should behave this:

• first transaction starts , locks rows reads
• first transaction waiting 10 seconds
• second transaction starts in meantime , cannot execute select since it's locked first one
• first transaction finishes wait, updates table & commits
• second transaction can proceed , nothing since rows status = 1 updated

unfortunately results i'm seeing far - transactions deadlocked , 1 of them killed sql server. don't understand why happening since accessing resources in same order.

here scripts necessary 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 jobdetails values( 'my job', 1) update jobdetails set executionstate_status = 1 

additional notes:

• i'm testing 1 row in table.
• changing level serializable results in deadlock.
• the reason why code looks because i'm trying simulate orm going - first entity, check in code if status 1 , send update where based on pk. know write code without orm having update where executionstate_status = 1

this assumption wrong:

second transaction starts in meantime , cannot execute select since it's locked first one

both repeatable read transactions' selects aquire , hold s locks on key till commit. s locks compatible. deadlocked when update trying x lock incompatible s lock. contrary that, select in read commited transaction immediatley releases s lock.

use exec sp_lock , see locks, e.g.

declare @jobname nvarchar(128);  begin tran     select @jobname = jobname     dbo.jobdetails     executionstate_status = 1      waitfor delay '00:00:10'      exec sp_lock  58,57      update dbo.jobdetails     set executionstate_status = 10     jobname = @jobname  commit