Microsoft® SQL Server 2012 Unleashed

Transactions and Locking

SQL Server issues and holds on to locks for the duration of a transaction to ensure the isolation and consistency of the modifications. Data modifications that occur within a transaction acquire exclusive locks, which are then held until the completion of the transaction. Shared locks, or read locks, are held for only as long as the statement needs them; usually, a shared lock is released as soon as data has been read from the resource (for example, row, page, table). You can modify the length of time a shared lock is held by using keywords such as HOLDLOCK in a query or setting the REPEATABLE_READ or SERIALIZABLE lock isolation levels. If one of these options is specified, shared locks are held until the completion ...