CODEWITHSUNDEEP
sqlsql-serverlockingblocking
SqlDevInANoSqlWorld
SqlDevInANoSqlWorld

Reputation: 13

SQL Server Blocking Chains

Suppose I have 3 queries all run in SSMS in this order

(1) select * from MainTableA

(2) truncate table MainTableA

(3) select name from MainTableA (nolock) where Id=1

Before (1) is finished (2) starts and, before (2) is finished, but after it starts, (3) starts.

(1) takes a table IS lock on MainTableA

(2) waits for a Sch-M table lock on MainTableA and is blocked by (1)

(3) waits for a Sch-S lock on MainTableA and is blocked by (2)

Why is (3) blocked by (2)? Since a Sch-S lock is compatible with an IS lock and (2) hasn't acquired it's lock yet, shouldn't (3) proceed? The blocking chain shows (2) is blocked by (1) and (3) is blocked by (2), but I'm not understanding why (2) can block (3) when it hasn't acquired the lock yet.

Upvotes: 1

Views: 186

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89061

Why is (3) blocked by (2)? Since a Sch-S lock is compatible with an IS lock and (2) hasn't acquired it's lock yet, shouldn't (3) proceed?

That's a really good question, and it's not obvious that it should be. In fact, it didn't used to block.

But the current behavior is that a waiting Sch-M lock blocks new Sch-S lock requests. So (3) gets in line behind (2), and won't get its Sch-S lock until after the metadata operation (here TRUNCATE TABLE) completes.

Under the old behavior, (3) could proceed, but once it got its Sch-S lock then it would block (2), Then (4), (5), (6) ... come along, also getting Sch-S locks and blocking (2). And (2) might have to wait indefinitely for a quiet window to complete.

For online database operations, it's mostly better for metadata changes to complete quickly, and forcing new Sch-S locks to wait behind a pending Sch-M lock helps make that happen.

Of course sometimes you'd rather have the old behavior, and so it's recently been re-introduced for certain operations as "Low Priority Waits" along with new timeout behavior. See eg https://bobsql.com/how-it-works-sql-server-locking-wait_with_low_priority/ and https://www.sqlskills.com/blogs/paul/low-priority-locking-wait-types/

Upvotes: 3

Related Questions