Connection Pooling problem in .NET v1.1

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.");
}
}
}
}
 
A

Angel Saenz-Badillos[MS]

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...
 
J

Jerry Hu

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...
 
A

Angel Saenz-Badillos[MS]

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Connection pooling parameters not working for Oracle connection 1
connection pooling vs session state 1
Oracle Data Provider for .NET: Connection request timed out 0
First connection to the database (Oracle) from a .Net console application takes 6 sec 3
Oracle connection Fails 2
Oracle Server connection from Windows ce 3
Forcing odp.net connection 1
Connection pool corrupt in Oracle provider 12

Top