SQL Server Locking Hints

While SQL Server provides automatic data locking mechanism which is good enough for most situations, there are times when developers must provide locking hints in their queries for better performance. There are several locking hints that can be used with SQL Server, however, the two most common ones are as follows: READ COMMITTED This is the default locking strategy used by SQL Server. PROS: Guarantees that only committed data is read. CONS: If a writer has a lock in place, readers are blocked until the writer releases it's lock, hence delaying readers. EXAMPLE

-- Default behavior; no special hint required
SELECT EmployeeID, EmployeeName FROM Employee

NOLOCK or READ UNCOMMITTED It is best used when approximations are acceptable, or for "dual-role" systems where database is responsible for many simultaneous reads and writes. PROS: Reads are blazing fast, since any exclusive locks are ignored. Also Shared Locks are not issued on rows read, so writers do not have to wait for read operations to complete. CONS: Data read may not be 100% accurate, since exclusive locks are ignored.

EXAMPLE

-- NOLOCK hint is used
SELECT EmployeeID, EmployeeName WITH (NOLOCK)
FROM Employee