Schema Locks - Pro SQL Server Internals - page 433

Chapter 23

Schema Locks

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 performance of online index rebuild and partition switch operations.

Schema Modification Locks
Schema modification (Sch-M) locks are acquired before any metadata changes and during execution of a TRUNCATE
TABLE statement. You can think about that lock type as a “super-lock.” It is incompatible with any other lock types,
and it completely blocks access to the object.

■ Similar to exclusive (X) locks, schema modification (Sch-M) locks are held until the end of the transaction.
You need to keep this in mind when you run DDL statements within explicit transactions. While that allows you to rollback
all of the schema changes in case of an error, it also prevents any access to the affected objects until the transaction
is committed.

Note

SQL Server also uses schema modification (Sch-M) locks while altering the partition function. This can seriously
affect the availability of the system when such alterations introduce data movement or scans. Access to the entire
partitioned table is then blocked until operation is completed.
Schema stability (Sch-S) locks are used during DML query compilation and execution. SQL Server acquires
them regardless of the transaction isolation level, even in READ UNCOMMITTED mode. Schema stability (Sch-S) locks are
compatible with any other than schema modification (Sch-M) lock types.
SQL Server can perform some optimizations to reduce the number of locks acquired. While a schema stability
(Sch-S) lock is always used during query compilation, SQL Server can replace them with intent object locks during
query execution. Let's look at an example.
In the first session, we started the transaction and altered the table acquiring a schema modification (Sch-M)
lock. In the next step in the other sessions, we ran a SELECT statement in READ UNCOMMITTED transaction isolation level
and a DELETE statement. Both of these sessions were blocked while waiting for schema stability (Sch-S) locks that were
required for query compilation. Figure 23-1 illustrates this behavior.