As Sqlserver uses a cooperative scheduler, the blocked sessions have yielded, and continuation is basically random.
Why are query executed first blocked by update executed later
There are sessions 1-4, and session 1 is update, which is used to block the environment needed for other sessions to form.
Symptom 1: Sessions 2 and 4 are select and 3 are update. 1-2-3-4 is executed in sequence. Once session 1 is terminated, I expect sessions 2 and 4 to be executed first, and session 3 to be executed last. However, the test results showed that 3 was executed first, and 2 and 4 were executed last. :
Phenomenon 2: The logic is almost the same as phenomenon 1, with only minor changes in session 3, but the final phenomenon is that session 2-3-4 executes in sequence, as if in SERIALIZABLE behavior, which is not consistent with the expectation that 2 and 4 finish first and 3 finish last.
Here is the test code for phenomena 1 and 2, executed in the order they were written to restore my tests
--Symptom 1
--session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
create table testtable(id int);
insert into testtable values(1);
begin tran
update testtable with(TABLOCK) set id = id+1
--session 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select top 1 id from testtable with(TABLOCK)
--session 3
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin tran
update testtable set id = id+1
--session 4
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select top 1 id from testtable with(TABLOCK)
--seesion 1
commit
--Symptom 2
--session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin tran
update testtable with(TABLOCK) set id = id+1
--session 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select top 1 id from testtable with(TABLOCK)
--session 3
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin tran
update testtable with(tablock) set id = id+1 where id = 1
--session 4
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select top 1 id from testtable with(TABLOCK)
--session 1
commit