Locking Hints

3 4

Locking hints are T-SQL keywords that can be used with SELECT, INSERT, UPDATE, and DELETE statements to direct SQL Server to use a preferred type of table-level lock for a particular statement. You can use locking hints to override the default transaction isolation level. You should use this technique only when absolutely necessary because if you're not careful, you could cause blocking or deadlocks.

Let's look at a situation where using a locking hint could be useful. Suppose you are using the default read committed isolation level for all transactions. With read committed, when a transaction performs a read, a shared lock is held on the resource only until the read is completed, and then the shared lock is released. Therefore, if a transaction reads the same data twice, the results might differ between reads because another transaction could have obtained a lock and updated the same data between the first and second read.

To avoid the repeated read problem, you could specify the serializable isolation level, but doing so will cause SQL Server to hold all shared locks needed for SELECT statements in all transactions until each transaction is completed. In other words, shared locks will be held on the table specified in a transaction's SELECT statement for the entirety of the transaction. If you do not want to enforce serializability on all your transactions, you can add a locking hint to a specific query. The HOLDLOCK locking hint in a SELECT statement instructs SQL Server to hold all shared locks on the table specified in a transaction's SELECT statement until the end of the transaction, despite the isolation level. Thus, if the transaction performed a repeated read, the data would be consistent (not changed by another transaction). The isolation level is not affected for other transactions when you use a locking hint.

NOTE

---

The SQL Server Query Optimizer automatically determines the most efficient execution plan and lock types for a query. Because the query optimizer automatically selects the correct locking type or types, locking hints should be used only if they are well understood and only when absolutely necessary, as they might adversely affect concurrency.

The following list describes the available table-level locking hints:

• HOLDLOCK Holds a shared lock until the completion of a transaction, rather than releasing it as soon as the table, page, or row of data is no longer required. Equivalent to using the SERIALIZABLE locking hint.
• NOLOCK Applies only to the SELECT statement. Does not obtain shared locks and does not honor exclusive locks; it will read data that is held exclusively by another transaction. This hint allows for reads of uncommitted data (dirty reads).
• PAGLOCK Uses a page-level lock where a single table-level lock would normally be used.
• READCOMMITTED Performs a scan with the same locking behavior as that of a transaction using the read committed isolation level (the default isolation level for SQL Server).
• READPAST Applies only to the SELECT statement and only to rows locked using row-level locking. Skips rows locked by other transactions that would normally be in the result set; returns results without these locked rows. Can be used only with transactions running at the read committed isolation level.
• READUNCOMMITTED Is equivalent to NOLOCK.
• REPEATABLEREAD Performs a scan with the same locking behavior as that of a transaction using the repeatable read isolation level.
• ROWLOCK Uses row-level locks instead of page-level or table-level locks.
• SERIALIZABLE Performs a scan with the same locking behavior as that of a transaction using the serializable isolation level. Equivalent to HOLDLOCK.
• TABLOCK Uses a table-level lock rather than a page-level or row-level lock. SQL Server holds this lock until the end of the statement.
• TABLOCKX Uses an exclusive lock on a table. Careful! This hint prevents other transactions from accessing the table.
• UPDLOCK Uses update locks instead of shared locks while reading a table. This hint allows other users to only read the data and allows you to update it, thus ensuring that no other user has updated the data since you last read it.

You can choose to combine compatible locking hints, such as TABLOCK and REPEATABLEREAD, but you cannot combine conflicting hints, such as REPEATABLEREAD and SERIALIZABLE. To indicate a table-level locking hint, include the hint within parentheses after the table name in the T-SQL statement. The following statement is an example of using the TABLOCKX hint in a SELECT statement:

USE pubs  SELECT COUNT(ord_num) FROM sales (TABLOCKX) WHERE ord_date > "Sep 13 1994" GO 

The TABLOCKX hint directs SQL Server to hold an exclusive table-level lock on the sales table until the statement is completed. This hint ensures that no other transaction can modify data in the sales table while the query is counting the orders from that table. Be careful with this kind of hint because blocking other transactions from this table might cause other transactions to wait, and slower response times and chained blocking might result. Again, use table-level locking hints only when absolutely necessary.