.net 如何捕获由死锁引起的SqlException？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2256939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to catch SqlException caused by deadlock?
提问by Joannes Vermorel
From a .NET 3.5 / C# app, I would like to catch SqlExceptionbut only if it is caused by deadlockson a SQL Server 2008 instance.
从 .NET 3.5 / C# 应用程序中，我想捕获SqlException但仅当它是由SQL Server 2008 实例上的死锁引起的。
Typical error message is Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
典型的错误信息是 Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Yet, it does not seem to be a documented error codefor this exception.
然而，它似乎不是此异常的记录错误代码。
Filtering exception against the presence of the deadlockkeyword in their message seems a very ugly way to achieve this behavior. Does someone know the right way of doing this?
针对消息中死锁关键字的存在过滤异常似乎是实现此行为的一种非常丑陋的方式。有人知道这样做的正确方法吗？
回答by AdaTheDev
The Microsft SQL Server-specific error code for a deadlock is 1205 so you'd need to handle the SqlException and check for that. So, e.g. if for all other types of SqlException you want the bubble the exception up:
Microsft SQL Server 特定的死锁错误代码是 1205，因此您需要处理 SqlException 并检查它。因此，例如，如果对于所有其他类型的 SqlException，您希望将异常冒泡：
catch (SqlException ex)
{
if (ex.Number == 1205)
{
// Deadlock
}
else
throw;
}
Or, using exception filtering available in C# 6
或者，使用 C# 6 中可用的异常过滤
catch (SqlException ex) when (ex.Number == 1205)
{
// Deadlock
}
A handy thing to do to find the actual SQL error code for a given message, is to look in sys.messages in SQL Server.
查找给定消息的实际 SQL 错误代码的一个方便的方法是查看 SQL Server 中的 sys.messages。
e.g.
例如
SELECT * FROM sys.messages WHERE text LIKE '%deadlock%' AND language_id=1033
An alternative way to handle deadlocks (from SQL Server 2005 and above), is to do it within a stored procedure using the TRY...CATCH support:
处理死锁的另一种方法（来自 SQL Server 2005 及更高版本）是使用 TRY...CATCH 支持在存储过程中执行此操作：
BEGIN TRY
-- some sql statements
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 1205)
-- is a deadlock
ELSE
-- is not a deadlock
END CATCH
There's a full example herein MSDN of how to implement deadlock retry logic purely within SQL.
有一个完整的例子这里在如何纯粹的内SQL执行死锁重试逻辑MSDN。
回答by Steven
Because I suppose you possibly want to detect deadlocks, to be able to retry the failed operation, I like to warn you for a little gotcha. I hope you'll excuse me for being a bit off topic here.
因为我想您可能想要检测死锁，以便能够重试失败的操作，所以我想警告您一些小问题。我希望你能原谅我在这里有点跑题。
A deadlock detected by the database will effectively rollback the transaction in which you were running (if any), while the connection is kept open in .NET. Retrying that operation (in that same connection), means it will be executed in a transactionless context and this could lead to data corruption.
数据库检测到的死锁将有效地回滚您正在运行的事务（如果有），同时连接在 .NET 中保持打开状态。重试该操作（在同一个连接中）意味着它将在无事务上下文中执行，这可能会导致数据损坏。
It's important to be aware of this. It's best to consider the complete connection doomed in case of a failure caused by SQL. Retrying the operation can only be done on the level where the transaction is defined (by recreating that transaction and its connection).
意识到这一点很重要。最好考虑在 SQL 导致失败的情况下完全连接。重试操作只能在定义事务的级别上完成（通过重新创建该事务及其连接）。
So when you are retrying a failed operation, please make sure you open a completely new connection and start a new transaction.
因此，当您重试失败的操作时，请确保打开一个全新的连接并开始新的事务。
回答by Brian
Here is a C# 6 way of detecting deadlocks.
这是检测死锁的 C# 6 方法。
try
{
//todo: Execute SQL.
//IMPORTANT, if you used Connection.BeginTransaction(), this try..catch must surround that code. You must rollback the original transaction, then recreate it and re-run all the code.
}
catch (SqlException ex) when (ex.Number == 1205)
{
//todo: Retry SQL
}
Make sure this try..catch surrounds your entire transaction. According to @Steven (see his answer for details), when the sql command fails due to the deadlock, it causes the transaction to be rolled back and, if you don't recreate the transaction, your retry will execute outside of the context of the transaction and can result in data inconsistencies.
确保此 try..catch 围绕您的整个交易。根据@Steven（详见他的回答），当 sql 命令由于死锁而失败时，它会导致事务回滚，如果你不重新创建事务，你的重试将在上下文之外执行交易，并可能导致数据不一致。
