Locks are held on pages that are read or modified during a transaction to prevent problems that might arise from concurrent use of resources by multiple transactions. Minimizing locks increases concurrency, which can improve performance results. Although there are many ways to minimize locks, one method is to commit transactions as soon as you have completed them. (For information about concurrency, see the Microsoft SQL Server Database Developer's Companion.)
SQL Server can hold several types of locks. In general, read operations acquire shared locks, and write operations acquire exclusive locks. Update locks are created at the page level and are acquired during the initial portion of an update operation when the pages are being read or by cursors opened with pessimistic concurrency. Update locks are compatible with shared locks. Later, if the pages are changed, the update locks are promoted to exclusive locks.
SQL Server locks can be held on a table (table or intent), a page, or an extent. SQL Server tries to satisfy requests with page locks before acquiring table locks. However, if many page locks are held during the course of a transaction, SQL Server upgrades the lock to a table lock (shared or exclusive). The point at which escalation occurs is configurable with sp_configure. SQL Server obtains table locks when it suspects that a transaction might affect an entire table. Table locks provide a way of avoiding lock collisions at the page level.
An intent lock indicates the intention to acquire a shared or exclusive lock on a data page. (An intent lock prevents another transaction from acquiring an exclusive lock on the table containing that page.) An extent lock is held on a group of eight database pages while they are being allocated or freed. Extent locks are set while a CREATE or DROP statement is running or while an INSERT statement that requires new data or index pages is running.
In addition, some SQL Server locks are considered blocking locks. A blocking lock is a lock that blocks another process that needs to acquire a lock. As soon as the process that is causing the block finishes, the other process can move forward.
For more information about locks, see the discussion about monitoring database integrity and optimizing databases in the Microsoft SQL Server Database Developer's Companion.
The following statistics are included in the SQLServer-Locks object: