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.
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.
|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 ...