Transport level error with .NET 2.0

aliostad · Oct 16, 2006

Has connection pooling implementation changed in .NET 2.0?!

I was happily running a code in 1.1 and now it seems that I have a
problem and receive this error:

System.Data.SqlClient.SqlException : A transport-level error has
occurred when sending the request to the server. (provider: TCP
Provider, error: 0 - An existing connection was forcibly closed by the
remote host.)

The code I am running is a database unit test whereby for every test I
attach my database, run the test and then detach it. Each test, running
on its own works fine. When I run the tests in succession (eg two tests
in the same test fixture by clicking on the test fixture on NUnit GUI)
it fails on the second test with the error above.

The tests open and EXPLICITLY close their connections via a helper
class and never keep any connection open.

Now, when I set pooling=false in my connection string, it runs fine and
now problem at all. To me it seems that .NET 2.0 not only pools the
coonections, but also caches them and since database is restarted,
connection is not valid anymore. If that is the case, this is a major
change from 1.1 and to me it is a bug, even if it is by design. Because
it introduces the potential problem for applications if server is
restarted so they must implement handle an exception that used to be
gracefully handled by pooling itself.

Guys from MS! Could you please shed some light on this?

Here is the trace. I can see a change of implementation from 1.1:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at
System.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte
flushMode)
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray,
Int32 timeout, Boolean inSchema, SqlNotificationRequest
notificationRequest, TdsParserStateObject stateObj)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Data.SqlClient.SqlCommand.DeriveParameters()
at
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand
command)
at
DRS.Common.Data.SqlHelperParameterCache.DiscoverSpParameterSet(String
connectionString, String spName, Boolean includeReturnValueParameter,
Object[] parameterValues)
at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
connectionString, String spName, Boolean includeReturnValueParameter)
at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
connectionString, String spName)
at DRS.Common.Data.SqlHelper.ExecuteReader(String connectionString,
String spName, Object[] parameterValues)
at DRS.Common.Data.SqlHelper.ExecuteEntity(String connectionString,
String spName, Type entityType, Object[] parameterValues)
at DRS.ScriptMarking.WaveData.GetCurrentWave(Int32 ctrId, Int32
comId) in C:\Projects\DRS Products\Script
Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.Unit\WaveData.cs:line
20
at DRS.ScriptMarking.WaveDataTest.TestSetWaveAsExported() in
C:\Projects\DRS Products\Script
Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.UnitTest\WaveDataTest.cs:line
20

aliostad · Oct 17, 2006

Anyone please? Have we got no MVPs here? Appreciate your comments.

Cheers

William \(Bill\) Vaughn · Oct 18, 2006

Ah, yes. ADO.NET 2.0 did redesign the connection pooling mechanism. First,
in any version, if the server or LAN goes down, the connections in the pool
are worthless. These are built based on internal handles that are only valid
as long as the server and the physical connection to the server remains
viable. No, when the server or application is restarted, there is no attempt
(in any version) to rebuild or refresh the pool(s).

In 2.0 the mechanism to detect if a connection is "bad" has been changed.
Now, when a bad pooled connection is detected, the SqlClient provider
flushes the pool. This makes sense because all of the connections are
identical--they all use the same connection string and transaction context.
This might have the effect you describe.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------

Has connection pooling implementation changed in .NET 2.0?!

I was happily running a code in 1.1 and now it seems that I have a
problem and receive this error:

System.Data.SqlClient.SqlException : A transport-level error has
occurred when sending the request to the server. (provider: TCP
Provider, error: 0 - An existing connection was forcibly closed by the
remote host.)

The code I am running is a database unit test whereby for every test I
attach my database, run the test and then detach it. Each test, running
on its own works fine. When I run the tests in succession (eg two tests
in the same test fixture by clicking on the test fixture on NUnit GUI)
it fails on the second test with the error above.

The tests open and EXPLICITLY close their connections via a helper
class and never keep any connection open.

Now, when I set pooling=false in my connection string, it runs fine and
now problem at all. To me it seems that .NET 2.0 not only pools the
coonections, but also caches them and since database is restarted,
connection is not valid anymore. If that is the case, this is a major
change from 1.1 and to me it is a bug, even if it is by design. Because
it introduces the potential problem for applications if server is
restarted so they must implement handle an exception that used to be
gracefully handled by pooling itself.

Guys from MS! Could you please shed some light on this?

Here is the trace. I can see a change of implementation from 1.1:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at
System.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte
flushMode)
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray,
Int32 timeout, Boolean inSchema, SqlNotificationRequest
notificationRequest, TdsParserStateObject stateObj)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Data.SqlClient.SqlCommand.DeriveParameters()
at
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand
command)
at
DRS.Common.Data.SqlHelperParameterCache.DiscoverSpParameterSet(String
connectionString, String spName, Boolean includeReturnValueParameter,
Object[] parameterValues)
at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
connectionString, String spName, Boolean includeReturnValueParameter)
at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
connectionString, String spName)
at DRS.Common.Data.SqlHelper.ExecuteReader(String connectionString,
String spName, Object[] parameterValues)
at DRS.Common.Data.SqlHelper.ExecuteEntity(String connectionString,
String spName, Type entityType, Object[] parameterValues)
at DRS.ScriptMarking.WaveData.GetCurrentWave(Int32 ctrId, Int32
comId) in C:\Projects\DRS Products\Script
Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.Unit\WaveData.cs:line
20
at DRS.ScriptMarking.WaveDataTest.TestSetWaveAsExported() in
C:\Projects\DRS Products\Script
Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.UnitTest\WaveDataTest.cs:line
20

