Nolock table hint is SQL Server

The NOLOCK table hint in SQL Server allows a query to read the data that is currently being modified by another transaction even if that transaction has not yet committed. It does not block other transaction.

It allows dirty reads because we may read uncommitted data. SQL default isolation level is read committed. NOLOCK is equivalent to setting the transaction isolation level to “Read uncommitted.”

 The query does not acquire shared locks on the table which means other transaction can modify the data while it is being read. The query does not wait for exclusive lock to be released. It reads data even if another transaction has an exclusive lock on it.

Since the query does not wait for locks, It can improve performance in high concurrency environment where locking and blocking are common.

See the example as below.

For the demo we are using the AdventureWorks

Lets see the below query

We have Person.Person table

SELECT * FROM Person.Person  where MiddleName is null

Currently we have 8499 records which have middle name is null. We want to update these records.

Here User A is selecting the data and meanwhile User B is doing the update of middle name of the person. The query completes and updates the records, but the data is not yet committed to the database so the records are locked.

Let’s see

-- run in query window 1 BEGIN TRAN UPDATE Person.Person SET MiddleName = 'NA' WHERE  MiddleName is null -- ROLLBACK or COMMIT

 

Its updated but transaction is not yet committed. User A is running the select statement let’s see what will happen.

Still it is running.

Running sp_who2 will show the SELECT statement is being blocked by the other process. I will need to either cancel this query or COMMIT or ROLLBACK the query in window 1 for this to complete.

Now cancel the select statement.

Now we are using the NOLOCK in select statement.

SELECT * FROM Person.Person (nolock) where MiddleName is null SELECT * FROM Person.Person (nolock) where MiddleName ='NA'

Data is not committed but still we are getting the updated data. If we will do the rollback of transaction, we will not get the updated data. It means whatever we have read these data are un committed data which is known as Dirty Read.

We have rollback the transaction. Let’s see the data.

Getting the correct data without any changes.

Types of SQL Server Locks Used with NOLOCK

Ø  MD – metadata lock

Ø  DB – database lock

Ø  TAB – table lock

Ø  PAG – page lock

 Mode

Ø  S – Shared access

Ø  Sch-S – Schema stability makes sure the schema is not changed while object is in use

Ø  IS – Intent shared indicates intention to use S locks

 We can use NOLOCK on with select statement. If we use other that it we will get an error.

Let’s see the nolock with Update statement.

UPDATE Person.Person with (nolock) SET MiddleName = 'NA' WHERE  MiddleName is null

 Getting the below error.

Schema Change Blocking with NOLOCK

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.

Let’s add the new column in this table.

BEGIN TRAN ALTER TABLE Person.Person ADD column_a VARCHAR(20) NULL ; -- ROLLBACK or COMMIT

 

See this table.

SELECT * FROM Person.Person (nolock)

We are still not get the result.

 Here we are doing the schema level change due to that stable is locked and we are not getting the data.

Let’s rollback this transaction and see.

As soon as we have rollback the transaction, we got the data for select statement.

Drawback of with NOLOCK

We have seen above how we  can get dirty reads using the NOLOCK hint. These are also other terms when we may encounter for this hint.

Ø  Dirty Reads – this occurs when updates are done, so the data you select could be different.

Ø  Nonrepeatable Reads – this occurs when we need to read the data more than once and the data changes during that process

Ø  Phantom Reads – occurs where data is inserted or deleted and the transaction is rolled back. So for the insert we will get more records and for the delete you will get less records.