Locking in the Database Engine
Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.
Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. The transaction does this by requesting a lock on the piece of data. Locks have different modes, such as shared or exclusive. The lock mode defines the level of dependency the transaction has on the data. No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Database Engine will pause the requesting transaction until the first lock is released.
When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. All locks held by a transaction are released when the transaction completes (either commits or rolls back).
Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a Transact-SQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions.
The following table lists the topics that describe the main concepts of locking.
Topic |
Description |
|---|---|
Locks can be requested for different types of resources, such as rows, pages, indexes, tables, or databases. Some operations require placing locks at multiple levels of granularity, forming a hierarchy of locks. |
|
Locks have different modes that specify the level of access other transactions have to the locked resource. |
|
Multiple transactions can acquire concurrent locks on the same resource if their lock modes are compatible. If a transaction requests a lock mode that conflicts with an existing lock, the transaction is paused until the first lock is freed. |
|
Locking a range of keys allows transactions running at the serializable isolation level to prevent phantom inserts and deletes. |
|
If a transaction acquires a large number of row or page locks, the Database Engine may grant a table lock and then free all the lower-level locks to minimize locking overhead. |
|
The Database Engine can dynamically choose the level of lock granularity based on the optimizer's estimate of the number of rows a Transact-SQL statement will reference. |
|
The Database Engine and its associated APIs provide several mechanisms for displaying information about the locks currently held in an instance or a database. |
|
Deadlocks occur when two tasks permanently block each other because each task has a lock on a resource needed by the other task. |