What should be considered when NOLOCK hint is used

Introduction

With SQL Server the NOLOCK hint is very tempting especially with SELECT statements where at least one of the tables is frequently updated. Using the NOLOCK removes all queueing problems and the SELECT statement runs smoothly, but not without side effetcs.

Why do we need a hint at all

First question might be that why do we need to add this hint to get the statement running without additional waits. The answer lies on a basic database management system principle: ACID (atomicity, consistency, isolation and durability). The property isolation defines that other transactions may not see uncompleted modifications. Since SQL Server (when default locking scenario is used) uses exclusive locks which prevent acquiring read (shared) locks all reading operations must wait until exclusive locks are released. This causes the select statement to wait if the data to be fetched is locked exclusively.

Using NOLOCK hint (which means exactly the same as READUNCOMMITTED) actually bypasses the check for exclusive locks and does not set shared locks at all. So what it also means is that the query sees data that is not yet committed to the database which may result to a dirty read. This means that the modifications are not isolated so one of the main database principles is ignored. This is why NOLOCK -like behaviour isn't used by default.

What it actually means

Let's take an example. First let's create necessary objects.

Collapse
----------------------------------
-- Create test objects
----------------------------------
-- Schema
CREATE SCHEMA LockTest;
GO

-- OrderEntry -table
IF OBJECT_ID ( 'LockTest.OrderEntry', 'U' ) IS NOT NULL
DROP TABLE LockTest.OrderEntry;
GO

CREATE TABLE LockTest.OrderEntry (
Id        int     not null identity(1,1) primary key,
Amount    int     not null,
UnitPrice decimal not null,
Area      int     not null
);
GO

-- AddOrders -procedure
IF OBJECT_ID ( 'LockTest.AddOrders', 'P' ) IS NOT NULL
DROP PROCEDURE LockTest.AddOrders;
GO

CREATE PROCEDURE LockTest.AddOrders @OrderCount int AS
BEGIN
  DECLARE @counter int = 0;

  WHILE (@counter < @OrderCount) BEGIN
     INSERT INTO LockTest.OrderEntry
        (Amount, UnitPrice, Area)
     VALUES
        (ROUND(RAND()*100, 0), ROUND(RAND()*100, 2), ROUND(RAND()*10, 0));

     SET @counter = @counter + 1;
  END;
END;
GO  
   
So now we have a single table and a procedure to create some random data. Let's add something to the table.

Collapse
-- Add some data to the table
BEGIN TRANSACTION;
EXEC LockTest.AddOrders @OrderCount=100;
COMMIT;

Read more: Codeproject

Posted via email from Jasper-net