读书频道 > 数据库 > SQL Server > SQL Server 2012实施与管理实战指南
4.4 Connection Timeout和Command Timeout
2013-04-08 13:41:10     我来说两句 
收藏    我要投稿   
本书主要面向对Microsoft SQL Server有一定基础的数据库系统管理人员和开发人员,针对他们在日常工作中可能遇到的种种困扰提出解决方案。本书讨论的主题不是一般的功能介绍,也不偏重于理论讨论,而是面向实践...  立即去当当网订购

每次对数据库连接时,我们有时候会碰到连接超时或者命令超时,这两个超时是不一样的。以ADO.NET为例,当客户端和服务器端连接时,碰到的超时情况主要有下面几种:

当从连接池获取一个连接时,碰到超时。

当建立一个全新连接(而不是从连接池获取)时,碰到超时。

当发送一个命令(command)到SQL Server时,超时。

当发送命令(连接字符串带有“context connection=true”属性)到SQL Server时,超时。

当不是显示的发送一个命令(implicitly)到SQL Server时,碰到超时。

当执行异步命令时,(BeginExecute)碰到超时。

当从服务器端,获取行时,碰到超时。

当用Bulk copy方式,上传数据时,碰到超时。

这些超时主要是通过连接字符串中的Connect Timeout和SqlCommand.CommandTimeout来进行控制。前面两种是登录超时由Connection Timeout来决定什么时候超时,后面几种是命令超时由Command Timeout来决定什么时候超时。

以下面的测试代码为例,NA是一个不存在的服务器,在连接字符串设定connect timeout=0,则下面代码会无限等待。默认的情况下,会在15秒后(默认timeout值)抛出错误。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;

namespace ADONET
{
class Program
{
static public SqlConnection conn;
static public SqlCommand cmd;
static public SqlDataAdapter DA;
static public DataSet ds;

static void Main(string[] args)
{

conn = new SqlConnection("Data Source=NA;User Id=sa; Password=xx;connection timeout=0");
cmd = new SqlCommand("WAITFOR DELAY ’00:01’", conn);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}

连接超时的错误信息如下:
Unhandled Exception: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
 at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternal ConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
 at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo server Info, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
 at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
 at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
 at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
 at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
 at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
 at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
 at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
 at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
 at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owning Connection)
 at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outer Connection, DbConnectionFactory connectionFactory)
 at System.Data.SqlClient.SqlConnection.Open()

依照以上的代码为例,改正服务器名后执行语句,读者会发现应用程序会抛出如下命令超时错误。这是因为Command Timeout默认值是30秒,而我们的语句执行需要1分钟。如果需要延长命令的超时时间,我们要修改cmd的超时属性。
Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 at ADONET.Program.Main(String[] args) in D:\TEST\MDACIntroduction\Pooling\ADONET - Copy\ADONET\Program.cs:line 23

对于ADO或OLEDB,连接超时可以通过连接字符串中的Connect Timeout属性来控制。如下面的代码,NA是一个不存在的服务器,在100秒后连接会报告失败。如果不做设定,默认的连接Timeout值为15。
Dim index
Dim a, b

Dim v1, v2

set conn = CreateObject("ADODB.CONNECTION")
set rs = CreateObject("ADODB.Recordset")
set cmd = CreateObject("ADODB.Command")

a = timer
'On Error Resume Next'
conn.Open "Provider=sqloledb; Data Source=NA; User Id=sa;Password=xx;connect timeout=100"
cmd.commandType = 1
cmd.ActiveConnection = conn
cmd.commandText = "WAITFOR DELAY '00:01'"
cmd.execute

b = timer
msgbox (b-a)

其错误的信息为:

Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied

改正上述ADO代码的服务器名,则过30秒后会抛出如下命令超时错误:

Microsoft OLE DB Provider for SQL Server: Query timeout expired

如需要延长命令超时时间,则需要更改cmd的超时属性。

