org.postgresql.util.PSQLException：错误：由于事务之间的读/写

从同一个PostgreSQL实例和表并行运行的两个不同应用程序进行JDBC插入时,我遇到了以下异常：

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.

尝试执行以下语句时发生异常：

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);
    }

}

..在线上标有217线.我在最后提供了实际的堆栈跟踪.

在conConnection()的实现中,Connection conn对象的事务隔离级别设置为SERIALIZABLE：

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

很可能另一个应用程序也试图同时在同一个表上写,虽然它确实提供了一个不同的操作字段,所以我看不出有任何混淆可能发生.而且,这是一个单一的原子插入,所以我看不到访问序列化是如何发挥作用的.

这是什么样的错误,我应该如何解决此问题？我应该查看事务隔离级别,整个表与行特定的锁(如果PostgreSQL中有这样的概念),等等？我应该重试(提示说“如果重试,交易可能会成功.”).我将尝试在SSCCE中重现它,但我只是发布它,以防它有明显的原因/解决方案

[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)