org.postgresql.util.PSQLException：错误：由于事务之间的读/写依赖关系，无法序列化访问

org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions

提问by Marcus Junius Brutus

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:

从在同一个 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.

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-217above. I provide the actual stack trace at the end.

.. 在线标记为line-217上面。我在最后提供了实际的堆栈跟踪。

The transaction isolation level for the Connectionconnobject is set to SERIALIZABLEin the implementation of getConnection():

Connectionconn对象的事务隔离级别SERIALIZABLE在以下实现中设置为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 operationfield 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.

很可能另一个应用程序也在同时尝试在同一张表上写入，尽管它确实提供了不同的operation字段，所以我不知道如何发生任何混淆。此外，这是一个单一的原子插入，所以我看不到访问序列化是如何发挥作用的。

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

这是什么类型的错误，我应该如何尝试解决此问题？我是否应该查看事务隔离级别、全表与行特定锁（如果 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)

回答by Craig Ringer

Whenever you request SERIALIZABLEisolation the DB will attempt to make concurrent sets of queries appear to have executed seriallyin 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 transactionswith a serialization failure error, telling you that you should retry it.

每当您请求SERIALIZABLE隔离时，数据库都会尝试使并发查询集根据它们产生的结果显示为串行执行。这并不总是可能的，例如当两个事务具有相互依赖关系时。在这种情况下，PostgreSQL 将中止具有序列化失败错误的事务之一，告诉您应该重试它。

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

使用的代码SERIALIZABLE必须始终准备好重试事务。它必须检查SQLSTATE序列化失败并重复事务。

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 ... SELECTthat touches vo_business.repositoryoperationfor 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.

因为它是这样的事，这是一个INSERT ... SELECT触及vo_business.repositoryoperation的读取和写入。这足以与另一个执行相同操作的事务或以另一种方式读取和写入表的事务建立潜在的依赖关系。

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.

如果 PostgreSQL 不确定它是否安全，它会更愿意中止可序列化的事务。证明系统有局限性。所以也有可能你刚刚发现了一个愚弄它的案例。

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

要确定我需要并排查看两个交易，但这里有一个证明insert ... select可以与自身发生冲突。打开三个psql会话并运行：

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

session0: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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:

session1 会提交很好。session2 将失败：

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 yourstatements can conflict with each other, but it shows that an insert ... selectisn't as atomic as you thought.

这与您的情况不同，序列化失败，并不能证明您的语句可以相互冲突，但它表明 aninsert ... select并不像您想象的那样原子。