在C＃中处理SQL死锁的模式？ [英] Patterns for handling a SQL deadlock in C#?

问题描述

我正在C＃中编写一个访问SQL Server 2005数据库的应用程序。该应用程序是相当数据库密集的，即使我尝试优化所有访问，设置适当的索引等等，我期望我迟早会遇到死锁。我知道为什么数据库死锁发生，但我怀疑我将能够发布软件，而不会在某些时候发生僵局。应用程序正在使用Entity Framework进行数据库访问。

在C＃客户端代码中处理SQLExceptions（死锁）是否有任何好的模式 - 例如重新运行语句批次在x毫秒之后？

澄清;我不是在寻找一个方法来首先避免死锁（隔离级别，索引，语句顺序等），而是如何在实际发生时处理它们。

解决方案

我发布了一个代码示例来处理这一段时间，但是，似乎在这段时间内，我似乎失去了我的账户，所以我现在不敢找到它，没有我在这里使用的代码。

简短的答案 - 将该东西包装在一个try..catch中。如果你看到一个看起来像一个死锁的错误，睡眠时间很短，增加一个重试计数器。如果您遇到另一个错误或重试计数器清除阈值，请将错误提交给调用例程。

（如果可以，尝试将其放在一个一般例行程序，并通过它运行大部分/全部的数据库访问，所以您处理程序中的死锁。）

编辑：啊，教我不要使用Google！之前的代码示例我和其他人给出的是在如何使用ADO在C＃中获得高效的Sql Server死锁处理？

I'm writing an application in C# which accesses a SQL Server 2005 database. The application is quite database intensive, and even if I try to optimize all access, set up proper indexes and so on I expect that I will get deadlocks sooner or later. I know why database deadlocks occur, but I doubt I'll be able to release the software without deadlocks occuring at some time. The application is using Entity Framework for database access.

Are there any good pattern for handling SQLExceptions (deadlocked) in the C# client code - for example to re-run the statement batch after x milliseconds?

To clarify; I'm not looking for a method on how to avoid deadlocks in the first place (isolation levels, indexes, order of statements etc) but rather how to handle them when they actually occur.

解决方案

I posted a code sample to handle exactly this a while back, but SO seemed to lose my account in the interim so I can't find it now I'm afraid and don't have the code I used here.

Short answer - wrap the thing in a try..catch. If you catch an error which looks like a deadlock, sleep for a short random time and increment a retry the counter. If you get another error or the retry counter clears your threshold, throw the error back up to the calling routine.

(And if you can, try to bung this in a general routine and run most/all of your DB access through it so you're handling deadlocks program-wide.)

EDIT: Ah, teach me not to use Google! The previous code sample I and others gave is at How to get efficient Sql Server deadlock handling in C# with ADO?

这篇关于在C＃中处理SQL死锁的模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！