I have heard many time people talking about the NOLOCK hint. Some of them says it improves the performance and some of them says it reduce the blocking and deadlock as it will not acquire any locks. Let us see what is happening when we use NOCLOCK table hint in queries.
Let us see a query with out NOLOCK table hint and analyse the lock acquired by that query.
SELECT *FROM Sales.SalesOrderHeader a CROSS JOIN Sales.SalesOrderHeader b
SELECT ResourceName = CASE resource_typerequest_status, * FROM sys.dm_tran_locksWHERE request_session_id = 53
WHEN 'database' THEN DB_NAME(resource_database_id)
WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
While analyzing the result, we can see a shared lock on database level. That is nothing to do with the NOLOCK hint. While opening a connection to the database, it will always take shared connection on database used by that connection to make sure that other sessions will not drop the database while in use.
UPDATE Sales.SalesOrderHeader SET status=5 WHERE SalesOrderID=43659
Now run the select statement and lock analyzing script. We can see that the request for Share Lock on page is in Wait status as it is conflicting with the Intent exclusive lock acquired by the update session on the page. This helps sql server to avoid the dirty read but it cause for blocking. You can see that the select statement is blocked by the update statement.
Let us see the same with NOLOCK hint
FROM Sales.SalesOrderHeader a WITH (NOLOCK)
CROSS JOIN Sales.SalesOrderHeader b WITH (NOLOCK)
In this case we can see only shared schema lock on the table. It is not taking a shared lock on the page and this lead to a dirty read.The shared schema lock om table level is important to make to sure that the schema of the table is not getting changed while reading the data. Let us try this select statement after the update statement and still the select statement will run without blocking as it is not trying to acquire the shared lock on pages and it also cause for the dirty read.
The READ UNCOMMITTED isolation level also works in the same way. Instead of specifying the table hint for each table we can set the isolation level to READ UNCOMMITTED. Theoretically NOLOCK hint improve the performance slightly as it need to acquires less lock compared with statement does not have a NOLOCK hint.Be careful with the usage of NOLOCK hint as it perform dirty read and may give undesired result.
If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba