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.
.gif "Diagram showing cost versus granularity")
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.
See Also
Concepts
Lock Granularity and Hierarchies
Lock Modes