Locking Architecture

A lock is an object used by software to indicate that a user has some dependency on a resource. The software does not allow other users to perform operations on the resource that would adversely affect the dependencies of the user owning the lock. Locks are managed internally by system software and are acquired and released based on actions taken by the user.

Microsoft® SQL Server™ uses locks to implement pessimistic concurrency control among multiple users performing modifications in a database at the same time. By default, SQL Server manages both transactions and locks on a per connection basis. For example, if an application opens two SQL Server connections, locks acquired by one connection cannot be shared with the other connection. Neither connection can acquire locks that would conflict with locks held by the other connection. Only bound connections are not affected by this rule. For more information, see Using Bound Connections.

SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query; for example one table may be very small and have a table lock applied, while another, larger table may have row locks applied. The level at which locks are applied does not have to be specified by users and needs no configuration by administrators. SQL Server ensures that locks granted at one level of granularity respect locks granted at another level. For example, if UserA attempts to acquire a share lock on a row, SQL Server also attempts to acquire intent share locks on the page and the table. If UserB has an exclusive lock at the page or table level, UserA is blocked from acquiring locks until the lock held by UserB is freed.

There are several lock modes: shared, update, exclusive, intent, and schema. The lock mode indicates the level of dependency the connection has on the locked object. SQL Server controls how the lock modes interact. For example, an exclusive lock cannot be obtained if other connections hold shared locks on the resource.

Locks are held for the length of time needed to protect the resource at the level requested:

• The duration of share locks used to protect reads depends on the transaction isolation levels. At the default transaction isolation level of READ COMMITTED, a share lock is held only as long as it takes to read a page. In scans, the lock is held until a lock is acquired on the next page in a scan. If the HOLDLOCK hint is specified, or the transaction isolation level is set to either REPEATABLE READ or SERIALIZABLE READ, the locks are held to the end of the transaction.
• Depending on the concurrency options set for a cursor, the cursor may acquire shared-mode, scroll locks to protect fetches. When scroll locks needed, they are held until the next fetch or the closing of the cursor, whichever happens first. If HOLDLOCK is specified, however, the scroll locks are held until the end of the transaction.
• Exclusive locks used to protect updates are held until the end of the transaction.

If a connection attempts to acquire a lock that conflicts with a lock held by another connection, the connection attempting to acquire the lock is blocked until:

• The conflicting lock is freed and the connection acquires the lock it requested.
• The time-out interval for the connection expires. By default, there is no time-out interval, but some applications set a time-out interval to prevent an indefinite wait.

If several connections become blocked waiting for conflicting locks on a single resource, the locks are granted on a first-come, first-served basis as the preceding connections free their locks.

SQL Server has an algorithm to detect deadlocks, a conditions where two connections have blocked each other. If SQL Server detects a deadlock, it will terminate one transaction, allowing the other to continue. For more information, see Deadlocking.

SQL Server may dynamically escalate or deescalate the granularity or type of locks. For example, if an update acquires a large number of row locks and has locked a significant percentage of a table, the row locks are escalated to a table lock. If a table lock is acquired, the row locks are released. SQL Server 7.0 rarely needs to escalate locks; the query optimizer usually chooses the correct lock granularity at the time the execution plan is compiled. For more information, see Lock Escalation and Dynamic Locking.

See Also