Java Wish List #2 - Help with Deadlock Retry code

I'm sure most of you have hit the situation where you've got to implement some "Deadlock retry" code. For the uninitiated, the situation is as follows - try as you might you've tuned your Java code and SQL but every now and then, perhaps due to some internal issue or external program you don't control, you get database deadlocks and you don't want the program to bomb out.

(For tips on how to help avoid deadlocks look here)

Eventually the deadlock should go away if one of threads/processes backs off for a bit. Although this cannot be guaranteed to occur it does in practice. So you'll do something like wrap a try/catch block in a while loop to attempt the same SQL a number of times, sleeping between each try, before finally giving up.

But this solution has its own quandries . . . .

Problem number one - how to know it's a deadlock? Each database uses a different SQL error code to indicate a deadlock e.g. For Sybase it's 1205 for Oracle it's 60, for DB2 it's something else. So you've got to write some nasty little code to inspect each SQLException - effectively doing a quasi "instanceof" call.

But here's the big problem - I've got 50+ SQLExceptions throughout my data access layer in various DAOs etc.

I don't want to wrap all my try{} catch(SQLException) blocks with while loops and don't want to have that nasty little code inspecting error codes and replicate those snippets 50 times.
If you want to see how bad such code can get check here - and that's just a small SQL call!

Yeah I could probably handle it with some reflection - pass a method and arguments to some reflection code that invokes the passed in code, inspects exceptions thrown, does retrys etc. But that seems like such a hack!

This type of problem has that "cross-cutting" feel to it that perhaps an AOP solution might be a cinch. Perhaps Spring has a solution?

Either way I'd still love to have some way in JDBC to set a policy for deadlock retrys e.g.

• NONE- The default don't do anything as it is currently


• RETRY - Retry N number of times with M milliseconds between each successive retry


• TBD - Allow a programmer-specified way to handle it

This type of solution is similar in some ways to shutdown hooks where the JVM guarantees that some code is executed if your code experiences an abrupt, unexpected shutdown ( e.g. Ctrl-C,
SIGQUIT, SIGKILL signal etc.).

Yes I can imagine some way in which this feature could be abused, but I think it could be a real bonus to many developers. Any thoughts on this? Alternative solutions?