Deadlock errors in SQL Server 2008 Web edition

Question

We recently upgraded from SQL Server 2008 Express to SQL Server 2008 Web edition.  We also upgraded to a new version of the Absolute Banner Manager third party tool - http://www.xigla.com/abannermanager/

We had been running a previous version of this third party tool under SQL Server Express with no problems.

Under the new version of the banner tool we started seeing a lot of deadlock errors.  The banner tool vendor kept insisting that their product was not the problem.

Eventually, the vendor sent us a fix that solved the problem.  In their fix, they put in the (nolock) table hint in all of their read only select statements.

From all of the research I have been doing, I find information that says that SQL Server is supposed to automatically manage locks and prevent deadlocks from occurring.

My question is this, since it appears that the (nolock) hint solves the problem, does this mean that the vendor has designed their database incorrectly somewhere along the way?  Or does this mean that I don't have a database attribute or parameter set correctly somewhere in SQL Management Studio?

Thanks,
Gary

---

Gary Frickey

Answer 1

Hi,

NOLOCK means a db design failure for me in this case. It is equivalent with the READ UNCOMMITTED transaction isolation level (aka: "dirty read"). It means the data is not guaranteed to be transactionally consistent. Your vendor may revisit their code or at least isolation levels.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 2

Hi Gary Frickey,

I agree with Janos, NOLOCK is bad. Checkout Side Effect of NOLOCK by Jason Strate, which provides an intresting analysis on NOLOCK hint .

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

From all of the research I have been doing, I find information that says that SQL Server is supposed to automatically manage locks and prevent deadlocks from occurring.

My question is this, since it appears that the (nolock) hint solves the problem, does this mean that the vendor has designed their database incorrectly somewhere along the way?  Or does this mean that I don't have a database attribute or parameter set correctly somewhere in SQL Management Studio?

It is true that SQL Server manages locking but it cannot prevent deadlocks.  SQL Server can only detect deadlocks and terminate one of the transactions in order to allow the other(s) to continue.

Deadlocks may be due to improper application design or lack of useful indexes.  Without good indexes, the likelihood of deadlocks is greatly increased because more data are touched.  Ensure stats are up-to-date and review execution plans to ensure indexes are used as expected and only needed data are accessed.

You might also consider turning on the READ_COMMITTED_SNAPSHOT database option to mitigate deadlocking.  This option will use row versioning instead of locking to provide read consistency in the default READ_COMMITTED isolation level and avoid the need for the nasty NOLOCK hint.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Thanks Dan,

Can you tell me how to turn on READ_COMMITTED_SNAPSHOT?  Can I do this for the entire database from someplace in SQL Management Studio?  Or does it have to be done in the application code?

Thanks,
Gary

---

Gary Frickey

Answer 5

Sorry for the quick question before I did more research.  I found this article that basically tells me what I needed about how to turn on READ_COMMITTED_SNAPSHOP.

http://stackoverflow.com/questions/232333/how-long-should-set-read-committed-snapshot-on-take

I will give this a try and post my results here shortly.

Gary

---

Gary Frickey

Answer 6

I have successfully executed the following SQL command
-------------------------------------------------------------------------------
USE Master
GO
ALTER

DATABASE [database] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

GO

--------------------------------------------------------------------------
 Then I go to look at the sys.databases view and it still shows the snapshot_isolation_state as 0 (off)

Shouldn't the sys.databases view reflect the successful execution of the Alter command and shouldn't I now see the snapshot_isolation_state = 1 for the database I changed?

---

Gary Frickey

Answer 7

My mistake, I was looking at the wrong value in the sys.databases view.  The "is read_committed_snapshot" is in fact set to True after running th Alter command.

Now I just need to test the code without using the (NOLOCK) hint to see if this resolves the problem.

---

Gary Frickey

Answer 8

So far, it appears that the "SET READ_COMMITTED_SNAPSHOP ON" may have solved my deadlock problem.  I have been running the third party app that was getting the deadlock errors without needing the (NOLOCK) hint for about 30 minutes now and have not seen any errors.  I need to monitor this for a couple more days to be sure and will close out this thread when I have a final conclusion.

Gary

---

Gary Frickey