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

Monday, July 4, 2011

Blocking : Uncommitted Open transactions

Often we come across blocks in SQL server which affects the performance of your production server. There are many information on the net to tune your queries to decrease blocking, or to choose an appropriate isolation level to improve concurrent access. But here I want to mention a rare scenario that sometimes may go unnoticed.

When you are analyzing blocks on SQL server, normally you may run a query like

Select * from sysprocesses where blocked <>0

And you will look at the head blockers.

But I would recomment you sometimes check this

Select * from sysprocesses where status='sleeping' and open_tran>0

If you have a record, you might further want to look at the host,program and the query which was executed recently on the spid.

A developer should thoroughly check entire flow of his SP and should ensure that it leaves no uncommited transactions when completed.

In some funny situations I have seen people enabling implicit transactions in there query analyzer and simply forget to put commit. By the time we report this issue to him, he must have erased and retyped many other queries in the same window. And will not rember what was the query which left the open transaction.