Acquiring locks
SQL Server can acquire a lock on any of the following resources to ensure that the user of that resource has a consistent view of the data throughout a transaction:
RID A row identifier for the single row to lock within a heap and is acquired when possible to provide the highest possible concurrency.
KEY A key or range of keys in an index for a serializable transaction can be locked in one of two ways depending on the isolation level. If a transaction runs in the
READ COMMITTED or REPEATABLE READ isolation level, the index keys of the accessed rows are locked. If the table has a clustered index, SQL Server acquires key locks instead of row locks because the data rows are the leaf-level of the index. If a transaction runs in the SERIALIZABLE isolation mode, SQL Server acquires key-range locks to prevent phantom reads.
PAGE An 8-kilobyte (KB) data or index page gets locked when a transaction reads all rows on a page or when page-level maintenance, such as updating page pointers after a page-split, is performed.
EXTENT A contiguous block of eight data or index pages gets a shared (S) or exclusive (X) locks typically during space allocation and de-allocation.
HoBT A heap or B-Tree lock can be an entire index or all data pages of a heap.
Table An entire table, including both data and indexes, can be locked for SELECT, UPDATE, or DELETE operations.
File A database file can be locked individually.
Application A resource defined by your application can be locked by using sp_getapplock so that you can lock any resource you want with a specified lock mode.
Metadata Any system metadata can be locked to protect system catalog information.
Allocation unit An database allocation unit used for storage of data can be locked.
Database An entire database gets a shared (S) lock to indicate it is currently in use so that another process cannot drop it, take it offline, or restore it.
To increase concurrency, SQL Server uses dynamic lock management. That is, in a large table for which many row locks are required (as determined by the query optimizer), SQL Server might instead take a page or table lock at the beginning of a transaction. SQL Server can also escalate lock modes dynamically during a transaction. For example, if the transaction initially has a set of row locks, and later requests more row locks, SQL Server releases the row locks and takes a table lock. This behavior simplifies lock management, but reduces concurrency.
Implicit transaction locks: Be aware that when you use implicit transactions, SQL Server holds locks until you commit the transaction. This behavior can reduce concurrency and interfere with truncation of the transaction log.
Example:
Answer:
