Multithreading - Avoiding and dealing with database deadlocks

I am looking for a good strategy of dealing with database deadlocks from within a Java 6 application; several parallel threads could, potentially, write into the same table at the same time. The database (Ingres RDMBS) will randomly kill one of the sessions if it detects a deadlock.

What would be an acceptable technique to deal with the deadlock situation, given the following requirements?

• the total elapsed time should be kept as small as reasonably possible
• killing a session will incur a significant (measurable) rollback
• time threads have no way to

  communicate with each other i.e. the strategy should be autonomous

So far, the strategy I came up with is something along these lines:

short attempts = 0;
boolean success = false;
long delayMs = 0;

Random random = new R开发者_Python百科andom();
do {
    try {
        //insert loads of records in table 'x'
        success = true;
    } catch (ConcurrencyFailureException e) {
        attempts++;
        success = false;
        delayMs = 1000*attempts+random.nextInt(1000*attempts);

        try {
                Thread.sleep(delayMs);
            } catch (InterruptedException ie) {
        }
    }
} while (!success);

Can it be improved in any way? e.g. waiting for a fixed amount (magic number) of seconds. Is there a different strategy that will produce better results?

Note: Several database level techniques will be used to ensure deadlocks are, in practice, very rare. Also, the application will attempt to avoid scheduling threads that write into the same table at the same time. The situation above will be just a “worst case scenario”.

Note: The table in which records are inserted is organised as a heap partitioned table and has no indexes; each thread will insert records in it's own partition.

---

A commonly used approach is some form of exponential back-off. Rather than your 1000*attempts+random aproach, make the delay an exponential function of the number of attempts. This ensures minimal latency in the first one or two attempts, where it might have just been bad luck that you deadlocked, but gives you much bigger delays later, when it is clear that the connection really is congested.

Of course, another approach would be to try to arrange your database accesses so that deadlocks are less likely to occur. But without knowing what your queries do (and how, and when they're executed), it's impossible to say if that can be done

---

That's the way we did it. Loop and retry the transaction until it finishes.

We didn't mess with random delays.

Also, we did the commit inside the try block and the rollback in the exception handler.

When you have multiple lockable resources and multiple concurrent transactions, deadlock is unavoidable. It's a logical consequence of contention for locks.

If you avoid contention for locks (i.e., pessimistic table-level locking) then you also tend to prevent concurrency. If you can define transaction which don't contend for locks, you can avoid deadlock. Concurrent access to the same table, however, is pretty much the definition of deadlock.

When loading, inserts (especial in a HEAP table) can (often) proceed in parallel without many contention issues. If you delay building the indices, then there's no other updates going on during the insert.

So, you may be able to avoid by dropping the indexes, changing the organization to a heap, loading with multiple concurrent processes (or threads, it's usually faster to have multiple processes), then build your indices (and possibly reorganize the table), you may be able to avoid deadlocks.

When doing updates or deletes, not much helps.

---

If you don't need to have concurrent access to the database a simple solution might be to remove it and use a task processing queue to update the database instead, serialising access to the database via the queue. I realise this will introduce an asynchronous element to your application, and so would not be suitable for most user initiated applications or online web-apps, but might be worth considering for a batch/offline type application (I realise probably not the answer your looking for though).

---

With a database like Ingres you will always get some deadlocks, so you have to assume that any insert, update or delete will fail and have a retry strategy in place (as in your example). You should design your database so that contention is minimised and deadlocks only happen rarely. If you are continually getting transactions failing even after several retries, then this is a sign that you'll have to do some major database redesign (or move to a system like Oracle where it is usually possible to design applications to avoid deadlocks by suitable use of row-level locking).

---

how is this ?

short attempts = 0;
boolean success = false;
long delayMs = 0;

Random random = new Random();
do {
try {
     synchronized(ClassName.class) {
         //insert loads of records in table 'x'
      }

    success = true;
} catch (ConcurrencyFailureException e) {
    attempts++;
    success = false;
    delayMs = 1000*attempts+random.nextInt(1000*attempts);

    try {
                    Thread.sleep(delayMs);
            } catch (InterruptedException ie) {
    }
  }
} while (!success);