更新：我最终设法在最小的设置中重现这个,我发布了
separate question.
从同一个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)
每当您请求SERIALIZABLE隔离时,DB将尝试使并发查询集看起来按照它们生成的结果进行串行执行.这并非总是可行的,例如当两个交易具有相互依赖性时.在这种情况下,Postgresql将中止其中一个带有序列化失败错误的事务,告诉您应该重试它.
使用SERIALIZABLE的代码必须始终准备好重新尝试事务.它必须检查sqlSTATE,并且对于序列化失败,重复该事务.
见the transaction isolation documentation.
在这种情况下,我认为你的主要误解可能是：
this is a single atomic insert
因为它不是那种,它是一个INSERT … SELECT,它接触vo_business.repositoryoperation进行读写.这足以与另一个执行相同操作的事务创建潜在依赖关系,或者以另一种方式读取和写入表.
另外,出于效率原因,可序列化隔离代码在某些情况下可以去生成保持块级依赖性信息.因此,它可能不一定是触及相同行的事务,只是相同的存储块,尤其是在负载下.
如果不确定它是否安全,Postgresql更愿意中止可序列化的事务.证明系统有局限性.所以你也有可能找到一个愚弄它的案例.
要确定我需要并排查看这两个事务,但这里有一个证明插入的证明… select可以与它自己发生冲突.打开三个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,2
WHERE NOT EXISTS (SELECT 1 FROM serialdemo WHERE x = 1);
session0: ROLLBACK;
session1: COMMIT;
session2: COMMIT;
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.
它与您的情况不同,序列化失败并不能证明您的语句可能相互冲突,但它表明插入… select不像您想象的那样原子.