Inside SQL Server: Controlling Locking

In the July and August issues, I discussed the locking mechanisms SQL Server 7.0 uses to protect the logical consistency of your data during transaction processing. SQL Server takes many factors into account when determining the amount of data to lock (rows, pages, or the entire table). These factors include the transaction isolation level you're operating in, the number of rows your query will process, the number of rows that fit on a page, the number of rows in the table, and whether you're retrieving or updating data. In most cases, SQL Server determines the best locking mechanism for your situation, so you can usually leave the locking decisions to SQL Server. However, in certain situations, you might need to give SQL Server some direction. In this column, I cover the options for controlling locking and how to use each of them. I also point out some places where Microsoft's online documentation is incomplete or inaccurate.

Related: SQL Server 7.0 Locking

Transaction Isolation Levels

One of the most straightforward ways of controlling locking behavior is setting your transaction isolation level. (See Inside SQL Server, "Transaction Isolation Levels," June 1999, for a discussion of the four levels specified by the ANSI SQL Committee and transaction behavior with each isolation level value.) SQL Server's default isolation level is READ COMMITTED, in which SQL Server holds shared locks only until the data has been read, and holds exclusive locks until the end of the transaction. A transaction operating in READ UNCOMMITTED isolation level takes no locks and ignores locks other transactions hold. A transaction operating in REPEATABLE READ isolation level keeps shared locks and exclusive locks until the end of the transaction. The most restrictive isolation level, SERIALIZABLE, locks data ranges the transaction specifies and holds all locks until the end of the transaction, so that users can't insert new rows into those ranges.

Changing your transaction isolation level comes at a cost. Although you can guarantee predictable behavior by keeping data locked for the duration of the transaction, other transactions will block and system throughput can decrease. The downside of choosing the less-restrictive READ UNCOMMITTED is that SQL Server can read data that might later be rolled back. All SET options, including those for changing the transaction isolation level, are in effect for the entire session and apply to all batches and transactions you execute in that session. To control locking on a smaller scale, you can use a table hint to apply any of the transaction isolation levels to any or all tables in a query.

Table Hints

The word hint, which the SQL Server 7.0 documentation uses, might sound like merely a suggestion. However, except in cases where the hint makes no sense and is ignored, hints act more like orders and SQL Server uses the locking strategy your table hints specify. The SQL Server 7.0 documentation lists 14 table hints. The 12 hints in Table 1 control the locking behavior that SQL Server uses on the table. The other two hints control SQL Server's choice of indexes for processing the query, so I won't discuss them this time.

The documentation generally describes these hints well, so I won't discuss their behavior in detail. The HOLDLOCK hint is available for backward compatibility only and is equivalent to the SERIALIZABLE hint (which is preferable in SQL Server 7.0). NOLOCK is equivalent to the preferred hint, READUNCOMMITTED. The four hints READUNCOMMITTED, READCOMMITTED, REPEATABLEREAD, and SERIALIZABLE mimic the behavior of the four isolation levels, but apply only to one table in one query.

Four hints control the unit of locking: ROWLOCK, PAGLOCK, TABLOCK, and TABLOCKX. The documentation says that the ROWLOCK, PAGLOCK, and TABLOCK hints apply only to shared locks, but this claim isn't true. If you're modifying data, SQL Server needs to acquire an exclusive lock, and you can't override that lock type with a table hint. If you force page locks with a table hint, SQL Server can take either shared or exclusive page locks, depending on the operation you're performing. Listing 1 shows a SQL Server 7.0 transaction. Without table hints, this transaction would acquire exclusive row locks on every row in the authors table. If you run the query without the WITH (PAGLOCK) hint, you see many KEY locks, which are just row locks in an index. The authors table has a clustered index, and data rows in a table with a clustered index are the leaf level of that index. Figure 1 shows the output of the script in Listing 1. You can see that an exclusive page lock is taken for the update, which updates every row in the table. Note that Listing 1 shows a parameter of the function @@spid for the sp_lock procedure, which shows you only the locks from your connection.

