Microsoft® SQL Server™ has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server automatically locks resources 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, but has a lower overhead because fewer locks are being maintained.
SQL Server can lock these resources (listed in order of increasing granularity).
| Resource | Description |
|---|---|
| RID | Row identifier. Used to individually lock a single row within a table. |
| Key | Key; a row lock within an index. Used to protect key ranges in serializable transactions. |
| Page | 8-KB data page or index page. |
| Extent | Contiguous group of eight data pages or index pages. |
| Table | Entire table, including all data and indexes. |
| DB | Database. |
SQL Server locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.
SQL Server uses these resource lock modes.
| Lock mode | Description |
|---|---|
| Shared (S) | Used for operations that do not change or update data (read-only operations), 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 then potentially updating resources later. |
| Exclusive (X) | Used for data-modification operations, such as UPDATE, INSERT, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. |
| Intent | Used to establish a lock hierarchy. |
| Schema | Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability (Sch-S) and schema modification (Sch-M). |
| Bulk update (BU) | Used when bulk copying data into a table and the TABLOCK hint is specified. |
Shared (S) locks allows concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared locks exist on the resource. Shared 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 locks for the duration of the transaction.
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. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction. A lock wait occurs. The second transaction attempts to acquire an exclusive lock for its update. Because both transactions are converting to exclusive locks and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update locks are used. Only one transaction can obtain an update lock to a resource at a time. If a transaction modifies a resource, the update lock is converted to an exclusive lock. Otherwise, the lock is converted to a shared-mode lock.
Exclusive (X) locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive lock.
An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page. Intent locks improve performance because SQL Server needs to examine intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.
Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
| Lock mode | Description |
|---|---|
| Intent shared (IS) | Indicates the intention of a transaction to read some (but not all) resources lower down 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 down 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 down in the hierarchy and modify some (but not all) resources lower down in the hierarchy by placing IX locks on those individual resources. Concurrent IS locks at the top-level resource are allowed. For example, an SIX lock on a table places an SIX lock on the table (allowing concurrent IS locks), and IX locks on the pages being modified (and X locks on the modified rows). There can be only one SIX lock per resource at one time preventing updates to the resource made by other transactions, although other transactions can read resources lower down in the hierarchy by obtaining IS locks at the table level. |
Sch-M locks are taken when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed. Sch-S locks are taken while compiling queries. Sch-S locks do not block out any transactional locks, including exclusive (X) locks. Therefore, while a query is being compiled other transactions can run, even transactions with X locks on a table. However, DDL operations cannot be performed on the table.
Bulk update (BU) locks are taken 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 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.
| Deadlocking | Cursor Locking |
| Locking Hints |