Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

The SQL Server query optimizer usually chooses the correct isolation level, including the type of lock and the lock mode. You should override this behavior only if thorough testing has shown that a different approach is preferable. Keep in mind that by setting an isolation level, you will have an impact on the locks that will held, the conflicts that will cause blocking, and the duration of your locks. Your isolation level is in effect for an entire session, and you should choose the one that provides the data consistency required by your application. Table-level locking hints can be used to change the default locking behavior only when necessary. Disallowing a locking level can adversely affect concurrency.

Lock Hints

Transact-SQL syntax allows you to specify locking hints for individual tables when they are referenced in SELECT, INSERT, UPDATE, and DELETE statements. The hints tell SQL Server the type of locking or row versioning to use for a particular table in a particular query. Because these hints are specified in a FROM clause, they are called table-level hints. Books Online lists other table-level hints besides locking hints, but the vast majority of them affect locking behavior. They should be used only when you absolutely need finer control over locking at the object level than what is provided by your session's isolation level. The SQL Server locking hints can override the current transaction isolation level for the session. In this section, I will mention only some of the locking hints that you might need in order to obtain the desired concurrency behavior. A more complete discussion of how locking hints can be used to tune queries and applications can be found in Inside Microsoft SQL Server 2005: Query Tuning and Optimization.

Many of the locking hints work only in the context of a transaction. However, every INSERT, UPDATE, and DELETE statement is automatically in a transaction, so the only concern is when you use a locking hint with a SELECT statement. To get the benefit of most of the following hints when used in a SELECT query, you must use BEGIN TRAN/COMMIT (or ROLLBACK) TRAN blocks. The lock hint syntax is as follows:

