By default, the locking granularity in Microsoft SQL Server is a page, which can contain several rows. In SQL Server 6.5, the lock manager has been enhanced to provide row-level locking (IRL) for most INSERT operations. IRL improves performance in situations where access contention and hotspots, which are areas of unusually high access, occur.
While row-level locking is not a substitute for a well-designed application, there are specific scenarios where it is especially useful. Row-level locking is useful when a hotspot develops on tables structured as a sequential file. With SQL Server, hotspots can occur when records are inserted at the end of a table and one of the following conditions exists:
When concurrent users try to insert data into the table's last page and contend for exclusive page access, a hotspot results. To alleviate these performance bottlenecks, enhancements to the lock manager in SQL Server 6.5 provide row-level concurrency for INSERT operations. Properly implemented IRL increases the speed of multiuser INSERT operations.
Note If a clustered index exists on a table, it must be a unique clustered index to take advantage of IRL. (Typically, a unique clustered index is created by default on the column or columns that form the primary key when the primary key is defined.)
For more information about enabling IRL, see sp_tableoption, later in this document.
Two new lock modes provide increased concurrency for IRL.
| Lock mode | Description |
|---|---|
| Insert_page | Multiple transactions can obtain an Insert_page lock on a page while concurrently inserting data. An Insert_page lock is compatible only with other Insert_page locks and is held until the transaction completes. |
| Link_page | A Link_page lock is obtained by the first transaction that detects that the current page is full and that a new page needs to be allocated and linked to the current page. The transaction's Insert_page lock is upgraded to a Link_page lock. Subsequent requests for Insert_page locks are blocked until the transaction owning the Link_page lock completes. |
The following table describes the compatibility of the new types of locks. "Y" means that the requested lock is compatible with the granted lock. "N" means that the requested lock is not compatible with the granted lock.
IRL is OFF by default. IRL can be enabled on individual tables or on an entire database by using the sp_tableoption stored procedure. The following example shows how the database owner can turn on the insert row lock option for all tables in the current database:
EXECUTE sp_tableoption '%.%', 'insert row lock', 'true'
Note The new value for insert row lock takes immediate effect on existing tables in the current database. For each table subsequently created, IRL must be explicitly set.
This example shows how a user other than the system administrator or database owner can turn off the insert row lock option for the sales table:
EXECUTE sp_tableoption 'sales', 'insert row lock', 'false'
For more information about enabling table options, see sp_tableoption, later in this document.
For recovery purposes, additional information must be logged into the transaction log each time a transaction that uses the IRL concurrency option rolls back. This additional information consists of compensating log records, which are used during recovery to bring the database back to a consistent state. To account for a potential rollback operation, SQL Server reserves enough space in the transaction log for all concurrent IRL operations.
Important If you intend to use IRL, and your application performs many concurrent INSERT operations, you should monitor the size of the log using the Windows NT Performance Monitor. Increase the size of the log space or dump the transaction log more frequently if your database is running low.
Reducing the locking granularity can result in deadlocks in some applications. It is possible for existing applications to encounter deadlocks if IRL is implemented without adequate testing. For example, consider a transaction that inserts a row on a page and then updates the row within the same transaction. For the INSERT operation, the transaction must acquire an Insert_page lock, and for the subsequent UPDATE operation, it needs an exclusive page lock. If there are several such transactions running concurrently, each transaction can have its own Insert_page lock on the page. When attempting to update the page, each transaction requests an exclusive page lock, which blocks on the Insert_page lock of another transaction and results in a deadlock. To achieve maximum benefit from IRL and to avoid deadlocks, it is recommended that you extensively test existing applications before changing to this new locking level.