对于ODBC,连接的超时不能通过连接字符串来控制,必须通过修改代码里的SQL_ATTR_LOGIN_ TIMEOUT属性来控制连接超时,默认值是15秒。
#include "stdafx.h"
#include <Windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <stdio.h>
#include <ODBCSS.h>
#include <iostream>
#include <odbcinst.h>
using namespace std;
// display error message when an error occurs
void displayErrorMessage(SQLSMALLINT handleType, SQLHANDLE handle)
{

RETCODE rc;
const int NAME_LEN = 300;
SQLTCHAR state[NAME_LEN];
SQLTCHAR errorInfo[NAME_LEN];
SQLINTEGER nativeError;
SQLSMALLINT cbLength;

 SQLINTEGER numRecords;

  rc = SQLGetDiagRec(handleType, handle, 1, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
  printf("the error message is %S\n", errorInfo); 

 SQLGetDiagField(handleType, handle, 0, SQL_DIAG_NUMBER, &numRecords, SQL_IS_INTEGER, NULL);
 for (int i=1; i<=numRecords; i++)
 {
  rc = SQLGetDiagRec(handleType, handle, i, state, &nativeError, errorInfo, NAME_LEN, &cbLength);
  printf("the error message is %S\n", errorInfo);

  printf("the error state is: %S\n", state);

  printf("the nativeError state is %d\n", nativeError);

 } 
}

int _tmain(int argc, _TCHAR* argv[])
{

 HENV henv = NULL;
 HDBC hdbc = NULL;
 HSTMT hstmt = NULL;

 RETCODE rc;
// 分配环境句柄
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }

// 设定属性
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }

// 分配连接句柄
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) { return -1; }
 
 SQLUINTEGER value = 3;
  
 rc = SQLSetConnectAttr(
  hdbc,
  SQL_ATTR_LOGIN_TIMEOUT,
  (SQLUINTEGER *)value,
  sizeof(value));

 // 使用SQLDriverConnect对数据库进行连接
 rc = SQLDriverConnect(
  hdbc,
  NULL, 
  (SQLTCHAR *) TEXT("Driver={SQL Server};Server=NA;Trusted_Connection=Yes;"), 
  SQL_NTS,
  NULL,
  0,
  NULL,
  SQL_DRIVER_NOPROMPT
  );

 if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
 { 
  displayErrorMessage(SQL_HANDLE_DBC, hdbc);
  return -1;
 }

 rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

 value = 10;
 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_QUERY_TIMEOUT, (SQLUINTEGER*) value, sizeof(value));

 TCHAR cmd[1000];
 wcscpy_s(cmd, TEXT("WAITFOR DELAY '00:01'"));
 rc = SQLExecDirect(hstmt, (SQLTCHAR*)cmd, wcslen(cmd));

 if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
 { 
  displayErrorMessage(SQL_HANDLE_STMT, hstmt);
  return -1;
 }

 SQLFreeStmt(hstmt, SQL_CLOSE);
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;

}

当连接超时,ODBC可能抛出的错误如下:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

改正前面ODBC代码的服务器名,ODBC会抛出如下的命令超时错误。ODBC默认的Command Timeout值是0,所以如果不做上述设定,查询会一直进行下去,直到返回。我们可以给SQL_ATTR_QUERY_ TIMEOUT设定一个合适的值。

[Microsoft][ODBC SQL Server Driver]Query timeout expired

表4-6显示了不同编程方式,对Connection Timeout和Command Timeout的不同设定方法。

表4-6  Connection Timeout和Command Timeout


编程方式 Connection Timeout Command Timeout
ADO.NET 默认值为15秒,可在连接字符串指定 默认值为30秒,可在command对象对其进行修改
ADO/OLEDB 默认值为15秒,可在连接字符串指定 默认值为30秒,可在command对象对其进行修改
ODBC 缺省值为15秒,可通过设定SQL_ATTR_LOGIN_ TIMEOUT属性对其进行修改 默认值为0,也就是没有超时。可通过设定SQL_ATTR_QUERY_TIMEOUT对其进行修改
 
点击复制链接 与好友分享!回本站首页
分享到: 更多
您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:4.3 连接池
下一篇:4.5 使用BID Tracing来跟踪检查应用程序的执行
相关文章
图文推荐
3.8 实训项目
3.6 实现数据完整性
3.5.5 外键约束
3.5.3 检查约束
排行
热门
文章
下载
读书

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做最好的IT技术学习网站