Help with deadlock in Sql Server 2008

Question

Hi,

My website doesn't seem to handle a high number of visitors, I believe it's because the server is too simple.

2 hours ago my website was getting a lot of hits and I noticed that 3 deadlock errors occured, the error is:

System.Data.SqlClient.SqlException : Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I'm not sure why this happened... Looking at the stack trace, I could see that this happened with a select query.

Anyone knows what may be the cause of this error?

The server is running Windows 2008 and Sql Server 2008.

Answer 1

Writes will block reads on SQL Server, unless you have row versioning enabled. You should use the sp_who2 stored procedure and a SQL Profiler trace. sp_who2 will tell you which processes are blocking which, and the profiler will tell you what the last statement was for the blocking process.

Answer 2

If you don't mind dirty reads you can try putting (NOLOCK) after your table names in your SELECT queries. The trade off here is that you are not guaranteed the most up to date data as UPDATE and INSERT statements currently executing are ignored.

Usually this is not to much of a train-smash as most systems read far more than they update/insert, but obviously it depends on the nature of your application.

Alternatively have a look at http://www.sql-server-performance.com/tips/deadlocks_p1.aspx

Answer 3

Dont forget you also have the Activity Monitor under your Management Folder which can give you a more user friendly GUI when looking for locks. (In SQL Server Management Studio, connect to the server with Object Explorer, expand Management, and then double-click Activity Monitor.)

Also if you right click on the Activity Monitor Icon you can:

• View Locks by Process
• View Locks by Object

EDIT: This will only work if you are using Management Studio 2005, but dont forget you can still use this version of Management Studio to connect to SQL Server 2008.

Answer 4

http://support.microsoft.com/?kbid=832524