Microsoft SQL Server 2012 Bible

Understanding SQL Server Locking

SQL Server implements the I (Isolation) portion of the ACID property via locks. Locks ensure that the data modified by one transaction is protected from data modified in a different transaction. There are a series of different levels for locking data. Before these locks can be controlled, they must be understood. SQL Server offers several methods to control locks.

Lock Granularity

A portion of the data controlled by a lock can vary from only a row to the entire database, as shown in Table 47.1. Several combinations of locks, depending on the lock granularity, can satisfy a locking requirement.

Table 47.1 Lock Granularity

Lock Size	Description
Row Lock	Locks a single row. This is the smallest lock available. SQL Server does not lock columns.
Page Lock	Locks a page, or 8KB. One or more rows may exist on a single page.
Extent Lock	Locks eight pages, or 64KB.
Table Lock	Locks the entire table.
Database Lock	Locks the entire database. This lock is used primarily during schema changes.
Key Lock	Locks nodes on an index.

For best performance, the SQL Server lock manager tries to balance the size of the lock against the number of locks. The struggle is between concurrency (smaller locks allow more transactions to access the data) and resource usage. (Fewer locks are faster because each lock requires memory in the system to hold the information about the lock.)

SQL Server automatically manages the granularity of locks by trying to keep the lock ...