Row-Level vs. Page-Level Locking

The debate over whether row-level locking is better than page-level locking or vice-versa has been one of those near-religious wars and warrants a few comments here. Although some people would have you believe that one is always better, it's not really that simple.

Prior to version 7, the smallest unit of data that SQL Server could lock was a page. Even though many people argued that this was unacceptable and it was impossible to maintain good concurrency while locking entire pages, many large and powerful applications were written and deployed using only page-level locking. If they were well designed and tuned, concurrency was not an issue, and some of these applications supported hundreds of active user connections with acceptable response times and throughput. However, with the change in page size from 2 KB to 8 KB for SQL Server 7, the issue has become more critical. Locking an entire page means locking four times as much data as in previous versions. Beginning with version 7, SQL Server implements full row-level locking, so any potential problems due to lower concurrency with the larger page size should not be an issue. However, locking isn't free. Considerable resources are required to manage locks. Recall that a lock is an in-memory structure of about 32 bytes, with another 32 bytes for each process holding the lock and each process waiting for the lock. If you need a lock for every row and you scan a million rows, you need more than 30 MB of RAM just to hold locks for that one process.

Beyond the memory consumption issues, locking is a fairly processing-intensive operation. Managing locks requires substantial bookkeeping. Recall that, internally, SQL Server uses a lightweight mutex called a spinlock to guard resources, and it uses latches—also lighter than full-blown locks—to protect non-leaf-level index pages. These performance optimizations avoid the overhead of full locking. If a page of data contains 50 rows of data, all of which will be used, it is obviously more efficient to issue and manage one lock on the page than to manage 50. That's the obvious benefit of page locking—a reduction in the number of lock structures that must exist and be managed.

If two different processes each need to update a few separate rows of data and some of the rows needed by each process happen to exist on the same page, one process must wait until the page locks of the other process are released. If, in this case, you use row-level locking instead of page-level locking, the other process does not have to wait. The finer granularity of the locks means that no conflict occurs in the first place because each process is concerned with different rows. That's the obvious benefit of row-level locking. Which of these obvious benefits wins? Well, the decision isn't clear cut, and it depends on the application and the data. Each type of locking can be shown to be superior for different types of applications and usage.

The stored procedure sp_indexoption lets you manually control the unit of locking within an index. It also lets you disallow page locks or row locks within an index. Since these options are available only for indexes, there is no way to control the locking within the data pages of a heap. (But remember that if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption setting.) The index options are set for each table or index individually. Two options, AllowRowLocks and AllowPageLocks, are both set to TRUE initially for every table and index. If both of these options are set to FALSE for a table, only full table locks are allowed.

As mentioned earlier, SQL Server determines at runtime whether to initially lock rows, pages, or the entire table. The locking of rows (or keys) is heavily favored. The type of locking chosen is based on the number of rows and pages to be scanned, the number of rows on a page, the isolation level in effect, the update activity going on, the number of users on the system needing memory for their own purposes, and so on.

Lock Escalation

SQL Server automatically escalates row, key, or page locks to coarser table locks as appropriate. This escalation protects system resources—it prevents the system from using too much memory for keeping track of locks—and increases efficiency. For example, after a query acquires many row locks, the lock level can be escalated to a table lock. If every row in a table must be visited, it probably makes more sense to acquire and hold a single table lock than to hold many row locks. A single table lock is acquired and the many row locks are released. This escalation to a table lock reduces locking overhead and keeps the system from running out of locks. Because a finite amount of memory is available for the lock structures, escalation is sometimes necessary to make sure the memory for locks stays within reasonable limits.

When the lock count for one transaction exceeds 1250 or when the lock count for one index or table scan exceeds 765, the lock manager looks to see how much memory is being used for all locks in the system. If more than 40 percent of the memory pool is being used for locks, SQL Server attempts to escalate multiple page, key, or RID locks into a table lock. SQL Server tries to find a table that is partially locked by the transaction and holds the largest number of locks for which no escalation has already been performed, and which is capable of escalation. Multiple RID, key, or page locks cannot be escalated to a table lock if some other processes hold incompatible locks on other RIDs, keys, or pages of the same table. SQL Server will keep looking for other tables partially locked by the same transaction until all possible escalations have taken place or the total memory used for locks drops under 40 percent. Note that SQL Server never escalates to page locks; the result of a lock escalation is always a table lock.