Connection Pooling problem in .NET v1.1

  • Thread starter Thread starter Jerry Hu
  • Start date Start date
J

Jerry Hu

I was trying to get around the problem that the connection pool could become
corrupt when server closes the connection, because the pool does not know a
connection is bad, and keeps handing out the same bad connection every time,
even using it gets exception.

I tried to follow the post from David Browne (11/5/2003) and Hussein
Abuthuraya[MSFT] (11/5/2003), but got nowhere because:

a) Open() does not through an exception if a connection is "bad", so you can
not create loop around that;

b) not closing/disposing the "bad" connection, the pool would eventually
reach the max and Open() would raise an InvalidOperationException (timeout);

c) closing the "bad" connection, new Open() would always get the bad one,
until the "lifetime" is over.

the problem becomes much more significant when we put my .NET asmx into
production environment (and I donot recycle the AppDomain). So, is there a
fix for this problem? beside using a new pool altogether?


Thanks,
Jerry


PS. sample code to illustrate the problem (borrowed and modified from
someone else's post). also, one can use TcpView from
http://www.sysinternals.com/ntw2k/source/tcpview.shtml to close an oracle
connection (port 1521) and a connection will become "bad".


using System;
using System.Data;
using System.Data.OracleClient;

namespace OracleTest
{
class OracleTest
{
private const string DSN = "Data Source=oracle;Password=pass;User ID=user;"
+
"Connection Lifetime=20;Pooling=True;Max Pool Size=4;";

[STAThread]
static void Main(string[] args)
{
OracleConnection oConn = null;

// consume 3 connections
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("1 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("2 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("3 Connected.");

for ( ; ; Console.ReadLine())
{
do
{
try
{
oConn = new OracleConnection(DSN);

Console.Write("Connecting... ");
oConn.Open();
Console.WriteLine("Connected.");

using (OracleCommand oCmd = oConn.CreateCommand())
{
oCmd.CommandText = "select sysdate from dual";
oCmd.CommandType = CommandType.Text;
using (OracleDataReader oDr = oCmd.ExecuteReader())
{
if (oDr.Read())
{
Console.WriteLine(((DateTime)oDr["SYSDATE"]).ToLongTimeString());
}
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception: {0}", e.Message);
//oConn.Close(); // should we close the bad one? or not?
continue; // try to get a good connection
}

oConn.Close();
break; // processing is done
} while (true);

Console.WriteLine("Waiting - press enter to continue.");
}
}
}
}
 
Jerry,
you can contact PSS for a v1.1 QFE for this bug:
830173 FIX: OracleClient does not dispose a pooled connection after an
exception has occurred.

Like you say the only other solution is to use a brand new pool, the
existing pool will be gracefully cleaned after a few minutes.
Thanks,


--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Jerry Hu said:
I was trying to get around the problem that the connection pool could become
corrupt when server closes the connection, because the pool does not know a
connection is bad, and keeps handing out the same bad connection every time,
even using it gets exception.

I tried to follow the post from David Browne (11/5/2003) and Hussein
Abuthuraya[MSFT] (11/5/2003), but got nowhere because:

a) Open() does not through an exception if a connection is "bad", so you can
not create loop around that;

b) not closing/disposing the "bad" connection, the pool would eventually
reach the max and Open() would raise an InvalidOperationException (timeout);

c) closing the "bad" connection, new Open() would always get the bad one,
until the "lifetime" is over.

the problem becomes much more significant when we put my .NET asmx into
production environment (and I donot recycle the AppDomain). So, is there a
fix for this problem? beside using a new pool altogether?


Thanks,
Jerry


PS. sample code to illustrate the problem (borrowed and modified from
someone else's post). also, one can use TcpView from
http://www.sysinternals.com/ntw2k/source/tcpview.shtml to close an oracle
connection (port 1521) and a connection will become "bad".


using System;
using System.Data;
using System.Data.OracleClient;

namespace OracleTest
{
class OracleTest
{
private const string DSN = "Data Source=oracle;Password=pass;User ID=user;"
+
"Connection Lifetime=20;Pooling=True;Max Pool Size=4;";

[STAThread]
static void Main(string[] args)
{
OracleConnection oConn = null;

// consume 3 connections
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("1 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("2 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("3 Connected.");

for ( ; ; Console.ReadLine())
{
do
{
try
{
oConn = new OracleConnection(DSN);

Console.Write("Connecting... ");
oConn.Open();
Console.WriteLine("Connected.");

using (OracleCommand oCmd = oConn.CreateCommand())
{
oCmd.CommandText = "select sysdate from dual";
oCmd.CommandType = CommandType.Text;
using (OracleDataReader oDr = oCmd.ExecuteReader())
{
if (oDr.Read())
{
Console.WriteLine(((DateTime)oDr["SYSDATE"]).ToLongTimeString());
}
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception: {0}", e.Message);
//oConn.Close(); // should we close the bad one? or not?
continue; // try to get a good connection
}

oConn.Close();
break; // processing is done
} while (true);

Console.WriteLine("Waiting - press enter to continue.");
}
}
}
}
Click to expand...
 
Thanks Angel. after applying the qfe, now the connection pooling is at least
usable.


Jerry


Angel Saenz-Badillos said:
Jerry,
you can contact PSS for a v1.1 QFE for this bug:
830173 FIX: OracleClient does not dispose a pooled connection after an
exception has occurred.

Like you say the only other solution is to use a brand new pool, the
existing pool will be gracefully cleaned after a few minutes.
Thanks,


--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Jerry Hu said:
I was trying to get around the problem that the connection pool could become
corrupt when server closes the connection, because the pool does not
Click to expand...
know
a
connection is bad, and keeps handing out the same bad connection every time,
even using it gets exception.

I tried to follow the post from David Browne (11/5/2003) and Hussein
Abuthuraya[MSFT] (11/5/2003), but got nowhere because:

a) Open() does not through an exception if a connection is "bad", so you can
not create loop around that;

b) not closing/disposing the "bad" connection, the pool would eventually
reach the max and Open() would raise an InvalidOperationException (timeout);

c) closing the "bad" connection, new Open() would always get the bad one,
until the "lifetime" is over.

the problem becomes much more significant when we put my .NET asmx into
production environment (and I donot recycle the AppDomain). So, is there a
fix for this problem? beside using a new pool altogether?


Thanks,
Jerry


PS. sample code to illustrate the problem (borrowed and modified from
someone else's post). also, one can use TcpView from
http://www.sysinternals.com/ntw2k/source/tcpview.shtml to close an oracle
connection (port 1521) and a connection will become "bad".


using System;
using System.Data;
using System.Data.OracleClient;

namespace OracleTest
{
class OracleTest
{
private const string DSN = "Data Source=oracle;Password=pass;User ID=user;"
+
"Connection Lifetime=20;Pooling=True;Max Pool Size=4;";

[STAThread]
static void Main(string[] args)
{
OracleConnection oConn = null;

// consume 3 connections
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("1 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("2 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("3 Connected.");

for ( ; ; Console.ReadLine())
{
do
{
try
{
oConn = new OracleConnection(DSN);

Console.Write("Connecting... ");
oConn.Open();
Console.WriteLine("Connected.");

using (OracleCommand oCmd = oConn.CreateCommand())
{
oCmd.CommandText = "select sysdate from dual";
oCmd.CommandType = CommandType.Text;
using (OracleDataReader oDr = oCmd.ExecuteReader())
{
if (oDr.Read())
{
Console.WriteLine(((DateTime)oDr["SYSDATE"]).ToLongTimeString());
}
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception: {0}", e.Message);
//oConn.Close(); // should we close the bad one? or not?
continue; // try to get a good connection
}

oConn.Close();
break; // processing is done
} while (true);

Console.WriteLine("Waiting - press enter to continue.");
}
}
}
}
Click to expand...
Click to expand...
 
Glad to hear you got it working.

Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.


Jerry Hu said:
Thanks Angel. after applying the qfe, now the connection pooling is at least
usable.


Jerry


Angel Saenz-Badillos said:
Jerry,
you can contact PSS for a v1.1 QFE for this bug:
830173 FIX: OracleClient does not dispose a pooled connection after an
exception has occurred.

Like you say the only other solution is to use a brand new pool, the
existing pool will be gracefully cleaned after a few minutes.
Thanks,


--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Jerry Hu said:
I was trying to get around the problem that the connection pool could become
corrupt when server closes the connection, because the pool does not
Click to expand...
know
a
connection is bad, and keeps handing out the same bad connection every time,
even using it gets exception.

I tried to follow the post from David Browne (11/5/2003) and Hussein
Abuthuraya[MSFT] (11/5/2003), but got nowhere because:

a) Open() does not through an exception if a connection is "bad", so
Click to expand...
you
can
not create loop around that;

b) not closing/disposing the "bad" connection, the pool would eventually
reach the max and Open() would raise an InvalidOperationException (timeout);

c) closing the "bad" connection, new Open() would always get the bad one,
until the "lifetime" is over.

the problem becomes much more significant when we put my .NET asmx into
production environment (and I donot recycle the AppDomain). So, is
Click to expand...
Click to expand...
there
a
fix for this problem? beside using a new pool altogether?


Thanks,
Jerry


PS. sample code to illustrate the problem (borrowed and modified from
someone else's post). also, one can use TcpView from
http://www.sysinternals.com/ntw2k/source/tcpview.shtml to close an oracle
connection (port 1521) and a connection will become "bad".


using System;
using System.Data;
using System.Data.OracleClient;

namespace OracleTest
{
class OracleTest
{
private const string DSN = "Data Source=oracle;Password=pass;User ID=user;"
+
"Connection Lifetime=20;Pooling=True;Max Pool Size=4;";

[STAThread]
static void Main(string[] args)
{
OracleConnection oConn = null;

// consume 3 connections
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("1 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("2 Connected.");
oConn = new OracleConnection(DSN);
oConn.Open();
Console.WriteLine("3 Connected.");

for ( ; ; Console.ReadLine())
{
do
{
try
{
oConn = new OracleConnection(DSN);

Console.Write("Connecting... ");
oConn.Open();
Console.WriteLine("Connected.");

using (OracleCommand oCmd = oConn.CreateCommand())
{
oCmd.CommandText = "select sysdate from dual";
oCmd.CommandType = CommandType.Text;
using (OracleDataReader oDr = oCmd.ExecuteReader())
{
if (oDr.Read())
{
Console.WriteLine(((DateTime)oDr["SYSDATE"]).ToLongTimeString());
}
}
}
}
catch (OracleException e)
{
Console.WriteLine("Exception: {0}", e.Message);
//oConn.Close(); // should we close the bad one? or not?
continue; // try to get a good connection
}

oConn.Close();
break; // processing is done
} while (true);

Console.WriteLine("Waiting - press enter to continue.");
}
}
}
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top