Understanding Locking in SQL Server
SQL Server implements multi-granular locking, which allows transaction to lock different types of resources at different levels. To minimize the effort on locking, SQL Server automatically locks resources at a level appropriate to the transaction. To increase concurrency, SQL Server implements locking at a smaller granularity, such as rows, but enforces a higher overhead because more locks must be held if many rows are locked. Locking at the table level, that is, higher granularity is low in concurrency, because it rejects access to any part of the table by other transactions. The higher the granularity the lower the overhead on the database server because fewer locks are maintained.
Locking Items
SQL Server can lock the following resources. The table lists the resources that can be locked in the order of increasing granularity.
SQL Server Lock Modes
For transaction to access resources, SQL Server resolves a conflict between concurrent transaction by using modes. SQL Server uses the resource lock modes listed in the following table:
Shared Locks : Shared (S) locks, by their functionality, allow concurrent transaction to read a resource. If there are any shared locks on a resource, no other transaction can modify the data on that resource. A shared lock releases the resource after the data has been read by the transaction. This is not possible when 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 Locks : Update (U) locks, by their functionality, prevent a common form of deadlock from occurring. The update locks occurs with a very simple process. A transaction reads a record, that is, acquires a shared (S) lock on the resource (page or row), and then any attempt by the transaction to modify the row, implements lock conversion to an exclusive (X) lock. When two concurrent transaction acquire shared mode locks on a resources and then attempt to update the data concurrently, one transaction attempts conversion of the lock to an exclusive lock. In this scenario, the conversion from a shared mode to an exclusive lock must wait. This is because the exclusive lock for one transaction is not compatible with the shared mode lock of the other. Hence, a lock wait occurs. For its processing, then, the second transaction modifies the data and attempts to acquire an exclusive lock. Under these circumstances, when both the transactions are converting from shared to exclusive locks, a deadlock occurs, because each transaction is waiting for the other transaction to release its shared mode lock. Hence, update locks are used to avoid this potential deadlock problem. SQL Server allows only one transaction to obtain an update lock on a resource at a time. The update lock is converted to an exclusive lock if a transaction modifies a resource. Otherwise, the lock is converted to a shared-mode lock.
Exclusive Locks : Exclusive (X) locks, by their functionality, exclusively restrict concurrent transactions from accessing a resource. No other transaction can read or modify data locked with an exclusive lock.
Intent Locks : An intent (I) lock, by its functionality, indicates that SQL Server wants to acquire a shared or exclusive lock on some of the resources lower down in the hierarchy. For example, when a shared intent lock is implemented at the table level, this means that a transaction intends to place shared locks on pages or rows within that table. Implementing an intent lock at the table containing that page. Intent locks improve SQL Server performance because SQL Server examines intent only at the table level to determine if a transaction can safely acquire a lock on that table. Therefore, you must examine every row or page lock on the table to determine whether or not a transaction can lock the entire table.
Intent locks with their diversified features include intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX) locks.
Schema Locks : SQL Server considers schema modification (Sch-M) locks when any data definition language (DDL) operation is being performed on a table. SQL Server considers schema stability (Sch-S) locks while compiling queries. An Sch-S lock does not block other locks including the exclusive (X) locks. Hence, other transactions even the transactions with exclusive (X) locks on a table, can run while a query is being compiled.