Hi all,
This is a summary of an IM discussion between myself and Del Simmons
(who has subsequently joined this list - welcome Del).
Del has a setup where if a database fails than the IP is transparently
switched to a new database. When this happens, all the existing
connections will be "bad".
If you have configured house-keeper-test-sql then they will be caught by
the next sweep of the house keeper, but that might not be for another 30
seconds or so and in the meantime you might refuse a lot of connections.
And if you are busy then you will have more active connections (and
active connections aren't tested, obviously).
If you configure fatal-sql-exception (you'll have to test on your
particular database what exception messages you are looking for) then
each connection will refuse exactly one client before destroying itself.
It would be nice to continue the client's execution with a new
connection - transparently. Only that is a bit tricky. Consider this:
Connection connection = DriverManager.getConnection("proxool.example:org.hsqldb.jdbcDriver:jdbc:hsqldb:test");
// Some preparation
connection.setAutoCommit(false);
Statement s = connection.prepareStatement("SELECT * FROM MY_TABLE;");
// Execute it (this is the bit that causes the SQLException because
// the database is down.
s.execute();
The problem is that Proxool won't know there's a problem until it tries
to execute the statement. If, at that point, it decides to try again
with a brand new connection then it will have to recreate the
preparation phase, and that might be a lot more complicated than this
example.
At the app level you might consider putting the whole of this code into
a short loop (if you're that interested in avoiding a refusal). But
making it transparent within Proxool is a little more involved.
A compromise might be that if we ever get a fatal sql exception then we
immediately wake up the house keeper thread so that we minimise the
refusals. (Note - I have just implemented this in version 0.6.4)
Another might be that we test each connection when we serve it to see if
it is ok. This might be some SQL (as defined in house-keeper-test-sql),.
but that will add an unacceptable performance overhead. Are there any
quick operations we can call on a Connection to check to see if it is
really connected to an operating database?
(Note, this message is being resent - it never arrived last time for
some reason. Apologies if it becomes a duplicate.)
Regards,
--
Bill Horsman
Proxool
http://proxool.sourceforge.net
ICQ: 119577180
|
