Dynamic Locking

Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time. Using low-level locks also increases the number of locks and the resources needed to manage them. Using high-level table or page locks lowers overhead, but at the expense of lowering concurrency.

The Microsoft SQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

Dynamic locking has the following advantages:

• Simplified database administration. Database administrators do not have to adjust lock escalation thresholds.

• Increased performance. The Database Engine minimizes system overhead by using locks appropriate to the task.

• Application developers can concentrate on development. The Database Engine adjusts locking automatically.

In SQL Server 2008, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option. For more information, see the LOCK_ESCALATION option of ALTER TABLE.