NOLOCK .... Think twice

Not all the RDBMS are the same.

One of the most important distinction between Oracle and  SQL Server / DB2 is that , in Oracle  readers does not block writers and writers does not block readers. This is called read consistency .

In SQL Server , readers does block writers and vice versa with the default setting. Most of developers use NOLOCK hint to alleviate this locking issue.  Not many are aware of the fact that NOLOCK does allow dirty ( uncommited) reads . That means , you could be reporting on non-existent data. Dirty reads are indeed dirty and dangerous.

Let us see an example.

We will create a test database and check the setting.

C:\>sqlcmd -W
1> create database testiso;
2> go
1> select name , snapshot_isolation_state , snapshot_isolation_state_desc , is_read_committed_snapshot_on from sys.databases where name ='TESTISO';
2> go
name snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on
---- ------------------------ ----------------------------- -----------------------------
testiso 0 OFF 0

(1 rows affected)


You can visit this link ( http://msdn.microsoft.com/en-us/library/ms178534.aspx) for the description of the properties.

Let us have two sessions , where session 1 creates a table , inserts a record into a table  within a transaction.

Session 1
=========
1> create table t ( c1 int identity(1,1) , c2 int , c3 varchar(25) ) ;
2> go

1> insert into t ( c2 , c3) values ( 10, 'Test Record1') ;
2> go

(1 rows affected)

1> begin transaction
2> insert into t ( c2 , c3) values ( 20, 'Test Record2') ;
3> go

(1 rows affected)

Now open another session ( Let us say Session 2 ) and SELECT the record from the table t.

1> select * from t;
2> go

Now this session will hang till the transaction in Session 1 is either commited / rolled back.

Let us kill the SELECT in session 2 and  issue the same SELECT with NOLOCK hint.

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.
1> select * from t with(nolock)
2> go
c1 c2 c3
-- -- --
1 10 Test Record1
2 20 Test Record2

(2 rows affected)
1>
Great . Now the session does not hang ; it gives an result set back . Look at the second record . This record is placed within a transaction ; so there is a chance , this transaction may be rolled back . As we used NOLOCK hint , my query can the uncommited result sets as well.

Now let us go back to SESSION 1 and rollback the transaction .

1> rollback;
2> go

1> select * from t 
2> go
c1 c2 c3
-- -- --
1 10 Test Record1


Now , with NOLOCK hint , you produced an report with a non existent data . That is DANGEROUS .

We can mitigate this issue with the different setting in the database.
Let us change the default setting of the database.

1> ALTER DATABASE testiso SET ALLOW_SNAPSHOT_ISOLATION ON;
2> go
1> ALTER DATABASE testiso SET READ_COMMITTED_SNAPSHOT ON;
2> go
1> select name , snapshot_isolation_state , snapshot_isolation_state_desc , is_read_committed_snapshot_on from sys.databases where name ='TESTISO';
2> go
name snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on
---- ------------------------ ----------------------------- -----------------------------
testiso 1 ON 1

(1 rows affected)

Let us repeat the same test . ie , Insert a record into a table  within a transaction.


Session 1
=========

 1> use testiso
 2> go
 Changed database context to 'testiso'.
 1> begin transaction
 2> insert into t ( c2 , c3) values ( 20, 'Test Record2') ;
 3> go

 (1 rows affected)

Session 2
=========


1> select * from t;
2> go

c1 c2 c3
-- -- --
1 10 Test Record1


Now , with this setting , the query in Session 2 does not hang and does not produce uncommited records and produces correct results .  But does have impact on tempdb. From my perspective , its worth it.

Comments Welcome.