考虑常见的几种前端超时情况测试:
1. 命令超时:执行语句超时
2. 命令超时:未提交事务超时
3. 后台 kill 该连接
4. 网络延迟或丢包
5. 死锁
【命令超时:执行语句超时】
在代码中设置超时时间5秒,同时执行的sql语句中也编写脚本等待5秒;
using System;
using System.Data.SqlClient;
namespace dbConnectionTest
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Data source=1card1-hzc;Integrated Security=SSPI;Initial Catalog=DemoDB";
string sqlstring = "waitfor delay '00:00:05';SELECT [Name],[value] FROM dbo.TestTab";
using (SqlConnection conn = new SqlConnection(connstring))
{
try
{
SqlCommand cmd = new SqlCommand(sqlstring, conn);
cmd.CommandTimeout = 5;//命令超时时间
Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
conn.Open();
SqlDataReader r = cmd.ExecuteReader();
Console.WriteLine("name value");
while (r.Read())
Console.WriteLine("{0} {1}", r[0].ToString(), r[1].ToString());
}
catch (SqlException se)
{
Console.WriteLine(se);
}
finally
{
conn.Close();
}
Console.ReadLine();
}
}
}
}
结果错误如下:
CommandTimeout: 5
System.Data.SqlClient.SqlException (0x80131904): Timeout 时间已到。在操作完成之前超时时间已过或服务器未响应。 ---> System.ComponentModel.Win32Exception (0x80004005): 等待的操作过时。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader()
在 dbConnectionTest.Program.Main(String[] args) 位置 e:\TempFile\SSIS\ConnectPoolTest\ConnectPoolTest\Program.cs:行号 20
ClientConnectionId:8bef8555-f818-49fb-b0ba-473d7a31191a
【命令超时:未提交事务超时】
代码还是上面的代码。执行的 sql 脚本 和 超时设置如下。
string sqlstring = "SELECT [Name],[value] FROM dbo.TestTab";
cmd.CommandTimeout = 5;//命令超时时间
1. 首先在数据库中执行以下sql脚本。使用显式事务,但不关闭事务
begin tran
update [dbo].[TestTab] set value = 0 where Name = 'kk'
-- rollback tran
2. VS 中执行代码
结果错误如下:
CommandTimeout: 5
System.Data.SqlClient.SqlException (0x80131904): Timeout 时间已到。在操作完成之前超时时间已过或服务器未响应。 ---> System.ComponentModel.Win32Exception (0x80004005): 等待的操作过时。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader()
在 dbConnectionTest.Program.Main(String[] args) 位置 e:\TempFile\SSIS\ConnectPoolTest\ConnectPoolTest\Program.cs:行号 20
ClientConnectionId:6b476a0d-4fc1-4f99-8208-0d61d268d4e7
【后台 kill 该连接】
上面的代码改以下两部分:超时设置30秒,执行sql语句20秒,主要是有时间在数据库中查询并kill 掉该链接。
string sqlstring = "waitfor delay '00:00:20';SELECT [Name],[value] FROM dbo.TestTab";
cmd.CommandTimeout = 30;//命令超时时间
2. 在数据库中查找该连接并kill掉
select p.spid,p.lastwaittype,p.cmd,p.loginame,p.program_name,s.text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid >50 and s.text like '%waitfor delay%' and spid<>@@SPID
kill 66
结果错误如下:
CommandTimeout: 30
System.Data.SqlClient.SqlException (0x80131904): 当前命令发生了严重错误。应放弃任何可能产生的结果。
当前命令发生了严重错误。应放弃任何可能产生的结果。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader()
在 dbConnectionTest.Program.Main(String[] args) 位置 e:\TempFile\SSIS\ConnectPoolTest\ConnectPoolTest\Program.cs:行号 20
ClientConnectionId:ec8021d9-4fbb-4994-8e7b-1a4b95fcb1f2
【网络延迟或丢包】
1. 首先打开SqlServer 配置管理器,因本地测试,所以把 shared memory 、named pipes 禁用,只允许通过 TCP/IP 访问 SqlServer。
2. 下载 打开 clumsy ,对端口 1433 启用 延迟 或丢包!(更多参考 : Clumsy logo差网络环境模拟工具 Clumsy)
3. 执行以下脚本
using System;
using System.Data.SqlClient;
namespace dbConnectionTest
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Data source=1card1-hzc;Integrated Security=SSPI;Initial Catalog=DemoDB";
string sqlstring = "SELECT [Name],[value] FROM dbo.TestTab;";
using (SqlConnection conn = new SqlConnection(connstring))
{
try
{
SqlCommand cmd = new SqlCommand(sqlstring, conn);
cmd.CommandTimeout = 30;//命令超时时间
Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
conn.Open();
SqlDataReader r = cmd.ExecuteReader();
Console.WriteLine("name value");
while (r.Read())
Console.WriteLine("{0} {1}", r[0].ToString(), r[1].ToString());
}
catch (SqlException se)
{
Console.WriteLine(se);
}
finally
{
conn.Close();
}
Console.ReadLine();
}
}
}
}
结果错误如下:
CommandTimeout: 30
System.Data.SqlClient.SqlException (0x80131904): 连接超时时间已到。在尝试使用预登录握手确认时超过了此超时时间。这可能是
因为预登录握手失败或服务器未能及时响应。 尝试连接到此服务器时花费的持续时间是 - [Pre-Login] initialization=54072;handshake=25; ---> System.ComponentModel.Win32Exception (0x80004005): 等待的操作过时。
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
在 System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
在 System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
在 System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable)
在 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
在 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
在 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
在 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
在 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, BooleanredirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
在 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
在 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
在 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
在 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
在 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
在 System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
在 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
在 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
在 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
在 System.Data.SqlClient.SqlConnection.Open()
在 dbConnectionTest.Program.Main(String[] args) 位置 e:\TempFile\SSIS\ConnectPoolTest\ConnectPoolTest\Program.cs:行号 19
【死锁】
1. 先启用回 shared memory 、named pipes ,执行以下代码,sql脚本中设置等待10秒钟。
using System;
using System.Data.SqlClient;
namespace dbConnectionTest
{
class Program
{
static void Main(string[] args)
{
string connstring = @"Data source=1card1-hzc;Integrated Security=SSPI;Initial Catalog=DemoDB";
string sqlstring = "update dbo.TestTab set value=0;WAITFOR DELAY '00:00:10';update dbo.TestTab2 set value=0;";
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
SqlTransaction trans = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = trans;
cmd.CommandText = sqlstring;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (SqlException se)
{
trans.Rollback();
Console.WriteLine(se);
}
finally
{
conn.Close();
}
Console.ReadLine();
}
}
}
}
2. 紧接着在数据库中执行以下脚本,使事务交叉。
BEGIN TRAN
UPDATE dbo.TestTab2 SET value=1;
UPDATE dbo.TestTab SET value=1;
COMMIT TRAN
结果错误如下:(有时死锁是发生在 第二步,执行多几次使发生在第一步的代码中)
System.Data.SqlClient.SqlException (0x80131904): 事务(进程 ID 68)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
在 dbConnectionTest.Program.Main(String[] args) 位置 e:\TempFile\SSIS\ConnectPoolTest\ConnectPoolTest\Program.cs:行号 22
参考:
SqlConnection.BeginTransaction 方法 (IsolationLevel)
《易车网连接超时研究》