Avoiding deadlock by using NOLOCK hint

Question

Once in a while I get following error in production enviornment which goes away on running the same stored procedure again.

Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Someone told me that if I use NOLOCK hint in my stored procedures, it will ensure it will never be deadlocked. Is this correct? Are there any better ways of handling this error?

Answer 1

Here is a good link on learning to troubleshoot deadlocks. I always try avoid using nolock for the reasons above. Also you might want to better understand Lock Compatibility.

Answer 2

While adding NOLOCK can prevent readers and writers from blocking each other (never mind all of the negative side effects it has), it is not a magical fix for deadlocks. Many deadlocks have nothing at all to do with reading data, so applying NOLOCK to your read queries might not cause anything to change at all. Have you run a trace and examined the deadlock graph to see exactly what the deadlock is? This should at least let you know which part of the code to look at. For example, is the stored procedure deadlocking because it is being called by multiple users concurrently, or is it deadlocking with a different piece of code?

Answer 3

Occasional deadlocks on an RDBMS that locks like SQL Server/Sybase are expected.

You can code on the client to retry as recommended my MSDN "Handling Deadlocks". Basically, examine the SQLException and maybe a half second later, try again.

Otherwise, you should review your code so that all access to tables are in the same order. Or you can use SET DEADLOCK_PRIORITY to control who becomes a victim.

On MSDN for SQL Server there is "Minimizing Deadlocks" which starts

Although deadlocks cannot be completely avoided

This also mentions "Use a Lower Isolation Level" which I don't like (same as many SQL types here on SO) and is your question. Don't do it is the answer... :-)

• What can happen as a result of using (nolock) on every SELECT in SQL Server?
• https://dba.stackexchange.com/q/2684/630

Note: MVCC type RDBMS (Oracle, Postgres) don't have this problem. See http://en.wikipedia.org/wiki/ACID#Locking_vs_multiversioning but MVCC has other issues.