Why are query executed first blocked by update executed later

博雄 胡 585 Reputation points
2025-04-10T07:52:14.9666667+00:00

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
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
177 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 75,051 Reputation points
    2025-04-10T15:28:56.3266667+00:00

    As Sqlserver uses a cooperative scheduler, the blocked sessions have yielded, and continuation is basically random.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.