Why does a SELECT query (Sch-S) get blocked due to an existing Sch-S lock held by index reorganize command?
1
vote
1
answer
423
views
SELECT query requires a SCH-S lock. This can be shared with other read queries.
UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries.
Link: https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/
The example shows an index reorg (Sch-S) lock blocks the statistics update's Sch-M lock. But why does it also block the subsequent Sch-S lock requested by the SELECT query? Aren't Sch-S locks shared?
Asked by variable
(3590 rep)
Jun 14, 2023, 10:52 AM
Last activity: Jun 14, 2023, 12:34 PM
Last activity: Jun 14, 2023, 12:34 PM