零SQL死锁设计 - 任何编码模式？ [英] Zero SQL deadlock by design - any coding patterns?

问题描述

我在MS SQL Server 2005上运行的.NET 2.0 webapp上遇到了非常罕见但令人烦恼的SQL死锁。过去，我们以非常经验的方式处理SQL死锁，基本上是调整查询直到它的工作。

I am encountering very infrequent yet annoying SQL deadlocks on a .NET 2.0 webapp running on top of MS SQL Server 2005. In the past, we have been dealing with the SQL deadlocks in the very empirical way - basically tweaking the queries until it work.

然而，我发现这种方法非常不满意：耗时和不可靠。我非常希望遵循确定性查询模式，确保按设计，不会遇到任何SQL死锁。

Yet, I found this approach very unsatisfactory: time consuming and unreliable. I would highly prefer to follow deterministic query patterns that would ensure by design that no SQL deadlock will be encountered - ever.

例如， C＃多线程编程，一个简单的设计规则，例如必须按照字典顺序执行锁定，确保不会有任何死锁发生。

For example, in C# multithreaded programming, a simple design rule such as the locks must be taken following their lexicographical order ensures that no deadlock will ever happen.

有没有任何SQL编码模式保证是死锁的？

Are there any SQL coding patterns guaranteed to be deadlock-proof?

推荐答案

编写死锁代码是真的很难。即使您以相同的顺序访问表，您仍然可能会遇到死锁[1]。我写了我的博客上的一篇文章，阐述了一些可以帮助您避免的方法并解决僵局的情况。

Writing deadlock-proof code is really hard. Even when you access the tables in the same order you may still get deadlocks [1]. I wrote a post on my blog that elaborates through some approaches that will help you avoid and resolve deadlock situations.

如果要确保两个语句/事务永远不会死锁，您可以通过观察每个语句消耗的锁使用 sp_lock 系统存储过程。要做到这一点，你必须要非常快，或者使用一个具有holdlock提示的开放交易。

If you want to ensure two statements/transactions will never deadlock you may be able to achieve it by observing which locks each statement consumes using the sp_lock system stored procedure. To do this you have to either be very fast or use an open transaction with a holdlock hint.

注意： p>

Notes:

1. 任何一个需要多个锁的SELECT语句可能会与一个智能设计的事务相抵触，从而以相反的顺序抓住锁。

这篇关于零SQL死锁设计 - 任何编码模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！