Locking Hints
Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. The hints specify the type of locking or row versioning the instance of the Microsoft SQL Server Database Engine uses for the table data. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.
For more information about the specific locking hints and their behaviors, see Table Hint (Transact-SQL).
Note
The Database Engine query optimizer almost always chooses the correct locking level. We recommend that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can adversely affect concurrency.
The Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. This means it is possible for a SELECT statement using NOLOCK to be blocked.
As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.
USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT Title
FROM HumanResources.Employee WITH (NOLOCK);
GO
-- Get information about the locks held by
-- the transaction.
SELECT
resource_type,
resource_subtype,
request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@spid;
-- End the transaction.
ROLLBACK;
GO
The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. In this case, serializability is no longer guaranteed.
See Also
Other Resources
DELETE (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Table Hint (Transact-SQL)
UPDATE (Transact-SQL)