Locking is mechanism of Database Engine to synchronize access by multiple users to the same piece of data at the same time.
Before reading / modifying a piece of data, a transaction must protect itself from the effects of another transaction modifying the same data. The transaction does this by requesting a lock on a piece of data. Locks have different modes such as shared or exclusive. The lock modes define the level of dependency the transaction. No transaction can be granted that would conflict with the mode of a lock already granted on that data to another transaction.
When a transaction modifies a piece of data, it holds lock protecting the modification until the end of the transaction. All locks held by a transaction are released when the transaction completes (either commits or rolls back).
Lock Granularity and Hierarchies
As you know, a database organizes by multiple objects: database instance, database files, table (including all data and indexes), extents, pages, rows…. So Microsoft Database Engine has multi-granular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, the Database Engine locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. However, it has a lower overhead because fewer locks are being maintained.
The Database Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. This group of locks at multiple levels of granularity is called a lock hierarchy. For example, to fully protect a read of an index, an instance of the Database Engine may have to acquire share locks on rows and intent share locks on the pages and table.
The following table shows the resources that the Database Engine can lock.
|RID||A row identifier used to lock a single row within a heap.|
|KEY||A row lock within an index used to protect key ranges in serializable transactions.|
|PAGE||An 8-kilobyte (KB) page in a database, such as data or index pages.|
|EXTENT||A contiguous group of eight pages, such as data or index pages.|
|HoBT||A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.|
|TABLE||The entire table, including all data and indexes.|
|FILE||A database file.|
|APPLICATION||An application-specified resource.|
|ALLOCATION_UNIT||An allocation unit.|
|DATABASE||The entire database.|
The Microsoft SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.
The following table shows the resource lock modes that the Database Engine uses.
|Shared (S)||Used for read operations that do not change or update data, such as a SELECT statement.|
|Update (U)||Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.|
|Exclusive (X)||Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.|
|Intent||Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).|
|Schema||Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).|
|Bulk Update (BU)||Used when bulk copying data into a table and the TABLOCK hint is specified.|
|Key-range||Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.|
Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. For example, no lock modes are compatible with exclusive locks. While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.
The following table shows the compatibility of the most commonly encountered lock modes.
|Existing granted mode|
|Intent shared (IS)||Yes||Yes||Yes||Yes||Yes||No|
|Intent exclusive (IX)||Yes||No||No||Yes||No||No|
|Shared with intent exclusive (SIX)||Yes||No||No||No||No||No|
An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at table and page level. However, one transaction will be granted an X lock at row level. The other transaction must wait until the row-level lock is removed.
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:
- When locking rows or index key ranges, the Database Engine places an intent lock on the pages that contain the rows or keys.
- When locking pages, the Database Engine places an intent lock on the higher level objects that contain the pages. In addition to intent lock on the object, intent page locks are requested on the following objects:
- Leaf-level pages of nonclustered indexes
- Data pages of clustered indexes
- Heap data pages
The Database Engine might do both row and page locking for the same statement to minimize the number of locks and reduce the likelihood that lock escalation will be necessary. For example, the Database Engine could place page locks on a nonclustered index (if enough contiguous keys in the index node are selected to satisfy the query) and row locks on the data.
To escalate locks, the Database Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), or row-level (RID) locks held by the transaction on the heap or index are released. If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.
The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.
|HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.|
If a lock escalation attempt fails because of conflicting locks held by concurrent transactions, the Database Engine will retry the lock escalation for each additional 1,250 locks acquired by the transaction.
Each escalation event operates primarily at the level of a single Transact-SQL statement. When the event starts, the Database Engine attempts to escalate all the locks owned by the current transaction in any of the tables that have been referenced by the active statement provided it meets the escalation threshold requirements. If the escalation event starts before the statement has accessed a table, no attempt is made to escalate the locks on that table. If lock escalation succeeds, any locks acquired by the transaction in a previous statement and still held at the time the event starts will be escalated if the table is referenced by the current statement and is included in the escalation event.
For example, assume that a session performs these operations:
- Begins a transaction.
- Updates TableA. This generates exclusive row locks in TableA that are held until the transaction completes.
- Updates TableB. This generates exclusive row locks in TableB that are held until the transaction completes.
- Performs a SELECT that joins TableA with TableC. The query execution plan calls for the rows to be retrieved from TableA before the rows are retrieved from TableC.
- The SELECT statement triggers lock escalation while it is retrieving rows from TableA and before it has accessed TableC.
If lock escalation succeeds, only the locks held by the session on TableA are escalated. This includes both the shared locks from the SELECT statement and the exclusive locks from the previous UPDATE statement. While only the locks the session acquired in TableA for the SELECT statement are counted to determine if lock escalation should be done, once escalation is successful all locks held by the session in TableA are escalated to an exclusive lock on the table, and all other lower-granularity locks, including intent locks, on TableA are released.
No attempt is made to escalate locks on TableB because there was no active reference to TableB in the SELECT statement. Similarly no attempt is made to escalate the locks on TableC, which are not escalated because it had not yet been accessed when the escalation occurred.
- A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
- A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
- The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
Escalation Threshold for a Transact-SQL Statement
When the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table. Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.
Lock escalation only occurs for tables that have been accessed at the time the escalation is triggered. Assume that a single SELECT statement is a join that accesses three tables in this sequence: TableA, TableB, and TableC. The statement acquires 3,000 row locks in the clustered index for TableA and at least 5,000 row locks in the clustered index for TableB, but has not yet accessed TableC. When the Database Engine detects that the statement has acquired at least 5,000 row locks in TableB, it attempts to escalate all locks held by the current transaction on TableB. It also attempts to escalate all locks held by the current transaction on TableA, but since the number of locks onTableA is < 5000, the escalation will not succeed. No lock escalation is attempted for TableC because it had not yet been accessed when the escalation occurred.
Escalation Threshold for an Instance of the Database Engine
Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:
- If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 24 percent of the memory used by the Database Engine, excluding AWE memory. The data structure used to represent a lock is approximately 100 bytes long. This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.
- If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.
The Database Engine can choose any active statement from any session for escalation, and for every 1,250 new locks it will choose statements for escalation as long as the lock memory used in the instance remains above the threshold.
For example, assume a session:
- Begins a transaction.
- Updates a table containing a clustered index.
- Issues a SELECT statement that references the same table.
The UPDATE statement acquires these locks:
- Exclusive (X) locks on the updated data rows.
- Intent exclusive (IX) locks on the clustered index pages containing those rows.
- An IX lock on the clustered index and another on the table.
The SELECT statement acquires these locks:
- Shared (S) locks on all data rows it reads, unless the row is already protected by an X lock from the UPDATE statement.
- Intent Share locks on all clustered index pages containing those rows, unless the page is already protected by an IX lock.
- No lock on the clustered index or table because they are already protected by IX locks.
If the SELECT statement acquires enough locks to trigger lock escalation and the escalation succeeds, the IX lock on the table is converted to an X lock, and all the row, page, and index locks are freed. Both the updates and reads are protected by the X lock on the table.
- Using an isolation level that does not generate shared locks for read operations.
- READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database option is ON.
- SNAPSHOT isolation level.
- READ UNCOMMITTED isolation level. This can only be used for systems that can operate with dirty reads.
|Changing the isolation level affects all tables on the instance of the Database Engine.|
- Using the PAGLOCK or TABLOCK table hints to have the Database Engine use page, heap, or index locks instead of row locks. Using this option, however, increases the problems of users blocking other users attempting to access the same data and should not be used in systems with more than a few concurrent users.
For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLEto escalate locks to the HoBT level instead of the table or to disable lock escalation.