PreparedStatement Batch Insert/Update - DeadLock Issue

Question

I'm working on a system that supports multiple databases. All the insert and updates happen in bulk and the same is achieved with the help of PreparedStatement batches. However, with PostgreSQL, there are quite a few times where it is causing a deadlock over updating in batch. I would like to know if there is a way to avoid this.

ERROR: deadlock detected
Detail: Process 30655 waits for ExclusiveLock on relation 295507848 of database 17148; blocked by process 30662.

I have retry logic in place but it gives:

ERROR: current transaction is aborted, commands ignored until end of transaction block

I'm a bit confused about how to handle this situation.

    public void InsertUpdateBatch(String auditPrefix, String _tableName, TableStructure<?> ts, 
    StringBuilder sb, String operation) throws Exception {
    
    boolean retry = true;
    boolean isInsert = "insert".equalsIgnoreCase(operation) ? true : false;
    int minTry = 0;
    int maxTries = 2;

    ThreadLocal<PreparedStatement> statement = isInsert ? pstmt : updateStmt;
    ThreadLocal<List<Object[]>> dataToProcess = isInsert ? insertBatchData : updateBatchData;
    
    while (retry) {
        try {
            long t1 = System.currentTimeMillis();
            int[] retCount = {};
            
            retCount = statement.get().executeBatch();
            
            // Clearing the batch and batch data
            statement.get().clearBatch();
            dataToProcess.get().clear();
            
            if(isInsert) {
                syncReport.addInsert(ts.getTableName(), retCount.length);
            } else {
                syncReport.addUpdate(ts.getTableName(), retCount.length);
            }
            
            this.syncReport.addDatabaseTime(t1, System.currentTimeMillis());

            retry = false;
            
        } catch (Exception e) {
            // Clearing the batch explicitly
            statement.get().clearBatch();
            
            log.log(Level.INFO, "Thread " + Thread.currentThread().getName() + ": tried the operation " + operation + " for "  + (minTry + 1) + " time(s)");

            if (++minTry == maxTries) {
                retry = false;
                minTry = 0;
                e.printStackTrace();
                commitSynchException(auditPrefix, _tableName, ts, sb, operation, isInsert, e);
            } else {
                
                trackRecordCount(e, ts, !isInsert);
                // Rebuild Batch
                rebuildBatch(ts, dataToProcess.get(), e);
                // Clearing old batch data after rebuilding the batch
                dataToProcess.get().clear();
            }
            
        }
    }
    
}

Answer 1

Retry is the solution. But you haven't properly implemented it.

-- EDIT as suggested by @Mark Rotteveel --

You need to explicitly call .abort() on your connection and then you can retry. You can probably get away with keeping your PreparedStatement / Statement objects, but if you still run into trouble consider closing and recreating these.

-- END EDIT ---

Your second problem is lack of nagled exponential backoff.

Computers are reliable. Very reliable. Better than swiss watches.

If two threads do a job, and as part of that job they deadlock each other, and they both will see this, abort their transactions, and start over, then...

probably the exact same thing will happen again. And again. And again. And again. Computers can be that reliable in unlucky scenarios.

The solution is randomized exponential backoff. One way to ensure that the two threads don't keep doing things the same way in the exact same order with the exact same timing is to literally start flipping coins to forcibly make it less stable. This sounds stupid, but without this concept the internet wouldn't exist (Ethernet works precisely like this: All systems on an ethernet network send data immediately and then check for spikes on the line that indicates multiple parties all sent at the same time, and the result was an unreadable mess. If they detect this, they wait randomly with exponential backoff and then send it again. This seemingly insane solution beat the pants off of token ring networks).

The 'exponential' part means: As retries roll in, make the delays longer (and still random).

Your final error is that you always retry, instead of only when that's sensible to do.

Here's an example exponential randomized backoff that fixes all your problems except the part where you need to make your (Prepared)Statement objects anew and close the old ones; your snippet does not make clear where that happens.

} (catch SQLException e) { // catch SQLEx, not Ex
    String ps = e.getSQLState();
    if (ps != null && ps.length() == 5 && ps.startsWith("40")) {
        // For postgres, this means retry. It's DB specific!
        retryCount++;
        if (retryCount > 50) throw e;
        try {
            Thread.sleep((retryCount * 2) + rnd.nextInt(8 * retryCount);
            continue; // continue the retry loop.
        } catch (InterruptedException e2) {
            // Interrupted; stop retrying and just throw the exception.
            throw e;
        }
     }
     // it wasn't retry; just throw it.
     throw e;
}

Or, do yourself a huge favour, ditch all this work and use a library. JDBC is designed to be incredibly annoying, inconsistent, and ugly for 'end users' - that's because the target audience for JDBC is not you. It's the DB vendors. It's the lowest level glue imaginable, with all sorts of weird hacks so that all DB vendors can expose their pet features.

Those using JDBC to access DBs are supposed to use an abstraction library built on top!

For example, JDBI is great, and supports retry very well, with lambdas.