title: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
body: <p><strong>UPDATE</strong>: I managed in the end to reproduce this in a minimal setting which I posted as a <a href="https://stackoverflow.com/q/21759739/274677">separate question</a>.</p>
  
  <p>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:</p>
  
  <pre><code> org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
   [java] ERROR&gt;  Detail: Reason code: Canceled on identification as a pivot, during write.
   [java] ERROR&gt;  Hint: The transaction might succeed if retried.
  </code></pre>
  
  <p>The exception occurred when trying to execute the following statement:</p>
  
  <pre><code>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);
      }
  
  }
  </code></pre>
  
  <p>.. on line marked with <code>line-217</code> above. I provide the actual stack trace at the end.</p>
  
  <p>The transaction isolation level for the <code>Connection</code> <code>conn</code> object is set to <code>SERIALIZABLE</code> in the implementation of <code>getConnection()</code>:</p>
  
  <pre><code>protected Connection getConnection() throws SQLException {
      Connection conn = ds.getConnection();
      conn.setAutoCommit(false);
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
      return conn;
  }
  </code></pre>
  
  <p>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 <code>operation</code> 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.</p>
  
  <p>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</p>
  
  <pre><code> [java] ERROR&gt;org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
   [java] ERROR&gt;  Detail: Reason code: Canceled on identification as a pivot, during write.
   [java] ERROR&gt;  Hint: The transaction might succeed if retried.
   [java] ERROR&gt;  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
   [java] ERROR&gt;  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
   [java] ERROR&gt;  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
   [java] ERROR&gt;  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
   [java] ERROR&gt;  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
   [java] ERROR&gt;  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
   [java] ERROR&gt;  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
   [java] ERROR&gt;  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
   [java] ERROR&gt;  at _int.esa.esavo.dbbusiness.DBBusiness.logRepositoryOperationStart(DBBusiness.java:217)
   [java] ERROR&gt;  at _int.esa.esavo.harvesting.H.main(H.java:278)
  </code></pre>