Automatic re-run of queries in java.sql.*

teterin · #1 (**permalink**) 11-20-07, 15:57

Hello!

Sometimes our queries fall victims to a deadlock, which causes the programs to be killed by an exception. The error message reads as something like:

Code:

Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with
 another process and has been chosen as the deadlock victim. Rerun the transaction.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258)

I can't be the first one to think of automatically rerunning the query for a certain number of times, if the error-message from the server says "Rerun the transaction." Is this possible within the existing java.sql.* framework? I don't want to replicate somebody else's effort by devising my own wrapper on top of java.sql.Statement...

Thanks for any hints...

amthomas · #2 (**permalink**) 11-20-07, 16:13

As far as I have seen there is no way to rerun a previous transaction. You can just catch it and try again

Usually if you are doing this it is a symptom of other problems that need to be looked at though. My uninformed and inexperienced guess is that the problem will grow worse and more users/threads are using the system.

Can you find out what it is deadlocked on? the transactions that are running into each other?

If it is tables being manually locked with explicit lock statements or select for updates then you can probably rearrange the table lock order so that they are the same and avoid the deadlock. (I usualy do table locks in alphabetical order if I have to do it manually hehe)

also (and I am really speculating here..) it looks like you are doing .getMoreResults which I will assume gets X results out of N. If this is leaving a cursor open on a locked table in the database then it might not truely be a deadlock. It is just one query take up to much time getting results slowly and the other query gets tired of waiting. You could probably improve that by getting what you need out immediately and doing incremental queries. This is wild speculation! haha.

teterin · #3 (**permalink**) 11-20-07, 17:32

The problems we are facing are caused by other software updating the same tables we are selecting from. Re-running the program always succeeds, but currently requires a manual intervention. I'd like to change the code to rerun automatically, if the failure-message suggests doing so...

The right way to do this, it seems, is by modifying the Statement-implementation... I can do it myself, but I was hoping, someone may already have done it...

Internet Services

Web Development

Digital Marketing

Consumer Tech