RE: [Firebird-net-provider] How to reset transaction after connection drop?

[Tedd <te...@al...>]

I have tracked down why I was having difficulty using FbConnections after
the database is shutdown and restarted.  The problem results from the way I
am using FbConnection objects.  When using connection pooling, when
FbConnection.Open()is called, a connection is retrieved from the unlocked
pool.  There currently is no method in place to check the validity of this
connection.  In the event Firebird restarts, the connection looks valid and
its state is set to Open.  However, any operation on this connection, such
as BeginTransaction() throws an exception.  Since I always Close() the
connection object after use, FbConnectionPool.CheckIn() modifies the created
ticks to 'now'.

This means that the connection is never 'Expired', so the underlying 'old'
FbIscConnection is never closed.  Any attempt to create a new connection
pulls the old invalid FbIscConnection from the unlocked pool.

Solutions:  First, do not 'Close()' an FbConnection where a 335544721
isc_network_error exception was thrown, even if its state is 'Open'.  This
prevents the connection from being put back into the unlocked pool.
However, I am just not comfortable leaving an 'Open' connection hang around
like that.  It is still in the locked pool so it will never be GC'd

Second, FbConnectionPool.CheckOut() should verify that a connection pulled
from the unlocked pool is, indeed, valid.
FbConnection.Open()|FbConnectionPool.CheckOut() should never return an
invalid connection, especially if when looks valid.  I have modified
FbConnectionPool and FbIscConnection to handle this.  I added a function to
FbIscConnection that verifies the validity of the connection by calling
gds.isc_database_info().  No database info is actually retrieved.  I am only
interested in showing that I can communicate with the database.  The
function returns true or false depending on the validity.  The modification
to FbConnectionPool is to call the FbIscConnection verification function
after pulling a connection from the unlocked pool.  If the connection is not
valid, it is expired.

There are some things I'd like change about the mods but it might mean more
changes to FbIscConnection that I'm comfortable making at this point.
Mostly, I think that the verification function should be static.

If anybody is interested, let me know how to send the modifications.

Lastly, it would be nice if we could set the maximum lifetime of a
connection.  FbConnectionPool would not return a FbIscConnection older than
the maximum lifetime.  I'm not sure how this coincides with Microsoft's
existing database classes.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.423 / Virus Database: 238 - Release Date: 11/25/2002