Sample Header Ad - 728x90

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