Using NOLOCK in SQL Server

When data in a database is read or modified, the database engine uses special types of controls, called locks, to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.

When designing database applications, you should keep in mind the different types of locks that will be issued, and the different levels of isolation your transactions will occur. Typically, the SQL Server defaults work fine for what you are trying to accomplish. However, there will be times when it is advantageous to manually make hints to how locks are issued on your tables in your SQL statements.

This article focuses on table : NOLOCK. I'll set up a table to use for our example queries. Execute the script in  to create the Orders table and populate the table with data.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Orders')

DROP TABLE Orders

GO

CREATE TABLE Orders ( OrderId int, ProductName varchar (30), OrderDare Date, Price money, )

GO

--Insert values to the table

INSERT INTO Orders VALUES (1,'Bag',GETDATE(),1500.00)

INSERT INTO Orders VALUES (1,'Book',GETDATE(),200.00)

GO

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

BEGIN TRANSACTION

INSERT INTO Orders

    (OrderId, ProductName (30), OrderDare, Price)

     VALUES

     (3,'Shoes',GETDATE(),2000.00)

        
                  The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script,

select * from Orders

The above query is in Executiong stage only and resultwon’t display.

Since the table Orders is locked by the insert statement.

With  using  the NOLOCK table hint in returning the  records in the Orders table.

select * from Orders WITH(NOLOCK)

The number of records returned is 3. Since the transaction that entered the record into the Orders table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:

ROLLBACK TRANSACTION

This statement removes the record from the SalesHistory table that I previously inserted. Now I run the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM Orders WITH(NOLOCK)

This time the record count returned is 2. My first query read a record that was not yet committed -- this is a dirty read.

When to use  WITH (NOLOCK)

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.

Most banking applications can safely use nolock because they are transactional in the business sense. You only write new rows, you never update them

Most of the Real time scenario’s when we used to read the date we need to use the WITH(NOLOCK)

The question is what is worse, a Deadlock or a wrong value?

 For finanical databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is bullshit.

In a financial database you use business transactions. That means adding one row to each account. It is of upmost importance that these transactions complete and the rows are successfully written.

Getting the account balance temporarily wrong isn't a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.