aliostad · Oct 18, 2006

Bill, thanks a lot for taking notice of this post.

I do not see any problem with flushing all pooled connections if server
goes down. As you said, they are all bad and invalid and it is best to
reset the pool. However, I cannot see how this could explain why I
should get an exception while not holding on to any connection. Surely
all invalid connections have been flushed after server restart and with
asking for a

As I said, I use a helper class to open the connection, do the job and
then close it. This helper class was designed based on one the earlier
versions of Microsoft Data Access Block and we changed it slightly to
retunr us our own Entities.

In order to reproduce, it is enough to run this code in a console
application:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace ConnectionTest
{
class Program
{
static void Main(string[] args)
{
OpenConnectionDoSomethingAndClose();
Thread.Sleep(20000); // sleep 20 secs. Go and restart database
OpenConnectionDoSomethingAndClose(); // this will raise Transport
level error!!

}

private static void OpenConnectionDoSomethingAndClose()
{
const string CONNECTION_STRING =
"server=.;database=SMW;integrated security=true";
const string DO_SOMETHING = "SELECT GETDATE()";

SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
try
{
SqlCommand cm = new SqlCommand(DO_SOMETHING, cn);
DateTime now = (DateTime) cm.ExecuteScalar();
Console.WriteLine(now);
}
finally
{
cn.Close();
}
}

}
}

I am sorry Bill, but it does not look right to me. I can understand
that if i had kept a connection open, that connection would be useless
after server restart but here, I am opening a new connection. I mustn't
be given an error because server restarted or there was a network
glitch or ... as I have asked for a connection after all of this.

I might be mising something but pooling that used to hide all of this
now is returning me some errors that it used to deal with. My allegory
is buying something from a shop but shopkeepr asks for extra because
items in warehouse became wet due to a leakage of his warehouse roof! I
should not care about the warehouse and its items.

I appreciate your feedback and on how I need to proceed on this issue.

Many thanks
Ali Kheyrollahi
http://www.drs.co.uk

William \(Bill\) Vaughn · Oct 19, 2006

If the helper class does not handle exceptions correctly, it could leak
connections.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------

aliostad said:
Bill, thanks a lot for taking notice of this post.

I do not see any problem with flushing all pooled connections if server
goes down. As you said, they are all bad and invalid and it is best to
reset the pool. However, I cannot see how this could explain why I
should get an exception while not holding on to any connection. Surely
all invalid connections have been flushed after server restart and with
asking for a

As I said, I use a helper class to open the connection, do the job and
then close it. This helper class was designed based on one the earlier
versions of Microsoft Data Access Block and we changed it slightly to
retunr us our own Entities.

In order to reproduce, it is enough to run this code in a console
application:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace ConnectionTest
{
class Program
{
static void Main(string[] args)
{
OpenConnectionDoSomethingAndClose();
Thread.Sleep(20000); // sleep 20 secs. Go and restart database
OpenConnectionDoSomethingAndClose(); // this will raise Transport
level error!!

}

private static void OpenConnectionDoSomethingAndClose()
{
const string CONNECTION_STRING =
"server=.;database=SMW;integrated security=true";
const string DO_SOMETHING = "SELECT GETDATE()";

SqlConnection cn = new SqlConnection(CONNECTION_STRING);
cn.Open();
try
{
SqlCommand cm = new SqlCommand(DO_SOMETHING, cn);
DateTime now = (DateTime) cm.ExecuteScalar();
Console.WriteLine(now);
}
finally
{
cn.Close();
}
}

}
}

I am sorry Bill, but it does not look right to me. I can understand
that if i had kept a connection open, that connection would be useless
after server restart but here, I am opening a new connection. I mustn't
be given an error because server restarted or there was a network
glitch or ... as I have asked for a connection after all of this.

I might be mising something but pooling that used to hide all of this
now is returning me some errors that it used to deal with. My allegory
is buying something from a shop but shopkeepr asks for extra because
items in warehouse became wet due to a leakage of his warehouse roof! I
should not care about the warehouse and its items.

I appreciate your feedback and on how I need to proceed on this issue.

Many thanks
Ali Kheyrollahi
http://www.drs.co.uk

aliostad · Oct 20, 2006

Bill,

Let's forget my original problem and focus on the sample code I sent as
it reproduces my original problem and you can see all the code and no
magic is behind it.

This does not leak connections but causes exception while it should
have been handled at the pooling level. To be honst to me it looks like
pooling not only does not flush all similar connections, but also it
even does not remove invalid one.

Is this not a bug?

Thanks
Ali

William \(Bill\) Vaughn · Oct 21, 2006

Perhaps you should document this on the bug reporting site. (connect)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...

Transport level error with .NET 2.0

aliostad

aliostad

William \(Bill\) Vaughn

aliostad

William \(Bill\) Vaughn

aliostad

William \(Bill\) Vaughn