With careful planning and a good understanding of the type and quantity of transactions that make up the database throughput, database designers can work around most major areas of contention. Now, with insert row-level locking (IRL), it is easier to achieve excellent performance for multiuser inserts. Insert row-level locking allows the individual rows on a page to be simultaneously locked by multiple transactions that insert new rows.
The lock manager has been enhanced to provide row-level locking for most INSERT operations. IRL improves performance in situations where access contention and hotspots, which are areas of unusually high access, occur.
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 provide row-level concurrency for INSERT operations. Properly implemented IRL increases the speed of multiuser INSERT operations. For more information on IRL, see SQL Server 6.5 Books Online.
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.)
The following diagrams illustrate how the type of index used on a table affects the amount of contention that occurs when new rows are inserted.
Note In these diagrams, the padlock sign represents a row-level lock from a transaction denoted by Tn.
Tables with nonclustered indexes on monotonically increasing keys experience contention at the leaf level of the index. The index benefits from insert-row level locking.
Tables without clustered indexes or with clustered indexes on monotonically increasing keys experience more contention on the last page, and therefore they benefit from insert row-level locking. This diagram represents a table with a clustered index on a monotonically increasing key.
The following diagram represents a table with a nonclustered index on a random key.
The following diagram represents a table with a clustered index on a random key.
Page-level locking is used for deletes and updates and is also used for inserts by default, unless insert row-level locking has been specified. When a transaction needs to update or delete a row on the page with a locked index row, the lock is escalated to an exclusive page-level lock.