Tuesday, July 5, 2011

Beware of Nolock

There is a common misconception among the developers regarding the use of nolock inside their query. In a recent meeting with development team, they told me they have included nolock so that it will not lock the table on read. I explained to them, in default isolation level, select query will create only a shared lock on the resource. So what will happen when you put nolock hint in your query?
- It will retrive the data ignoring the locks held by updates.

This can be demonstrated by a simple example.

In sql management studio, open a query window and run the following code.


create Table Fruits
(
FruitName varchar(25),
Quantity int
)

Go

insert into Fruits values ('Apple',100)
Go
insert into Fruits values ('Orange',50)
Go
insert into Fruits values ('Strawberry',500)
Go

Begin tran

Update Fruits set Quantity =75 where FruitName='Orange'


See that after updating the quantity for orange, you have not committed the transaction.
Now open another query window and run

select * from Fruits

This query will wait indefenitely.

Now cancel this query, and try running the same query with nolock hint
select * from Fruits(nolock)

Its completed just like that.
Note the quantity of orange will be shown as 75, where as we have not yet decided whether to commit the query in first window.
This is called dirty read.

To conclude I will quote the app team head

"This means I have only two options, either my application be extremely slow, or my data is often inconsistant".

Well, we should try other options to tune the code before using nolocks. Above said, there are many situations when a dirty read is not an issue. But it all depends on your business requirement

1 comment:

  1. Glad to see you started writing blog posts.

    Yes agreed "It depends" We can also use the snapshot isolation.

    ReplyDelete