NOLOCK is a SQL Server table hint that lets a SELECT read rows without acquiring shared locks. It may appear to reduce blocking, but it can return uncommitted data that is later rolled back. section 5.2
The address that never existed
At MagicChoc, a salesperson loads Cooking Schmooking’s delivery addresses while an administrator edits the customer record. The salesperson reads an address from the administrator’s in-flight transaction; that transaction is canceled, so the address was never committed, yet the order is delivered there. section 5.2
Listing 5.5 Adding the NOLOCK query hint
DECLARE @CustomerID INT ;
SET @CustomerID = 2 ;
SELECT
Street
, Area
, City
, ZipCode
FROM dbo.Addresses a WITH(NOLOCK)
INNER JOIN dbo.Customers c
ON a.AddressID = c.DeliveryAddressID
WHERE CustomerID = @CustomerID ;
Shows the table-hint syntax applied to the address lookup that creates the risk. section 5.2
Apply these rules when a query appears slow:
- Do not use NOLOCK as a routine performance tweak; unexpected results can contain data that never existed in the database. section 5.9
- Treat locking and blocking as problems to investigate rather than bypassing correctness; transaction isolation levels provide another tuning approach. section 5.2
