G
Guest
We have come across what appears to be a problem with connection pooling in
the Oracle Provider for .NET, v1.1. I am using the latest 9i client and
hitting against various 9i servers running on Win2K.
When using connection pooling, if database connectivity is lost temporarily
(for example, restarting the service), any pool connections that error
during the interruption will never recover - Subsequent calls on the
connection result in the error, "ORA-03114: not connected to ORACLE". This
problem does not occur is pooling is disabled. Additionally, the problem
"eventually" corrects itself if a "Connection Lifetime" is specified for the
connection.
The issue was originally discovered in a service-based application that
would poll the database periodically. When the database was shutdown for
maintenance, the service would still attempt to connect. We found that if
pooling was enable, the service would not reconnect after the database was
brought back online. The following short block of code, executed in a
single process (the scope of the connection pool), will reproduce the issue.
Execute the block and everything works properly. Stop the Oracle service
and it will fail, as expected (the execute fails, not the open). However,
if the Oracle service is restarted, executing the block again will also
fail - and continue to fail. If connection lifetimes are specified, the
pool will eventually correct itself as the underlying connections are
terminated and replaced. My best guess is that, when a connection is
returned to the pool, the "error state" is being maintained by either the
underlying .NET classes or the OCI.
We are currently using the connection lifetime as a work around, but feel
that there is an issue with the connection pool that needs to be addressed.
try
{
using(OracleConnection connection = new OracleConnection("Data
Source=DBA9i;Pooling=true;Enlist=false;User Id=myid;Password=mypwd;Min Pool
Size=1;Max Pool Size=1"))
{
connection.Open();
try
{
using(OracleCommand command = connection.CreateCommand())
{
command.CommandText = "select * from mytable";
using (IDataReader reader = command.ExecuteReader())
{
// omitted
}
}
}
finally
{
connection.Close();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
the Oracle Provider for .NET, v1.1. I am using the latest 9i client and
hitting against various 9i servers running on Win2K.
When using connection pooling, if database connectivity is lost temporarily
(for example, restarting the service), any pool connections that error
during the interruption will never recover - Subsequent calls on the
connection result in the error, "ORA-03114: not connected to ORACLE". This
problem does not occur is pooling is disabled. Additionally, the problem
"eventually" corrects itself if a "Connection Lifetime" is specified for the
connection.
The issue was originally discovered in a service-based application that
would poll the database periodically. When the database was shutdown for
maintenance, the service would still attempt to connect. We found that if
pooling was enable, the service would not reconnect after the database was
brought back online. The following short block of code, executed in a
single process (the scope of the connection pool), will reproduce the issue.
Execute the block and everything works properly. Stop the Oracle service
and it will fail, as expected (the execute fails, not the open). However,
if the Oracle service is restarted, executing the block again will also
fail - and continue to fail. If connection lifetimes are specified, the
pool will eventually correct itself as the underlying connections are
terminated and replaced. My best guess is that, when a connection is
returned to the pool, the "error state" is being maintained by either the
underlying .NET classes or the OCI.
We are currently using the connection lifetime as a work around, but feel
that there is an issue with the connection pool that needs to be addressed.
try
{
using(OracleConnection connection = new OracleConnection("Data
Source=DBA9i;Pooling=true;Enlist=false;User Id=myid;Password=mypwd;Min Pool
Size=1;Max Pool Size=1"))
{
connection.Open();
try
{
using(OracleCommand command = connection.CreateCommand())
{
command.CommandText = "select * from mytable";
using (IDataReader reader = command.ExecuteReader())
{
// omitted
}
}
}
finally
{
connection.Close();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
