如何使用 ADO 在 C# 中获得高效的 Sql Server 死锁处理？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/320636/
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 get efficient Sql Server deadlock handling in C# with ADO?
提问by Victor Rodrigues
I have a class 'Database' that works as a wrapper for ADO.net. For instance, when I need to execute a procedure, I call Database.ExecuteProcedure(procedureName, parametersAndItsValues).
我有一个“数据库”类，它用作 ADO.net 的包装器。例如，当我需要执行一个过程时，我调用 Database.ExecuteProcedure(procedureName, parametersAndItsValues)。
We are experiencing serious problems with Deadlock situations in SQL Server 2000. Part of our team is working on the sql code and transactions to minimize these events, but I'm thinking about making this Database class robust against deadlock situations.
我们在 SQL Server 2000 中遇到了死锁情况的严重问题。我们团队的一部分正在处理 sql 代码和事务，以尽量减少这些事件，但我正在考虑使这个数据库类能够抵抗死锁情况。
We want the deadlock victim to retry perhaps after some time delay, but I don't know if it is possible. Here is the code for a method we use:
我们希望死锁受害者在延迟一段时间后重试，但我不知道是否可能。这是我们使用的方法的代码：
public int ExecuteQuery(string query)
{
int rows = 0;
try
{
Command.Connection = Connection;
Command.CommandType = CommandType.Text;
if(DatabaseType != enumDatabaseType.ORACLE)
Command.CommandText = query;
else
Command.CommandText ="BEGIN " + query + " END;";
if (DatabaseType != enumDatabaseType.SQLCOMPACT)
Command.CommandTimeout = Connection.ConnectionTimeout;
if (Connection.State == ConnectionState.Closed)
Connection.Open();
rows = Command.ExecuteNonQuery();
}
catch (Exception exp)
{
//Could I add here any code to handle it?
throw new Exception(exp.Message);
}
finally
{
if (Command.Transaction == null)
{
Connection.Close();
_connection.Dispose();
_connection = null;
Command.Dispose();
Command = null;
}
}
return rows;
}
Can I do this handling inside a catch block?
我可以在 catch 块内进行这种处理吗？
采纳答案by Sam Saffron
First, I would review my SQL 2000 code and get to the bottom of why this deadlock is happening. Fixing this may be hiding a bigger problem (Eg. missing index or bad query).
首先，我将查看我的 SQL 2000 代码并找出发生这种死锁的原因。解决这个问题可能会隐藏一个更大的问题（例如，缺少索引或错误的查询）。
Second I would review my architecture to confirm the deadlocking statement really needs to be called that frequently (Does select count(*) from bobhave to be called 100 times a second?).
其次，我会检查我的架构以确认死锁语句确实需要频繁调用（是否select count(*) from bob必须每秒调用 100 次？）。
However, if you really need some deadlock support and have no errors in your SQL or architecture try something along the following lines. (Note: I have had to use this technique for a system supporting thousands of queries per second and would hit deadlocks quite rarely)
但是，如果您确实需要一些死锁支持并且您的 SQL 或架构中没有错误，请尝试以下方法。（注意：我不得不将这种技术用于支持每秒数千次查询的系统，并且很少会遇到死锁）
int retryCount = 3;
bool success = false;
while (retryCount > 0 && !success)
{
try
{
// your sql here
success = true;
}
catch (SqlException exception)
{
if (exception.Number != 1205)
{
// a sql exception that is not a deadlock
throw;
}
// Add delay here if you wish.
retryCount--;
if (retryCount == 0) throw;
}
}
回答by Marc Gravell
If you are getting problems with deadlocks, it would be better to look at what the SQL code is doing. For example, lock-escalation deadlocks are very easy to create if you have serializable isolation level (or whatever the equivalent is in your rdbms) - and can be mitigated in a few ways, such as re-ordering queries, or (in SQL Server at least) using the (UPDLOCK) to take a write lock earlier (so you don't get a competing read-lock).
如果遇到死锁问题，最好查看 SQL 代码在做什么。例如，如果您具有可序列化的隔离级别（或 rdbms 中的任何等效级别），则很容易创建锁升级死锁 - 并且可以通过几种方式缓解，例如重新排序查询，或（在 SQL Server 中）至少）使用 (UPDLOCK) 提前获取写锁（这样您就不会获得竞争性读锁）。
Re-trying is going to be mixed... for example, if you are in a TransactionScope, it might already have aborted. But just at the purist level - if I get problems talking to the db I want my code to panic, and panic early... re-try seems a bit hacky in this particular scenario.
重试将是混合的……例如，如果您在 TransactionScope 中，它可能已经中止。但只是在纯粹的层面 - 如果我在与数据库交谈时遇到问题，我希望我的代码恐慌，并尽早恐慌......在这种特殊情况下重试似乎有点麻烦。
回答by Marc Gravell
If the deadlock can be solved at the data layer, that's definitely the way to go. Locking hints, redesigning the way the module works and so on. NoLock isn't a panacea though - sometimes it's not possible to use for reasons of transactional integrity and I have had cases of straight (albeit complex) data reads with all relevant tables NoLock'd that still caused blocks on other queries.
如果可以在数据层解决死锁，那肯定是要走的路。锁定提示，重新设计模块的工作方式等等。不过，NoLock 并不是万能药——有时由于事务完整性的原因无法使用它，而且我遇到过直接（尽管很复杂）数据读取所有相关表 NoLock 的情况，但仍然导致其他查询的阻塞。
Anyway - if you can't solve it at the data layer for whatever reason, how about
无论如何 - 如果您无法在数据层解决它，无论出于何种原因，怎么样
bool OK = false;
Random Rnd = new Random();
while(!OK)
{
try
{
rows = Command.ExecuteNonQuery();
OK = true;
}
catch(Exception exDead)
{
if(exDead.Message.ToLower().Contains("deadlock"))
System.Threading.Thread.Sleep(Rnd.Next(1000, 5000));
else
throw exDead;
}
}
回答by Neil
Building on @Sam's response, I present a general purpose retry wrapper method:
基于@Sam 的响应，我提出了一个通用的重试包装方法：
private static T Retry<T>(Func<T> func)
{
int count = 3;
TimeSpan delay = TimeSpan.FromSeconds(5);
while (true)
{
try
{
return func();
}
catch(SqlException e)
{
--count;
if (count <= 0) throw;
if (e.Number == 1205)
_log.Debug("Deadlock, retrying", e);
else if (e.Number == -2)
_log.Debug("Timeout, retrying", e);
else
throw;
Thread.Sleep(delay);
}
}
}
private static void Retry(Action action)
{
Retry(() => { action(); return true; });
}
// Example usage
protected static void Execute(string connectionString, string commandString)
{
_log.DebugFormat("SQL Execute \"{0}\" on {1}", commandString, connectionString);
Retry(() => {
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(commandString, connection))
command.ExecuteNonQuery();
});
}
protected static T GetValue<T>(string connectionString, string commandString)
{
_log.DebugFormat("SQL Scalar Query \"{0}\" on {1}", commandString, connectionString);
return Retry(() => {
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(commandString, connection))
{
object value = command.ExecuteScalar();
if (value is DBNull) return default(T);
return (T) value;
}
});
}
