Nolock in sql server

NOLOCK is a query hint that can be used in a SELECT statement to specify that the query should read data without acquiring shared locks on the underlying tables. This hint is also known as "read uncommitted" or "dirty read". When we use NOLOCK, it allows our query to potentially read data that is in the process of being modified by other transactions, which can result in non-repeatable reads and even the possibility of reading data that is in an inconsistent state.

Syntax

SELECT department_id,        department_name,        location_id FROM   departments WITH (nolock)

Dirty Reads: Using NOLOCK can lead to dirty reads, which means we might read data that has been modified by another transaction but hasn't been committed yet. This can result in inconsistent or incorrect data being returned.

Non-Repeatable Reads: NOLOCK can also lead to non-repeatable reads, where the same query executed multiple times within the same transaction may return different results if other transactions are modifying the data concurrently.

Uncommitted Data: we may read data that is in an uncommitted state, which can be problematic if the other transaction is later rolled back, leaving our data in an inconsistent state.

Reduced Lock Contention: On the positive side, using NOLOCK can reduce lock contention and improve query performance in situations where we are confident that dirty reads and potential inconsistencies are acceptable. It can be useful in scenarios where you are running reports or queries that don't need strict transactional consistency.

Other Isolation Levels: If we require more control over isolation levels, consider using other isolation levels such as READ COMMITTED or REPEATABLE READ, which offer a higher degree of data consistency while still allowing concurrent access.

Let’s see the example of no lock

In one session we are reading the data with no lock. In the other session we are updating the data in the table in transaction but transaction is not yet committed. In the other window we will get not modified data.

If we not using the no lock out query is waiting to complete the transaction   

Here still we have not yet committed  

This query is still executing. Because SQL is by default reading the committed data.

Let’s use no lock. We will get the uncommitted data. 

Here we got the data. Even though our transaction is not yet commented.  Now we are committing the transaction. 

 Transaction committed successfully. Now we can see the updated data.