SELECT select_list FROM object [WITH (locking hint)] DELETE [FROM] object [WITH (locking hint) [WHERE <search conditions>] UDPATE object [WITH (locking hint) SET <set_clause> [WHERE <search conditions>] INSERT [INTO] object [WITH (locking hint) <insert specification>

Tip

You can specify one of the following keywords for locking hint:

• HOLDLOCK This is equivalent to the following SERIALIZABLE hint. This option is similar to specifying SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, except that the SET option affects all tables, not only the one specified in this hint.

• UPDLOCK This takes update page locks instead of shared page locks while reading the table and holds them until the end of the transaction. Taking update locks can be an important technique for eliminating conversion deadlocks.

• TABLOCK This takes a shared lock on the table even if page locks would be taken otherwise. This option is useful when you know you'll escalate to a table lock or if you need to get a complete snapshot of a table. You can use this option with HOLDLOCK if you want the table lock held until the end of the transaction block (REPEATABLE READ). If you use this hint with a DELETE statement on a heap, it allows SQL Server to deallocate the pages as the rows are deleted. (If row or page locks are obtained when deleting from a heap, space will not be deallocated and cannot be reused by other objects.)

• PAGLOCK This keyword takes shared page locks when a single shared table lock might otherwise be taken. (To request an exclusive page lock, you must use the XLOCK hint along with the PAGLOCK hint.)

• TABLOCKX This takes an exclusive lock on the table that is held until the end of the transaction block. (All exclusive locks are held until the end of a transaction, regardless of the isolation level in effect. This hint has the same effect as specifying both the TABLOCK and the XLOCK hints together.)

• ROWLOCK This specifies that a shared row lock be taken when a single shared page or table lock is normally taken.

• READUNCOMMITTED | REPEATABLEREAD | SERIALIZABLE These hints specify that SQL Server should use the same locking mechanisms as when the transaction isolation level is set to the level of the same name. However, the hint controls locking for a single table in a single statement, as opposed to locking of all tables in all statements in a transaction.

• READCOMMITTED This hint specifies that SELECT operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. If the database option READ_COMITTED_SNAPSHOT is OFF, SQL Server uses shared locks and releases them as soon as the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, SQL Server does not acquire locks and uses row versioning.

• READCOMMITTEDLOCK This hint specifies that SELECT statements use the locking version of READCOMMITTED isolation (the SQL Server default). No matter what the setting is for the database option READ_COMMITTED_SNAPSHOT, SQL Server acquires shared locks when it reads the data and releases those locks when the read operation is completed.

• NOLOCK This allows uncommitted, or dirty, reads. Shared locks are not issued by the scan, and the exclusive locks of others are not honored. This hint is equivalent to READUNCOMMITTED.

• READPAST This specifies that locked rows are skipped (read past). READPAST applies only to transactions operating at the READ COMMITTED isolation level and reads past row-level locks only.

• XLOCK This hint specifies that SQL Server should take an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the specified resource.

Setting a Lock Timeout

Setting a LOCK_TIMEOUT also lets you control SQL Server locking behavior. By default, SQL Server does not time out when waiting for a lock; it assumes optimistically that the lock will be released eventually. Most client programming interfaces allow you to set a general timeout limit for the connection so a query is automatically canceled by the client if no response comes back after a specified amount of time. However, the message that comes back when the time period is exceeded does not indicate the cause of the cancellation; it could be because of a lock not being released, it could be because of a slow network, or it could just be a long-running query.

Like other SET options, SET LOCK_TIMEOUT is valid only for your current connection. Its value is expressed in milliseconds and can be accessed by using the system function @@LOCK_TIMEOUT. This example sets the LOCK_TIMEOUT value to 5 seconds and then retrieves that value for display:

SET LOCK_TIMEOUT 5000; SELECT @@LOCK_TIMEOUT;

If your connection exceeds the lock timeout value, you receive the following error message:

Server: Msg 1222, Level 16, State 50, Line 1 Lock request time out period exceeded.

Setting the LOCK_TIMEOUT value to 0 means that SQL Server does not wait at all for locks. It basically cancels the entire statement and goes on to the next one in the batch. This is not the same as the READPAST hint, which skips individual rows.

The following example illustrates the difference between READPAST, READUNCOMMITTED, and setting LOCK_TIMEOUT to 0. All these techniques let you "get around" locking problems, but the behavior is slightly different in each case.

1. In a new query window, execute the following batch to lock one row in the titles table:

   USE AdventureWorks; BEGIN TRAN; UPDATE HumanResources.Department SET ModifiedDate = getdate(); WHERE DepartmentID = 1;

2. Open a second connection, and execute the following statements:

   USE AdventureWorks; SET LOCK_TIMEOUT 0; SELECT * FROM HumanResources.Department; SELECT * FROM Sales.SalesPerson;

   Notice that after error 1222 is received, the second SELECT statement is executed, returning all 17 rows from the SalesPerson table. The batch is not cancelled when error 1222 is encountered.

   Warning

   Not only is a batch not cancelled when a lock timeout error is encountered, but any active transaction will not be rolled back. If you have two UPDATE statements in a transaction and both must succeed if either succeeds, a timeout for one of the UPDATE statements will still allow the other statement to be processed. You must include error checking in your batch to take appropriate action in the event of an error 1222.

3. Open a third connection, and execute the following statements:

   USE AdventureWorks; SELECT * FROM HumanResources.Department (READPAST); SELECT * FROM Sales.SalesPerson;

   SQL Server skips (reads past) only one row, and the remaining 15 rows of Department are returned, followed by all the SalesPerson rows. The READPAST hint is frequently used in conjunction with a TOP clause, in particular TOP 1, where your table is serving as a work queue. Your SELECT must get a row containing an order to be processed, but it really doesn't matter which row. So SELECT TOP 1 * FROM <OrderTable> will return the first unlocked row, and you can use that as the row to start processing.

4. Open a fourth connection, and execute the following statements:

   USE AdventureWorks; SELECT * FROM HumanResources.Department (READUNCOMMITTED); SELECT * FROM Sales.SalesPerson;

   In this case, SQL Server does not skip anything. It reads all 16 rows from Department, but the row for Department 1 shows the dirty data that you changed in step 1. This data has not yet been committed and is subject to being rolled back.

The READUNCOMMITTED hint is probably the least useful hint in SQL Server 2005 because of the availability of row versioning. In fact, any time you find yourself needing to use this hint, or the equivalent NOLOCK, you should consider whether you can actually afford the cost of one of the snapshot-based isolation levels.