SQL Server Interview Question #252
What are key-range locks?
Advanced Transactions & Concurrency Senior Advanced
Quick Interview Answer
Key-range locks protect ranges of index keys rather than only existing rows. SQL Server uses them under SERIALIZABLE isolation to prevent another transaction from inserting rows into a range that would change the result of a protected predicate query.
Effective range locking depends on the access path and index structure. Appropriate indexes can therefore influence both performance and the scope of locking under SERIALIZABLE workloads.
Detailed Explanation
Key-range locks protect ranges of index keys rather than only existing rows. SQL Server uses them under SERIALIZABLE isolation to prevent another transaction from inserting rows into a range that would change the result of a protected predicate query.
Effective range locking depends on the access path and index structure. Appropriate indexes can therefore influence both performance and the scope of locking under SERIALIZABLE workloads.