Microsoft® SQL Server 2008 R2 Unleashed

SQL Server Lock Granularity

Lock granularity is essentially the amount of data locked as part of a query or update to provide complete isolation and serialization for the transaction. The Lock Manager needs to balance the concurrent access to resources versus the overhead of maintaining a large number of lower-level locks. For example, the smaller the lock size, the greater the number of concurrent users who can access the same table at the same time but the greater the overhead in maintaining those locks. The greater the lock size, the less overhead required to manage the locks, but concurrency is also less. Figure 37.6 demonstrates the trade-offs between lock size and concurrency.

Figure 37.6 Trade-offs between performance and concurrency, ...