SQL SERVER : The Scene Behind NOLOCK

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

The lock acquired by this session can be easily found using the below query

SELECT ResourceName = CASE resource_type
            WHEN 'database' THEN DB_NAME(resource_database_id)
            WHEN 'object' THEN OBJECT_NAME(resource_associated_entity_id, resource_database_id)
            ELSE ''
            END,request_mode,request_status, * FROM sys.dm_tran_locksWHERE request_session_id = 53




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.

Next we can see an Intent Shared(IS) lock on the table level.An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page.This IS lock also make sure that this table will not be modified till the select statement complete the operation.Next you an see a shared lock on page. This is to make sure that the data in the page are not getting modified while reading the data.

Let us see how it will work along with an update statement.

BEGIN TRAN

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

SELECT *

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

SQL server: Deciphering Wait resource

When I was working with couple of frequent blocking issue in our live server, I have noticed different types of wait resources which are not meaningful unless they mapped to a table or an index. In this post I will explain how to map the wait resources to table/index.

Key Wait Resource 

Below is a sample of blocked process report with Key as wait resource .

The first part of the key wait resource is the database id and second part is called as Hobt_Id. Hobt is an acronym for Heap Or B Tree. The hobt_id can be mapped to sys.indexes and sys.objects through sys.partitions. Below script will map the key wait resource to corresponding index and table.

SELECT 
 o.name AS TableName, 
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID 
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID  AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594040811520

Page Wait Resource 

Below is a sample of blocked process report with Page as wait resource .

As everyone knows, the first part of the wait resource is the database id (68)  , second part is file id (1)  and third part is the page number(492478). To find out the object id associated with this page follow the below steps.

SELECT DB_NAME(68)

Select the database based on the output above statement. Then execute the below statement

DBCC traceon (3604)

GO
DBCC page (68, 1, 492478) --Database_id,file_id,page_id


This will give a result as given below.

The object id can be mapped to a table using  the system function  object_name()

Object Wait Resource 

Below is a sample of blocked process report with Object as wait resource .

Here also the first part of the wait resource is the database id(68) ,second part is the object id and third part is known as lock partition id. The object id part can be mapped to an object using the object_name() system function . The lock partition id is not very useful in troubleshooting the blocking issue.This will have positive value only when the server has more than 16 CPU. I will explain more about the lock partition in my  future post.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba