Database Reference
In-Depth Information
Summary
SQL Server uses Schema locks to maintain stable schema during query compilation and execution. There are two
types of schema locks in SQL Server: schema stability (SCH-S) and schema modification (SCH-M) locks.
Schema stability (SCH-S) locks are acquired on objects referenced by queries during query compilation and
execution. In some cases, however, SQL Server can replace schema stability (SCH-S) locks with intent table locks,
which also protect table schema. Schema stability (SCH-S) locks are compatible with any other lock types, with the
exception of schema modification (SCH-M) locks.
Schema modification (Sch-M) locks are incompatible with any other lock types. SQL Server uses them during
DDL operations. If a DDL operation needs to scan or modify the data (for example, adding trusted foreign key
constraint to the table; altering partition function on non-empty partition), the schema modification (Sch-M) lock
would be held for the duration of the operation. This can take a lot of time on large tables and cause severe blocking
issues in the system. You need to keep this in mind when designing systems with DDL and DML operations running
in parallel.
SQL Server uses lock partitioning on systems that have 16 or more logical processors. With lock partitioning,
SQL Server maintains separate lock structures on a per-processor basis. Intent and schema stability locks are held
within the single lock partition, while other lock types are acquired and held across all partitions. This increases the
amount of memory required to store lock information, and it can increase the chances of deadlocks when DDL and
DML statements are running in parallel.
SQL Server 2014 introduces low-priority locks that can be used to reduce blocking during online index rebuild
and partition switch operations. Those locks do not block other sessions, which are requesting incompatible lock
types at the time when a low-priority lock is waiting for the lock to be acquired.
