通过使用NOLOCK提示避免死锁 [英] Avoiding deadlock by using NOLOCK hint
问题描述
偶尔我会在生产环境中遇到以下错误，该错误会再次运行相同的存储过程而消失。
Once in a while I get following error in production enviornment which goes away on running the same stored procedure again.
事务（流程ID 86）被另一个进程锁定在锁资源上，因此被选为死锁受害者。重新运行事务
Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
有人告诉我，如果我在存储过程中使用NOLOCK提示，它将确保它永远不会死锁。这样对吗？有没有更好的方法来处理此错误？
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?
推荐答案
在RDBMS上偶尔会出现死锁，如SQL Server / Sybase。
Occasional deadlocks on an RDBMS that locks like SQL Server/Sybase are expected.
您可以在客户端上进行编码，以按照我推荐的 MSDN处理死锁 。
基本上，检查一下SQLException，也许半秒钟后再试一次。
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.
否则，您应该检查代码，以便对表的所有访问都在相同的顺序。或者，您也可以使用SET DEADLOCK_PRIORITY来控制谁成为受害者。
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.
在SQL Server的MSDN上，有最小化死锁 开始
On MSDN for SQL Server there is "Minimizing Deadlocks" which starts
尽管死锁无法完全避免
Although deadlocks cannot be completely avoided
这也提到了我不喜欢的使用较低的隔离级别（与许多SQL类型相同）在这里等等），这是您的问题。不这样做就是答案...：-）
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
注意： MVCC 类型RDBMS（Oracle，Postgres）没有此问题。请参见 http://en.wikipedia.org/wiki/ACID#Locking_vs_multiversioning ，但< a href = http://en.wikipedia.org/wiki/Snapshot_isolation rel = nofollow noreferrer> MVCC还有其他问题。
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.
这篇关于通过使用NOLOCK提示避免死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
