org.postgresql.util。PSQLException:错误:不能串行化访问，因为在事务中读取/写入依赖关系。

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

. .上面标有第217行。我在最后提供了实际的堆栈跟踪。

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

连接conn对象的事务隔离级别在getConnection()的实现中被设置为SERIALIZABLE:

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

这是什么样的错误，我应该如何去解决这个问题?我是否应该查看事务隔离级别、全表和特定于行的锁(如果在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)

1 个解决方案

#1

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.

每当您请求可序列化的隔离时，DB将尝试使并发的查询集看起来在它们产生的结果上连续执行。这并不总是可能的，例如，当两个交易有相互依赖时。在这种情况下，PostgreSQL将使用序列化失败错误中止其中的一个事务，并告诉您应该重试。

Code that uses SERIALIZABLE must always be prepared to re-try transactions. It must check the SQLSTATE and, 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 ... 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.

因为它不是这样的，它是一个插入…选择这个触动vo_business。阅读和写作的复位操作。这足以创建一个潜在的依赖关系，另一个事务执行相同的操作，或者以另一种方式读取和写入表。

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 ... select can conflict with its self. Open three psql sessions and run:

要知道我需要同时看到这两个交易，但这里有一个证明显示插入…选择可以与自我冲突。打开三个psql会话并运行:

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:

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

它与您的情况不一样，并不能证明您的语句可以相互冲突，但是它显示了插入…选择并不像你想的那样原子化。