The documentation for the PAGLOCK hint also errs in saying that PAGLOCK acquires a page lock only in cases where SQL Server would usually acquire a table lock. As you can prove to yourself by removing the hint, the default behavior for the update in Listing 1 is row locks, which the PAGLOCK hint overrides. Also, you can use the ROWLOCK hint to force row- or key-level locks; those locks can be either exclusive or shared, depending on whether you're retrieving or modifying data. Although the TABLOCK hint has a counterpart to force an exclusive lock, using TABLOCK in an update query also results in an exclusive lock.

The READPAST hint has no equivalent in SQL Server releases before 7.0. READPAST lets your transaction skip locked rows; earlier releases didn't lock individual rows. If one connection has one or more rows locked, a query running with the READPAST hint ignores a locked row and moves to the next one. Listing 2 shows two connections. The first updates the row for publisher 9901 in the publishers table. The second connection tries to read the entire publishers table with the READPAST hint. You can see in the output in Figure 2 that the entire table is returned except for the locked row for publisher 9901.

Setting LOCK_TIMEOUT

In SQL Server 6.x, you have no way to tell SQL Server to stop waiting for a lock. SQL Server operates as though the locked data will eventually be released. You can control a connection timeout setting through many client tools, including ODBC and the ISQL/W tool, but a connection timeout setting merely tells the client to cancel the query if SQL Server hasn't returned results in a specified amount of time. The client doesn't know why the results haven't come back. The cause might be a lock, network problems, or a long-running query.

Besides the READPAST hint, SQL Server 7.0 has a new SET option, which gives you another way to skip locked data. The option, LOCK_TIMEOUT, tells SQL Server not to wait more than a specified number of milliseconds for a lock to be released. Setting LOCK_TIMEOUT to zero means that SQL Server won't wait at all if it finds any locked data. The documentation for LOCK_TIMEOUT implies that it's interchangeable with READPAST, but there's a crucial difference. READPAST, as in Figure 2, skips individual locked rows. Setting LOCK_TIMEOUT to zero causes SQL Server to stop processing the statement when it finds a locked row. Listing 3 shows the same two connections as in Listing 2, but instead of using the READPAST hint, Connection 2 sets the LOCK_TIMEOUT value to zero. (Before you run these queries, be sure to roll back the transaction from Listing 2 or close the query window to release the locks.) Figure 3 shows that only five rows are returned—the five rows before the locked row for publisher 9901—and you get error message 1222. Then the next statement in the batch executes. That statement returns the value of the system function @@lock_timeout, which shows the current setting of the LOCK_TIMEOUT option.

Earlier releases of SQL Server include the NOLOCK hint, which lets SQL Server read uncommitted data so it doesn't block on a lock. If you run the NOLOCK hint with the query in Connection 2, you get all eight rows of the publishers table, including the uncommitted city value for publisher 9901.

Although the LOCK_TIMEOUT setting might be just what you've been waiting for, use it with extreme caution. This error doesn't automatically roll back a transaction. So if SQL Server reaches its lock timeout value, it stops trying to modify rows in the current table and moves on to the next statement. Instead of the transaction being an atomic, all-or-nothing operation, you might be left with part of the transaction incompletely executed. If you want the transaction to be all-or-nothing, you need to include a specific test for error 1222, and include an explicit ROLLBACK TRANSACTION as the action to perform when the error is encountered. You might consider adding this test to all data-modification statements in applications that adjust the LOCK_TIMEOUT value. This test is the only way to guarantee that the transactions maintain their consistency.

Proceed with Caution

The new locking mechanisms in SQL Server 7.0 give your applications more flexibility. In general, let SQL Server decide which type and granularity of locking is most appropriate, and minimize your use of locking hints. As I've shown, several caveats are attached to hint use, along with the risk of unexpected, inconsistent behavior. However, in cases where you've analyzed the locking behavior and you understand why you need a particular strategy, you might choose to override the default behavior. Just make sure to test your applications thoroughly before using a locking hint in production.