SQL Server 6.5 Introduces Insert Row Locking

Arguments over row- vs. page-level locking take on near religious importancein some database circles. Over the years, row locking has been one of the mostrequested enhancements to SQL Server from developers and has provided ammunitionfor the Oracle marketing machine because Oracle's product has row-level lockingand Microsoft's didn't--until now. In SQL Server 6.5, Microsoft introducesInsert Row Locking (IRL), which is a first step toward a more robust lock model.This article explains what IRL can and can't do, defines the major problems itsolves today, and points out some gotchas to avoid.

Why does a database need locks at all? The answer is simple: Locks provideconsistent and concurrent access to data in a multiuser environment. Locksprevent simultaneous updates of the same data. With locks, you can be sure thatwhile your airline agent is selling you that aisle seat, another agent isn'tpromising the same seat to someone else. Lose the locks, and airline agents cansell the same seat to more than one person. Yes, yes, I know: They do thatanyway. But those mistakes are not the database's fault.

Tradition Rules
In the beginning was the page, and Bill said, "Let the page be thesmallest unit of data that can be locked." (Actually, it was the engineersat Sybase who said that, but that's another story.) The page, 2KB in SQL Server,was the lowest level of lock granularity in SQL Server. But, one row, such as acustomer record, is always less than 2KB (because a row must fit on one page),so a page usually contains many rows. With page locking, Joe cannot increase thecredit limit on the "Smith" account if Mary is updating the "Smithe"account at the same time and these records are stored on the same page. Rowlocks solve the problem by providing a finer level of granularity. With rowlocking, you can edit Smith and Smithe at the same time because individual rowsare locked rather than the whole page.

In the past, a transaction obtained an exclusive page lock when it needed toinsert a row, which made the page off-limits to other transactions. Thetransaction held this exclusive lock for the life of the transaction (everythingbetween a BEGIN TRAN and COMMIT or ROLLBACK TRAN) and blocked all other attemptsto access the page because competing transactions cannot hold exclusive locks atthe same time.

With IRL, SQL Server provides logical row locking for most insertoperations. I call IRL logical row locking for those people who arguethat it's not true row-level locking because SQL Server implements IRL with twonew page locks called insert_page (IX) and link_page (LN). IRL lets differenttransactions obtain insert_page locks at the same time, meaning multipletransactions can insert rows concurrently on the same page without blocking eachother.

Insert_page locks are compatible with each other in that many transactionscan hold locks on the same page simultaneously. You obtain a link_page when onetransaction detects that the current page is full and identifies the need toallocate and link a new page to the table's page chain. During a link, SQLServer temporarily upgrades the insert_page lock to a link_page, which blockssubsequent insert_page requests until the transaction owning the link_pagecompletes. IRL works only with inserts because insert_page and link_page areincompatible with all other lock types. (In case you're wondering, IRL does notwork with inserts that occur during a deferred update. Check SQL Server BooksOnline--BOL--documentation if you're not familiar with when an update isactually performed as a delete followed by an insert.) Exclusive and sharedlocks are necessary for managing selects, updates, and deletes. Figure 1summarizes the compatibility of lock types in SQL Server 6.5.

IRL Tackles the Hot Page Problem
One of the most common concurrency issues SQL Server designers face is thehot page insert problem. A hot page occurs when multiple users try toinsert data on the same page at the same time, creating a bottleneck with thepotential to cripple OnLine Transaction Processing (OLTP) applications thatrequire high concurrency levels. Here's how a hot page occurs and how IRL solvesthe problem.

A table is a linked list of 2KB pages with no explicit sort order, unless aclustered index exists. The last page in the chain is like a bucket, and all newrows go in it. SQL Server adds new pages to the end of the chain when thecurrent bucket fills up. When two people attempt to insert data at the same timewhen no index exists on a table, they need access to the last page. But only oneof them can grab the lock, so the other one waits. The last page becomes aserious bottleneck as more users are added and contention for this page becomesmore severe.

You can add a clustered index to force a sort order on the underlyingtable. SQL Server inserts data in its sorted location rather than on the lastpage in the data chain, eliminating the problem of everyone vying for access toone page. People have mistakenly thought this tactic would solve the problementirely. Unfortunately this solution doesn't always do the trick. What happensif the clustered index happens to be on a primary key using the Identityproperty to assign key values? (primary keys create a unique index on the key,and you can use Identity to auto-generate ascending key values similar to thecounter data type in Microsoft Access.) The clustered index inserts data in sortedorder. But the counter behavior of Identity means that sorted order isalways on the last page, reintroducing the problem we were trying to solve inthe first place.

So, you put a clustered index on every table and never use it with anIdentity column. This solution eliminates contention in the data pages, but whathappens when you add a non-clustered (NC) index on a column defined with anIdentity property? This method introduces a hot page in the leaf level of the NCindex because SQL Server inserts a row on the last page in the leaf level of theNC index every time you add a new data row. No matter how you slice it, youintroduce a hot page whenever you use an index with an Identity column.Sometimes contention occurs on the data pages, and other times it occurs onindex pages. Either way produces a limitation I'd rather do without.

IRL eliminates this particular hot page problem by implementing row-levelconcurrency for insert operations. You don't get a hot spot because insertsdon't block each other in either the data or index pages. Microsoft benchmarksshow that IRL offers massive performance improvements of up to 40 percent forapplications that are highly concurrent, but it provides little or no benefit tolow concurrency applications.

Use IRL with Care
IRL solves important problems, but you must keep a few gotchas in mind.Don't enable IRL in a production application without extensive testing, or thegotchas will cause more problems than IRL solves.

The first point to keep in mind is that IRL works only for inserts. Youstill need to deal with all the old concurrency issues that occur with updates,deletes, and selects. Second, if a clustered index exists, you must declare itunique, or IRL will not be available for the table. I'm not sure why thislimitation exists, but it does.

Third, SQL Server 6.5 disables IRL by default, and you can turn it on forspecific tables with sp_tableoption. The procedure calls dbcc rowlock, whichtoggles the sysobjects.userstat 0x01 bit, and takes effect immediately. You donot need to stop and start SQL Server. For example, you can enter

sp_tableoption @TableNamePattern
[, '@OptionName']
[, '@OptionValue']

@TableNamePattern selects the qualified or non-qualified name of a usertable (with or without database and owner specifications) and accepts any stringpattern appropriate for use with the like operator, so you can affect manytables at once. The following command enables IRL for all tables in the currentdatabase.

EXECUTE sp_tableoption '%.%', 'insert row lock','true'

In addition, IRL can introduce deadlock situations because it lets sometransactions that used to occur sequentially now occur concurrently. A deadlockoccurs when two users have locks on separate objects and each user wants a lockon the other's object. Each user waits for the other to release their lock,neither process can finish, and SQL Server kills one of the processes. "PreventingDeadlocks and Lock Starvation" and "Insert Row Locking" in SQLServer BOL tell you how to avoid deadlocks in the first place.

Also, IRL can double the size of your transaction log during rollbackoperations. Microsoft did some clever programming to provide IRL without havingto make significant changes to lock and log managers that were originallywritten to support page-based data access. SQL Server writes extra informationto the log during a rollback (called compensatory records by Microsoft) to avoidthe data inconsistencies that can occur when using page-based technology tohandle row-level data access. My informal testing shows that rollbacks createapproximately twice the number of log records when IRL is on. Fortunately,COMMITS don't require extra space and tend to occur more frequently thanrollbacks.

IRL Is Only the Beginning
IRL isn't a magic elixir for your row-locking blues, but it solves somereal-world problems such as insert hot pages, and is the first step toward theflexible dynamic locking model that Microsoft hopes to implement in SQLServer 7.0. Dynamic locking mixes the best of page and row locking by startingout with coarse locks (i.e., a page) and de-escalating to finer granularitylocks (i.e., a row) when contention is detected. Remember, IRL is better than norow locks at all until dynamic locking is more than a white paper. Check outhttp://www.microsoft.com/sql/locking.htm for more information about Microsoft'sDynamic Locking Initiative.