NOLOCK, some say it’s a fast option for queries and will never cause blocking, I say it’s quite dirty and MAY cause blocking.
I used to think that a NOLOCK (which is the same as read uncommitted) hint actually meant that a “no lock” was used, I was very wrong and the naming doesn’t really help. Let’s have a look.
I will run a random SELECT statement with the hint, such as
SELECT * FROM Sales.BigSalesOrderDetailEnlarged WITH (NOLOCK)
Then I will check the “go to” DMV for locks.
SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks WHERE resource_database_id = 13
A Sch-S (schema stability) lock is taken. This is a lightweight lock; the only lock that can conflict with this is a Sch-m (schema modification) lock. (C = Conflict). This means that a NOLOCK can actually block for example against an ALTER TABLE command.
Below shows the example of when my NOLOCK query is being blocked by ALTER INDEX commands:
SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks
So there you go, it can be involved in blocking.
This is my example data.
In Window 1 using SQL Server management Studio I begin an open UPDATE statement – notice that it has NOT been committed.
BEGIN TRAN UPDATE [HumanResources].[Department] SET Name = 'Global' WHERE DepartmentID = 16
In window 2 I do a NOLOCK select statement.
SELECT * FROM HumanResources.Department (NOLOCK)
Now that’s dirty.
Pingback: NOLOCK - SQL Server Blog - SQL Server - Toad World
Your opening statement said you knew nolock meant read uncommitted. You ran a query against a table with an open transaction – uncommitted data. And you got the result with the uncommitted data in your query. Exactly what part is dirty?
officially “dirty” when it reads data that is uncommitted, if I didnt use the nolock it would be blocked until I commited or rolled back the other trans.
Link below provides additional discussion on use of NoLock, the main issue being Deadlock vs possible ‘wrong’ value.
Working with real-time vehicle tracking data, dirty isn’t the issue, and after being stung with database deadlocks in Oracle v8.x I have always used ReadUncommitted in SqlServer.
NoLock is a much more succinct way of issuing a command without having to explicitly specify transaction for ReadUncommitted.
NoLock can return more than just dirty reads. It is also possible to get duplicate results, among other things. There is a nice post on NoLock here:
Pingback: Don’t blink you might READPAST it | All about SQL