SQL Server uses schema locks to ensure that the database schema is consistent during the query execution, for example, to prevent table alteration while other queries are accessing the table. Exclusive (X) table lock will not help here because in a READ UNCOMMITTED transaction isolation level, readers do not acquire intent shared (IS) table locks. This is the reason SQL Server introduces two other lock types: schema stability (Sch-S) and schema modification (Sch-M).
In this chapter, we will discuss schema locks in depth. We will also talk about low-priority locks, which are introduced in SQL Server 2014 to improve the ...