org.postgresql.util.PSQLException: ERROR: could no

UPDATE: I managed in the end to reproduce this in a minimal setting which I posted as a separate question.

I 've encountered the following exception when doing JDBC inserts from two different applications running side-by-side on the same PostgreSQL instance and tables:

 org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
 [java] ERROR>  Detail: Reason code: Canceled on identification as a pivot, during write.
 [java] ERROR>  Hint: The transaction might succeed if retried.

The exception occurred when trying to execute the following statement:

public int logRepositoryOperationStart(String repoIvoid, MetadataPrefix prefix, RepositoryOperation operation, int pid, String command, String from_XMLGregCal) throws SQLException {
    Connection        conn = null;
    PreparedStatement ps   = null;
    try {
        conn = getConnection();
        conn.commit();
        String SQL = "INSERT INTO vo_business.repositoryoperation(ivoid, metadataprefix, operation, i, pid, command, from_xmlgregcal, start_sse)  "+
                     "(SELECT ?, ?, ?, COALESCE(MAX(i)+1,0), ?, ?, ?, ? FROM vo_business.repositoryoperation                                      "+
                     "WHERE ivoid=? AND metadataprefix=? AND operation=?)                                                                         ";
        ps = conn.prepareStatement(SQL);
        ps.setString(1, repoIvoid);
        ps.setString(2, prefix.value());
        ps.setString(3, operation.value());
        ps.setInt   (4, pid);
        ps.setString(5, command);
        ps.setString(6, from_XMLGregCal);
        ps.setInt   (7, Util.castToIntWithChecks(TimeUnit.SECONDS.convert(System.currentTimeMillis(), TimeUnit.MILLISECONDS)));
        ps.setString(8, repoIvoid);
        ps.setString(9, prefix.value());
        ps.setString(10, operation.value());
        if (ps.executeUpdate() != 1) { // line 217
            conn.rollback();
            throw new RuntimeException();
        }
        conn.commit();
        return getMaxI(conn, repoIvoid, prefix, operation);
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        DbUtils.closeQuietly(conn, ps, (ResultSet) null);
    }

}

.. on line marked with line-217 above. I provide the actual stack trace at the end.

The transaction isolation level for the Connection conn object is set to SERIALIZABLE in the implementation of getConnection():

protected Connection getConnection() throws SQLException {
    Connection conn = ds.getConnection();
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    return conn;
}

It is likely that another application was also trying to write on the same table at the same time, though it certainly provided a different operation field so I don't see how any mixup could have occurred. Moreover, this is a single atomic insert so I don't see how access serialization comes into play.

What kind of error is this and how should I go about in trying to troubleshoot this? Should I be looking at transaction isolation levels, whole-table vs. row-specific locks (if there is such a concept in PostgreSQL), etc.? Should I just retry (the hint says that "The transaction might succeed if retried."). I'll try to reproduce it in a SSCCE but I 'm just posting this in case it has an obvious cause / solution

 [java] ERROR>org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
 [java] ERROR>  Detail: Reason code: Canceled on identification as a pivot, during write.
 [java] ERROR>  Hint: The transaction might succeed if retried.
 [java] ERROR>  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
 [java] ERROR>  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
 [java] ERROR>  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
 [java] ERROR>  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
 [java] ERROR>  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
 [java] ERROR>  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
 [java] ERROR>  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
 [java] ERROR>  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
 [java] ERROR>  at _int.esa.esavo.dbbusiness.DBBusiness.logRepositoryOperationStart(DBBusiness.java:217)
 [java] ERROR>  at _int.esa.esavo.harvesting.H.main(H.java:278)

Whenever you request SERIALIZABLE isolation the DB will attempt to make concurrent sets of queries appear to have executed serially in terms of the results they produce. This is not always possible, e.g. when two transactions have mutual depenencies. In this case, PostgreSQL will abort one of the transactions with a serialization failure error, telling you that you should retry it.

Code that uses SERIALIZABLE must always be prepared to re-try transactions. It must check the SQLSTATE and, for serialization failures, repeat the transaction.

See the transaction isolation documentation.

In this case, I think your main misapprehension may be that:

this is a single atomic insert

as it is nothing of the sort, it's an INSERT ... SELECT that touches vo_business.repositoryoperation for both reading and writing. That's quite enough to create a potential dependency with another transaction that does the same, or one that reads and writes to the table in another way.

Additionally, the serializable isolation code can under some circumstances de-generate into holding block-level dependency information for efficiency reasons. So it might not necessarily be a transaction touching the same rows, just the same storage block, especially under load.

PostgreSQL will prefer to abort a serializable transaction if it isn't sure it's safe. The proof system has limitations. So it's also possible you've just found a case that fools it.

To know for sure I'd need to see both transactions side by side, but here's a proof showing an insert ... select can conflict with its self. Open three psql sessions and run:

session0: CREATE TABLE serialdemo(x integer, y integer);

session0: LOCK TABLE serialdemo IN ACCESS EXCLUSIVE MODE;

session1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

session2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

session1: INSERT INTO serialdemo (x, y)
          SELECT 1, 2
          WHERE NOT EXISTS (SELECT 1 FROM serialdemo WHERE x = 1);

session2: INSERT INTO serialdemo (x, y)
          SELECT 1, 2
          WHERE NOT EXISTS (SELECT 1 FROM serialdemo WHERE x = 1);

session0: ROLLBACK;

session1: COMMIT;

session2: COMMIT;

session1 will commit fine. session2 will fail with:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

It's not the same serialization failure as your case, and doesn't prove that your statements can conflict with each other, but it shows that an insert ... select isn't as atomic as you thought.