SQL Server uses lock mechanism to synchronize data access to prevent corruption for in-memory data (such as buffer pool) and to provides higher level of concurrency. Most of DBAs get confuse when it comes to lock mechanism.
What is SQL Server Lock?
Microsoft SQL Server has multi-granular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task which called ‘Lock Escalation’. Locking at a smaller granularity, such as rows, increases concurrency and locking at a larger granularity, such as tables. Be aware that SQL Server by default holds maximum 5000 locks, very small granular lock such as row lock will increase the concurrency and the overhead, and very large granular lock such as table lock will decrease the concurrency and the overhead. SQL Server automatically changes the lock type if the number of used locks are huge or the data that is been locked are sequentially stored.
SQL Server Locks the following resources:
- RowID (RID): Row identifier. Used to lock a single row within a table.
- Key: Row lock within an index. Used to protect key ranges in serializable transactions.
- Page: 8 kilobyte –(KB) data page or index page.
- Extent: Contiguous group of eight data pages or index pages.
- Table: Entire table, including all data and indexes.
- Database: Database
SQL Server provides different lock modes to determine how the specific resource (one of above) needs to be locked, the lock modes are listed as follow:-
- Shared(S): Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
- Update(U): Update (U) locks prevent a common form of deadlock. A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive (X) lock.
- Exclusive(X): Exclusive (X) locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive (X) lock.
- Intent: An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy.
- Intent Shared (IS): Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing S locks on those individual resources.
- Intent Exclusive (IX): Indicates the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing X locks on those individual resources. IX is a superset of IS.
- Shared with Intent Exclusive (SIX): Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources. Concurrent IS locks at the top-level resource are allowed.
- Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.
- Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.
- Bulk Update: Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.
SQL Server Lock Compatibility
Resource lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on the same resource.
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 at the end of the first transaction. 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.
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft SQL Server automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold. SQL Server may choose to do both row and page locking for the same query, and also lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.
Lock Escalation Scenario:
when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.
Although row level locks increase concurrency, it is at the cost of system overhead. Table or page locks lower overhead, but at the expense of lowering concurrency.
Microsoft SQL Server uses a dynamic locking strategy to determine the most cost-effective locks. SQL Server automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.
Dynamic locking has the following advantages:
- Simplified database administration.
- Increased performance.
- Application developers can concentrate on development.
Key-range locking solves the phantom read concurrency problem and supports serializable transactions. Key-range locks cover individual records and the ranges between records, preventing phantom insertions or deletions into a set of records accessed by a transaction. Key-range locks are used only on behalf of transactions operating at the serializable isolation level.
Serializability requires that any query executed during a transaction must obtain the same set of rows if it is executed again at some later point within the same transaction. If this query attempts to fetch a row that does not exist, the row must not be inserted by other transactions until the transaction that attempts to access the row completes. If a second transaction were allowed to insert the row, it would appear as a phantom. If a second transaction attempts to insert a row that resides on a locked data page, page-level locking prevents the phantom row from being added, and serializability is maintained. However, if the row is added to a data page not already locked by the first transaction, a locking mechanism should be in place to prevent the row from being added.
A key-range lock works by covering the index rows and the ranges between those index rows rather than locking the entire base table rows. Because any attempt to insert, update, or delete any row within the range by a second transaction requires a modification to the index, the second transaction is blocked until the first transaction completes because key-range locks cover the index entries.