Thread: Connection exceptions
Hi
Anybody got any best practices for how do I deal with connection problems
in JDBC?
I am working on a storage tier where the DAOs can encounter faulty
database connections, either closed or other-wise non functional
connections.
The database connection is pooled with Commons DBCP, and my initial
thought was to reinitialise or restart the pool, if there were any
problems.
But then I realised that the pool can test connections before giving it to
the application, so now moving into muddy waters because I dont know much
about these details.
My original code is something like:
while (retry > 0) {
Connection con = ds.getConnection
if (con==null) {
dsFactory.restartDS();
retry--:
continue;
}
try {
dao.execute();
} catch (SQLExceptions e) {
con.close();
}
Not knowing the details of how the DBCP or the DataSource works in these
situations, make this code prone to problems.
I will continue to investigate, but any feedback about what to look at is
appreciated.
regards
thomas
(Damn web mail, sent it before I was finished)
Hi
Anybody got any best practices for how to deal with connection problems
in JDBC with DBCP?
I am working on a storage tier where the DAOs can encounter faulty
database connections, either closed or other-wise non functional
connections. The database connection is pooled with Commons DBCP and my
initial thought was to reinitialise or restart the pool, if there were any
problems.
But then I realised that the pool can test connections before giving it to
the application, so now I am moving into muddy waters, because I dont know
much about these details.
My original code is something like:
while (retry > 0) {
Connection con = ds.getConnection
if (con==null) {
dsFactory.restartDS();
retry--:
continue;
}
try {
dao1.execute();
dao2.execute();
} catch (SQLExceptions e) {
retry--:
continue;
if (retry == 0) {
throw new FaultRuntimeException("Can not complete operation", e)
}
con.close();
}
Not knowing the details of how the DBCP, DataSource or JDBC works in these
situations, this code is prone to problems.
I will continue to investigate, but any feedback about what to look at is
appreciated.
regards
thomas
>
> Hi
>
> Anybody got any best practices for how do I deal with connection problems
> in JDBC?
>
> I am working on a storage tier where the DAOs can encounter faulty
> database connections, either closed or other-wise non functional
> connections.
> The database connection is pooled with Commons DBCP, and my initial
> thought was to reinitialise or restart the pool, if there were any
> problems.
> But then I realised that the pool can test connections before giving it to
> the application, so now moving into muddy waters because I dont know much
> about these details.
>
> My original code is something like:
>
> while (retry > 0) {
> Connection con = ds.getConnection
> if (con==null) {
> dsFactory.restartDS();
> retry--:
> continue;
> }
> try {
> dao.execute();
> } catch (SQLExceptions e) {
>
> con.close();
> }
>
> Not knowing the details of how the DBCP or the DataSource works in these
> situations, make this code prone to problems.
>
> I will continue to investigate, but any feedback about what to look at is
> appreciated.
>
> regards
>
> thomas
>
On Fri, 2009-06-19 at 12:37 +0200, Thomas Finneid wrote: > Anybody got any best practices for how do I deal with connection problems > in JDBC? Most of your question really deals with the connection pooler you're using, rather than JDBC its self. I'd start with the documentation for DBCP. That said, there are a few areas where you do need to handle connection issues. The big one is that when you perform an operation, or a series of operations wrapped into a transaction, they shouldn't change any state in the wider program until they're complete, so you can retry them if something goes wrong. You should be prepared to catch an SQLException thrown at any point in the operation or series of operations. You shouldn't care at what point in the process the exception was thrown. When you catch it, you can test the SQLState to see if the error is possibly transient and be prepared to retry the operation. (deadlocked transaction? OK, retry. Connection broken? Obtain a new connection and retry. etc) That way, you can just go ahead and use the connection you got from the pooler. If it doesn't work for any reason, your code will deal with it. After all, just because the connection was fine when the pooler tested it doesn't mean it's still going to be fine by the time you start using it; that's a race you'll win most of the time but are far from guaranteed to win. Much better to make the code actually doing the work handle failures cleanly rather than try to prevent them. Think, for example, of a user with a laptop wandering around - they might leave wifi range half way through an operation. -- Craig Ringer
Craig Ringer wrote: > Most of your question really deals with the connection pooler you're > using, rather than JDBC its self. I'd start with the documentation for > DBCP. I have looked at it, and its really poor, but I'll look for a mailing list to see if I can get answers there. Not sure if anybody here can answer this question, but one thing I dont understand is the separation between DBCP and the pooler and who do what. Apparently they are two different apache-commons packages. Anybody know anything about these details or where I can find any documentation or articles about it? > shouldn't care at what point in the process the exception was thrown. > When you catch it, you can test the SQLState to see if the error is > possibly transient and be prepared to retry the operation. (deadlocked > transaction? OK, retry. Connection broken? Obtain a new connection and > retry. etc) My main algorithm is to not care, but as soon as a problem occurs, return the connection to the pool and let the pool deal with its condition, then get a new connection and try again. Repeat three times and then fail by throwing an exception to the client (after appropriate cleanup and logging.) regards thomas