Dynamic Locking

SQL Server 7.0 has a superior locking mechanism that is unusual in the database industry: dynamic locking. At run time, the storage engine dynamically cooperates with the query processor to choose the lowest-cost locking strategy based on the characteristics of the schema and query.

Dynamic locking has these advantages:

Simplified database administration, because database administrators no longer need to be concerned with adjusting lock-escalation thresholds
Increased performance, because SQL Server minimizes system overhead by using locks appropriate to the task
Concentrated development by application developers, because SQL Server adjusts locking automatically

Dynamic locking allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, is expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. However, it has a lower overhead because fewer locks are being maintained.

SQL Server can dynamically lock the following resources (thereby increasing granularity).

Resource	Description
RID	A row identifier. Used to lock a single row within a table.
Key	A row lock within an index. Used to protect key ranges in serializable transactions.
Page	An 8-KB data page or index page.
Extent	A contiguous group of eight data pages or index pages.
Table	An entire table, including all data and indexes.
DB	A database.