Database Reference
In-Depth Information
Low-Priority Locks (SQL Server 2014)
SQL Server 2014 introduced concept of
low-priority locks
, which can improve concurrency in the system during
online index rebuild and partition switch operations. You already know that a partition switch acquires a schema
modification (Sch-M) lock. The same is also true with online index rebuild. Even though it holds intent shared (IS)
table lock during the rebuild process, it needs to acquire shared (S) table lock at beginning and schema modification
(Sch-M) lock in the final phase of execution. Both locks are held for a very short time; however, they can introduce
blocking issues in busy OLTP environments.
Consider the situation when you start an online index rebuild at a time when you have another active transaction
modifying data in a table. That transaction will hold intent exclusive (IX) lock on a table, which prevents the
online index rebuild from acquiring shared (S) table lock. The lock request would wait in the queue and block all
other transactions that want to modify data in the table, and it needs to acquire intent exclusive (IX) locks there.
Figure
23-10
illustrates this situation.
Figure 23-10.
Blocking during the initial stage of an index rebuild
This blocking condition would clear only after the first transaction is completed and the online index rebuild
acquires and releases shared (S) table lock. A similar blocking condition could occur in the final stage of an online
index rebuild when it needs to acquire schema modification (Sch-M) lock to replace a reference to the index in the
metadata. Both readers and writers will be blocked while the index rebuild waits for the schema modification (Sch-M)
lock to be granted.
While this behavior occurs in every version of SQL Server, you can mitigate blocking issues in SQL Server 2014 by
using low-priority locks. Low-priority locks do not block other sessions that want to acquire incompatible lock types
while they are waiting for such locks to be acquired. Conceptually, you can think of low-priority locks as staying in a
different locking queue than regular locks. Figure
23-11
illustrates this concept.
