Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    64

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

    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...
    If you ever back up Sybase, you want this backup-server plugin.

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    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.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    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...
    If you ever back up Sybase, you want this backup-server plugin.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •