What is NOLOCK in SQL Server with Example

In a SQL Server database, concurrency is very necessary to make sure data integrity. By default, the concurrency utilizes a locking mechanism on the underlying data. However, sometimes locking is able to lead to blocking, and blocking will be able to result in time wasted and losses in productivity. In this case, you need to learn how to identify and fix performance related concurrency problems.

You may ever see the use of the NOLOCK hint in existing code for your stored procedures and you are not exactly sure if this is helpful or not. By the way, what is NOLOCK in SQL Server? Give the example for it.

Apparently, in some SQL Server shops the use of NOLOCK is used through the application. In this page, we are going to take a closer look at how this works and what the problem is when using NOLOCK in SQL Server.

• More info: work2vec Program Implementation on Python Gensim Library

WHAT DOES THE NOLOCK DO?

• The NOLOCK offers SQL to read data from tables by ignoring any locks. So, it is not being blocked by other processes.
• The NOLOCK is able to improve query performance, but also introduces the possibility of dirty reads.

You have to read more to better understand the use of NOLOCK in SQL Server.

EXAMPLE OF THE NOLOCK IN SQL SERVER

Now, let us walk through some examples to see how this works. For note: These queries are run against the AdventureWorks database.

Here is a query which returns all of the data from the Person Contact table. If you run this query, you are able to see that there is only one record that has a Suffix value for ContactID. Let us say another user runs the query in a transaction such as below. The query will update and complete the records. However it is not yet committed to the database. Therefore, the records are locked.

run in query window 1

BEGIN TRAN

Update Person Contact Set Suffix = B Where ContactID < 20

ROLLBACK or COMMIT

If you run the same query from above again you are going to notice that it never completes, as the UPDATE has not yet been committed.

When you run “sp_who2” you are able to see that the SELECT statement is being blocked. You are going to need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete.

For this instance, we are going to cancel the SELECT query. To get around the locked records, you are able to use the NOLOCK hint. The query will be able to complete and has not been committed or rolled back.

If you notice the Suffix column, now it has “B” for all records. This is due to the update in window 1 that updated those records. Although that transaction has not been performed, but the SQL Server ignores the locks and returns the data as using the NOLOCK. If the update is rolled back, the data is going to revert back to what it looked like before.

This is considered a Dirty Read as this data may or may not exist depending on the final outcome in query window 1. If you rollback the update by using the Rollback command and rerun the SELECT query you are able to see the Suffix is back to what it looked like before.

We are able to say that the issue with using the NOLOCK is that there is the possibility of reading data which has been altered, but not yet committed to the database. If you are running reports and do not care if the data may be off this is not an issue, however if you are making transactions where the data requires to be in a consistent state you are able to see how the NOLOCK hint can return false data.

TYPES OF SQL SERVER LOCKS USED WITH NOLOCK

Now, you have to know types of SQL servers locks when the NOLOCK is used.

If you run your SELECT without NOLOCK, you are able to see the locks which are taken if you use sp_lock. To get the lock information, you ran sp_lock in another query window while this was running.

While if you do the same for your SELECT with the NOLOCK, you will be able to see these locks.

SELECT * From Person Contact WITH (NOLOCK) Where ContactID < 20

The differences are that there is a “S” shared access lock which is put on the page that you are reading for the first 19 rows of data in the table when you do not use NOLOCK. Also, you are getting a Sch-S lock versus an IS lock for the table.

Another thing to note is when you only SELECT data SQL Server still makes a lock to ensure the data is consistent.

Here are the lock types and the lock modes which are used for the two queries.

Lock Types

• MD – metadata lock
• DB – database lock
• TAB – table lock
• PAG – page lock

Mode

• S: It shared access.
• Sch-S: This schema stability ensures the schema is not altered while the object is in use.
• IS: This signifies to utilize S locks.

ISSUES WITH SQL SERVER NOLOCK

We explained above how you are able to get dirty reads by using the NOLOCK hint. Also, these are other terms you may encounter for this hint.

• Don’t miss: work2vec Word Vector on Python

• Dirty Reads

This issue happens when updates are done, so the data you choose can be different.

• Non Repeatable Reads

This issue happens when you need to read the data more than once and the data changes during that process.

• Phantom Reads

This issue happens where data is inserted or removed and the transaction is rolled back. So, for the insert you are going to get more records and for the delete you are going to get less records.