As mentioned previously in this chapter in the "Transaction Isolation Levels in SQL Server" section, you can set an isolation level for your connection using the SET TRANSACTION ISOLATION LEVEL command. This command sets a global isolation level for your entire session, which is useful if you want to provide a consistent isolation level for your application. However, sometimes you will want to specify different isolation levels for different queries in the system, or different isolation levels for different tables within a single query. SQL Server allows you to do this by supporting table hints in the FROM clause of SELECT, UPDATE, and DELETE statements. This allows you to override the isolation level that is currently set at the session level.
In this chapter, you have seen that locking is dynamic and automatic in SQL Server. Based on certain factors (such as SARGs, key distribution, data volume, and so on), the query optimizer chooses the granularity of the lock (row, page, or table level) on a resource. Although it is usually best to leave such decisions to the cost-based optimizer, you might encounter certain situations in which you want to force a different lock granularity on a resource than what the optimizer has chosen. SQL Server provides additional table hints that you can use in the query to force lock granularity for various tables that are participating in a join.
SQL Server also automatically determines the lock type (SHARED, UPDATE, EXCLUSIVE) to use on a resource depending on the type of command being executed on the resource. For example, a SELECT statement will use a shared lock. SQL Server 2000 also provides additional table hints to override the default lock type.
The table hints to override the lock isolation, granularity, or lock type for a table can be provided in the FROM clause of the query by using the WITH operator. The following is the syntax of the FROM clause when using table hints:
FROM table_name [ [AS] table_alias ] WITH ( table_hint [ ,...n ] ) [, ...n]
The following sections discuss the various locking hints that can be passed to an optimizer to manage isolation levels and the lock granularity of a query.
Although many of the table-locking hints can be combined, you cannot combine more than one hint at a time on a table from the granularity and isolation level hints. Also, the NOLOCK, READUNCOMMITTED, and READPAST hints described in the following sections cannot be used on tables that are the target of INSERT, UPDATE, or DELETE queries.
SQL Server provides a number of hints that you can use in a query to override the default transaction isolation level. These hints are described as follows:
HOLDLOCK? Within a transaction, a shared lock on a resource (row, page, table) is released as soon as the T-SQL statement that is holding the shared lock is finished with the resource. To maintain a shared lock for the duration of the entire statement, or for the entire transaction if the statement is in a transaction, use the HOLDLOCK clause in the statement. The following example demonstrates the usage of the HOLDLOCK statement within a transaction:
declare @seqno int begin transaction -- get a UNIQUE sequence number from sequence table SELECT @seqno = isnull(seq#,0) + 1 from sequence WITH (HOLDLOCK) -- in the absence of HOLDLOCK, shared lock will be released -- and if some other concurrent transaction ran the same -- command, both of them could get the same sequence number UPDATE sequence set seq# = @seqno --now go do something else with this unique sequence number commit tran
As discussed earlier in this chapter in the "Deadlocks" section, using HOLDLOCK in this manner leads to potential deadlocks between processes that are executing this transaction at the same time. For this reason, the HOLDLOCK hint, as well as the REPEATABLEREAD and SERIALIZABLE hints, should be used sparingly if at all. In this example, it might be better for the SELECT statement to use an update or exclusive lock on the sequence table using the hints discussed later in the section in the "Lock Type Hints" section. Another option would be to use an application lock as discussed previously in this chapter in the "Using Application Locks" section.
NOLOCK? You can use this option to specify that no shared lock be placed on the resource and that requests for update or exclusive locks be denied. This option is similar to running a query at isolation level 0 (READUNCOMMITTED), which allows the query to ignore exclusive locks and read uncommitted changes. The NOLOCK option is a useful feature in reporting environments, where the accuracy of the results is not critical.
READUNCOMMITTED? This is the same as specifying the Read Uncommitted mode when using the SET TRANSACTION ISOLATION LEVEL command, and it is the same as the NOLOCK table hint.
READCOMMITTED? This is the same as specifying the Read Committed mode when you use the SET TRANSACTION ISOLATION LEVEL command. The query will wait for exclusive locks to be released before reading the data. This is the default locking isolation mode for SQL Server.
REPEATABLEREAD? This is the same as specifying Repeatable Read mode with the SET TRANSACTION ISOLATION LEVEL command. It prevents nonrepeatable reads within a transaction, and behaves similarly to using the HOLDLOCK hint.
SERIALIZABLE? This is the same as specifying Serializable mode with the SET TRANSACTION ISOLATION LEVEL command. It prevents phantom reads within a transaction, and behaves similarly to using the HOLDLOCK hint.
READPAST? This hint applies only to the SELECT statement. By specifying this option, you can skip over the rows that are locked by other transactions, returning the rows that can be read. In the absence of the READPAST option, the SELECT statement will wait (or time out if lock timeout values are set) until the locks are released on the rows by other transactions. A statement that uses the READPAST clause can only read past row locks that are held by transactions running in Read Committed mode. This lock hint is useful when reading information from a SQL Server table used as a work queue.
You can use the following optimizer hints to override lock granularity:
ROWLOCK? You can use this option to force the Lock Manager to place a row-level lock on a resource instead of a page-level or a table-level lock. This can be used in conjunction with the XLOCK lock type hint to force exclusive row locks.
PAGLOCK? You can use this option to force a page-level lock on a resource instead of a row-level or table-level lock. This can be used in conjunction with the XLOCK lock type hint to force exclusive page locks.
TABLOCK? You can use this option to force a table-level lock instead of a row-level or a page-level lock. This option can be used in conjunction with the HOLDLOCK table hint to hold the table lock until the end of the transaction.
TABLOCKX? You can use this option to force a table-level exclusive lock instead of a row-level or a page-level lock. No shared or update locks are granted to other transactions as long as this option is in effect. If you are planning maintenance on a SQL Server table and you don't want interference from other transactions, this is one of the ways to essentially put a table into a single user mode.
You can use the following optimizer hints to override the lock type that SQL Server uses:
UPDLOCK? This option is similar to HOLDLOCK except that whereas HOLDLOCK uses a shared lock on the resource, UPDLOCK places an update lock on the resource for the duration of the transaction. This allows other processes to read the information, but not acquire update or exclusive locks on the resource. This option provides read repeatability within the transaction while preventing deadlocks that can result when using HOLDLOCK.
XLOCK? This option is similar to HOLDLOCK except that whereas HOLDLOCK uses a shared lock on the resource, XLOCK places an exclusive lock on the resource for the duration of the transaction. This prevents other processes from acquiring locks on the resource.