Dynamic Locking

Although row level locks increase concurrency, it is at the cost of system overhead. Table or page locks lower overhead but at the expense of lowering concurrency.

Microsoft® SQL Server™ uses a dynamic locking strategy to determine the most cost-effective locks. SQL Server 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, because database administrators no longer have to be concerned with adjusting lock escalation thresholds.
• Increased performance, because SQL Server minimizes system overhead by using locks appropriate to the task.
• Application developers can concentrate on development, because SQL Server automatically adjusts locking.