知道何时重试或从C＃调用SQL Server时失败？ [英] Know when to retry or fail when calling SQL Server from C#?
问题描述
我有从一个有些脆弱的环境承载的SQL Server获取数据C＃应用程序。有什么我可以做，以解决环境问题的，所以我需要为妥善地处理它们成为可能。
要做到这一点，我要重试是基础设施故障，如网络故障，结果操作，SQL服务器将脱线，因为他们正在重新启动，查询超时等。与此同时，我不不想，如果他们已经失败，逻辑错误重试查询。我只是想那些泡沫异常到客户端。
我的问题是：什么是环境问题（丢失的连接，超时）和其他类型的异常来区分的最好方式（这样的事情会发生，即使环境是稳定的逻辑错误）
有没有在C＃中处理这样的事情一个常用的模式呢？例如，有没有我可以检查SqlConnection对象检测失败连接上的属性？如果没有，什么是解决这个问题的最好方法？
对于什么是值得的，我的code不是什么特别的东西：
使用（SqlConnection的连接=新的SqlConnection（myConnectionString））
使用（的SqlCommand命令= connection.CreateCommand（））
{
command.CommandText = mySelectCommand;
connection.Open（）; 使用（SqlDataReader的读卡器= Command.ExecuteReader却（））
{
而（reader.Read（））
{
//使用返回的数据进行处理。
}
}
}
其中单的SQLException （5月）封装多个SQL Server错误。您可以通过它们与错误属性迭代。每个错误是 SQLERROR ：
的foreach（在exception.Errors SQLERROR错误）
每个 SQLERROR 有您可以使用大约一个类属性确定是否可以重试或没有（在你重试的情况下，如果你要重新连接太）。从<一个href=\"http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.class%28v=vs.110%29.aspx\"相对=nofollow> MSDN ：
-
类＆LT; 10对你那么信息传递（可能），如果一开始你没有正确的输入则无法重试错误。 -
类11〜16由用户生成的，那么可能会再次你不能做任何事情，如果用户第一次不纠正他的投入。请注意，第16类，包括很多的临时的错误和类13是死锁（感谢EVZ），所以如果你一个处理它们一个你可以排除这些类。 -
类17〜24是通用的硬件/软件错误，你可能会重试。当类20或更高版本，你必须重新连接过。需要注意的是22和23可能是严重的硬件/软件错误，你可能直接停止努力。 24表示媒体错误（东西用户应该被警告，但您可以在情况下，它只是一个临时的错误重试）。
您可以找到每个类的这里。
在一般来说，如果你处理与它们的类错误，你将不再需要确切地知道每个错误（使用 error.Number 属性或例外。数这仅仅是第一个 SQLERROR 在该列表）的快捷方式。这有时，它不是很有用（或无法恢复的错误），您可能会重试的缺点。我建议在两步接近
- 检查已知错误codeS（列表错误codeS与
SELECT * FROM master.sys.messages），看你要处理什么（知道怎么样）。这种观点包含了所有支持语言的信息，所以你可能需要通过msglangid过滤它们列（例如1033英语）。 - 对于一切依靠错误类别，重试当
类13或高于16（如果20或更高的重新连接）。 - 严重性错误高于21（22，23和24）是严重的错误，等待一点不会解决问题（数据库本身也可能会损坏）。
策略重试取决于你在处理错误：免费资源，等待挂起操作完成，采取替代措施等。一般来说，你应该只重试，如果全部错误重试的
布尔rebuildConnection = TRUE; //首先尝试连接必须是打开的for（int i = 0; I＆LT; MaximumNumberOfRetries ++我）{
尝试{
//（RE）创建连接到SQL Server
如果（rebuildConnection）{
如果（连接！= NULL）
connection.Dispose（）; //创建连接并打开它...
} //执行你的任务 //没有例外，任务已经完成
打破;
}
赶上（SQLEXCEPTION E）{
如果（e.Errors.Cast＆所述; SQLERROR＆GT;（）所有（X =方式＆gt; CanRetry（X）））{
// 该怎么办？处理，在这里，还检查Number属性。
//对于Class＆LT; 20，你可以简单的Thread.Sleep（DelayOnError）; rebuildConnection = e.Errors
.Cast＆LT;＆SQLERROR GT;（）
。任何（X =＆GT; x.Class＆GT = 20）; 继续;
} 扔;
}
}
在总结一切尝试 / 最后来正确处理连接。有了这个简单的假天真 CanRetry（）功能：
私有静态只读INT [] = RetriableClasses {13，16，17，18，19，20，21，22，24};私人静态布尔CanRetry（SQLERROR错误）{
//如果你只想处理众所周知的错误使用此开关，
//如果你想永远重试将其删除。 黑名单的做法可能
//也行：返回false时，你确定你不能从一个恢复
//错误并依靠类为别的。
开关（error.Number）{
//处理众所周知错误codeS，
} //处理未知错误严重性21或更少。 22个或更多
//表明，需要手工修复一个严重的错误。
// 24表示的介质错误。它们是严重的错误（应该
//也被通知），但我们可能会重试...
返回RetriableClasses.Contains（error.Class）; // LINQ ...
}
找非关键性错误列表<一个有些pretty棘手的方式href=\"http://stackoverflow.com/questions/1980937/list-of-sql-server-errors-that-should-be-retried\">here.
我通常嵌入这一切（样板）code在一个方法（在那里我可以的隐藏的所有的脏东西的完成创建/配置/重新连接）与此签名：
公共静态无效的尝试（
FUNC＆LT; SqlConnection的＆GT; connectionFactory的，
动作＆LT;＆的SqlCommand GT;演员）;
要使用这样的：
尝试（
（）=＆GT;新的SqlConnection（的connectionString）
CMD =＆GT; {
cmd.CommandText =SELECT * FROM master.sys.messages
使用（VAR读卡器= cmd.ExecuteReader（））{
// 做东西
}
}）;
请注意，骨架（重试错误），也可以用来当你不使用SQL Server的工作（实际上它可以被用于许多其它操作，例如I / O和网络相关的东西，所以我建议写一般的功能和广泛的重复使用）。
I have a C# application that fetches data from SQL Server hosted in a somewhat flaky environment. There is nothing I can do to address the environmental issues, so I need to handle them as gracefully as possible.
To do so, I want to retry operations that are the result of infrastructure failures such as network glitches, SQL servers going off-line because they're being rebooted, query time-outs etc. At the same time, I don't want to retry queries if they've failed for logical errors. I just want those to bubble the exception up to the client.
My question is this: what is the best way to distinguish between environmental problems (lost connections, time-outs) and other kinds of exceptions (things like logical errors that would have happened even if the environment was stable).
Is there a commonly used pattern in C# for dealing with things like this? For example, is there a property I can check on the SqlConnection object to detect failed connections? If not, what is the best way to approach this problem?
For what it is worth, my code isn't anything special:
using (SqlConnection connection = new SqlConnection(myConnectionString))
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = mySelectCommand;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Do something with the returned data.
}
}
}
One single SqlException (may) wraps multiple SQL Server errors. You can iterate through them with Errors property. Each error is SqlError:
foreach (SqlError error in exception.Errors)
Each SqlError has a Class property you can use to roughly determine if you can retry or not (and in case you retry if you have to recreate connection too). From MSDN:
Class< 10 is for errors in information you passed then (probably) you can't retry if first you don't correct inputs.Classfrom 11 to 16 are "generated by user" then probably again you can't do anything if user first doesn't correct his inputs. Please note that class 16 includes many temporary errors and class 13 is for deadlocks (thanks to EvZ) so you may exclude these class if you handle them one by one.Classfrom 17 to 24 are generic hardware/software errors and you may retry. WhenClassis 20 or higher you have to recreate connection too. Note that 22 and 23 may be serious hardware/software errors and you may directly stop trying. 24 indicates a media error (something user should be warned but you may retry in case it was just a "temporary" error).
You can find a more detailed description of each class here.
In general if you handle errors with their class you won't need to know exactly each error (using error.Number property or exception.Number which is just a shortcut for first SqlError in that list). This has the drawback that you may retry when it's not useful (or error can't be recovered). I'd suggest a two steps approach:
- Check for known error codes (list error codes with
SELECT * FROM master.sys.messages) to see what you want to handle (knowing how). That view contains messages in all supported languages so you may need to filter them bymsglangidcolumn (for example 1033 for English). - For everything else rely on error class, retrying when
Classis 13 or higher than 16 (and reconnecting if 20 or higher). - Errors with severity higher than 21 (22, 23 and 24) are serious errors and little waiting won't fix that problems (database itself may also be damaged).
Strategy for retrying depends on error you're handling: free resources, wait for a pending operation to complete, take an alternative action, etc. In general you should retry only if all errors are "retry-able":
bool rebuildConnection = true; // First try connection must be open
for (int i=0; i < MaximumNumberOfRetries; ++i) {
try {
// (Re)Create connection to SQL Server
if (rebuildConnection) {
if (connection != null)
connection.Dispose();
// Create connection and open it...
}
// Perform your task
// No exceptions, task has been completed
break;
}
catch (SqlException e) {
if (e.Errors.Cast<SqlError>().All(x => CanRetry(x))) {
// What to do? Handle that here, also checking Number property.
// For Class < 20 you may simply Thread.Sleep(DelayOnError);
rebuildConnection = e.Errors
.Cast<SqlError>()
.Any(x => x.Class >= 20);
continue;
}
throw;
}
}
Wrap everything in try/finally to properly dispose connection. With this simple-fake-naive CanRetry() function:
private static readonly int[] RetriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 };
private static bool CanRetry(SqlError error) {
// Use this switch if you want to handle only well-known errors,
// remove it if you want to always retry. A "blacklist" approach may
// also work: return false when you're sure you can't recover from one
// error and rely on Class for anything else.
switch (error.Number) {
// Handle well-known error codes,
}
// Handle unknown errors with severity 21 or less. 22 or more
// indicates a serious error that need to be manually fixed.
// 24 indicates media errors. They're serious errors (that should
// be also notified) but we may retry...
return RetriableClasses.Contains(error.Class); // LINQ...
}
Some pretty tricky ways to find list of non critical errors here.
Usually I embed all this (boilerplate) code in one method (where I can hide all the dirty things done to create/dispose/recreate connection) with this signature:
public static void Try(
Func<SqlConnection> connectionFactory,
Action<SqlCommand> performer);
To be used like this:
Try(
() => new SqlConnection(connectionString),
cmd => {
cmd.CommandText = "SELECT * FROM master.sys.messages";
using (var reader = cmd.ExecuteReader()) {
// Do stuff
}
});
Please note that skeleton (retry on error) can be used also when you're not working with SQL Server (actually it can be used for many other operations like I/O and network related stuff so I'd suggest to write a general function and to reuse it extensively).
这篇关于知道何时重试或从C＃调用SQL Server时失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
