我最终成功地重现了序列化问题这个问题 https://stackoverflow.com/q/21706858/274677到 SSCCE（最短的独立完整示例）。我正在使用jdbc and java标签，尽管我相信这不是 Java 或 JDBC 特定的。
所以，我有两个表“主表”和“详细表”。 DETAIL TABLE 中的行挂在 MASTERTABLE 中的行下方：
CREATE TABLE public.MASTERTABLE(typename VARCHAR, i INTEGER);
ALTER TABLE public.MASTERTABLE ADD PRIMARY KEY (typename, i);
CREATE TABLE public.DETAILTABLE(typename VARCHAR, i INTEGER, j INTEGER);
ALTER TABLE public.DETAILTABLE ADD PRIMARY KEY (typename, i, j);
ALTER TABLE public.DETAILTABLE ADD CONSTRAINT detail_2_master FOREIGN KEY (typename, i) REFERENCES public.MASTERTABLE(typename, i);
然后我有代码（附加在最后），它在无限循环中执行以下操作：
- 在主表中插入给定的行
typename
- 在步骤 #1 中添加的主表行下的详细表中插入 100 行。
当我在两个单独的实例中启动代码时，同时运行，并使用不同的typename参数（假设一个正在使用type-a和另一个type-b）几分钟后我得到“could not serialize access due to read/write dependencies among transactions“消息（跟踪在末尾）。
我不明白的是为什么 PostgreSQL 对两个并发事务感到困惑，因为它们显然正在访问表的不同“切片”：
- 一个实例是使用 a 读取和写入行
typename的价值 ”type-a"
- 另一个实例是使用 a 读取和写入行
typename value "type-b"
此外，PostgreSQL 知道typename是主表和明细表主键的一部分，因此保证访问的行集是不相交的。
我在代码下方附加了导致错误的典型调用以及引发的跟踪。
code
如果将下面的代码修改为使用事务隔离级别Connection.TRANSACTION_REPEATABLE_READ代替Connection.TRANSACTION_SERIALIZABLE错误条件不会被触发。我没有包含在重新运行之前清空表的代码，因为我试图使其尽可能短。
import javax.sql.*;
import java.sql.*;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
public class FooMain {
public static void main(String args[]) throws Exception {
String typeName = args[0];
Integer transIsoLevel = Connection.TRANSACTION_SERIALIZABLE;
DataSource ds = getDataSource("jdbc:postgresql://localhost:5432/your-database", "your-user-name", "your-user-password");
Testing testingAPI = new Testing(ds, transIsoLevel);
for (int i = 0 ; i < 1000*1000 ; i++) {
System.out.printf("%s - %d\n", typeName, i);
testingAPI.addMasterRow(typeName, i);
for (int j = 0 ; j < 100 ; j++)
testingAPI.addDetailRow(typeName, i);
}
}
public static DataSource getDataSource(String dbURL, String user, String pwd) {
BasicDataSource dS = new BasicDataSource();
dS.setDriverClassName("org.postgresql.Driver");
dS.setUsername(user);
dS.setPassword(pwd);
dS.setUrl(dbURL);
dS.setMaxActive(1);
dS.setMaxIdle(1);
dS.setInitialSize(1);
dS.setValidationQuery("SELECT 1");
return dS;
}
}
class Testing {
private DataSource ds;
private int transactionLevel;
public Testing(DataSource ds, int transactionLevel) {
this.ds = ds;
this.transactionLevel = transactionLevel;
}
private Connection getConnection() throws SQLException {
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(transactionLevel);
return conn;
}
public void addMasterRow(String typeName, int i) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps1 = null;
ResultSet rs1 = null;
PreparedStatement ps2= null;
try {
conn.commit();
{ // this select statement does nothing, but I believe it is needed
// to trigger the error
String SQL="SELECT COUNT(*) FROM public.mastertable "+
"WHERE typename=? ";
ps1 = conn.prepareStatement(SQL);
ps1.setString (1, typeName);
rs1 = ps1.executeQuery();
}
{
String SQL="INSERT INTO public.mastertable(typename, i) "+
"VALUES(?, ?) ";
ps2 = conn.prepareStatement(SQL);
ps2.setString ( 1, typeName);
ps2.setInt ( 2, i);
ps2.executeUpdate();
}
conn.commit();
} finally {
DbUtils.closeQuietly( conn, ps1, rs1);
DbUtils.closeQuietly((Connection) null, ps2, (ResultSet) null);
}
}
public void addDetailRow(String typeName, int i) throws SQLException {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
conn.commit();
String SQL="INSERT INTO public.detailtable(typename, i, j) "+
"(SELECT ?, ?, COALESCE(MAX(j)+1,0) FROM public.detailtable "+
"WHERE typename=? AND i=?) ";
ps = conn.prepareStatement(SQL);
ps.setString ( 1, typeName);
ps.setInt ( 2, i);
ps.setString ( 3, typeName);
ps.setInt ( 4, i);
ps.executeUpdate();
conn.commit();
} finally {
DbUtils.closeQuietly(conn, ps, (ResultSet) null);
}
}
}
调用
只需同时调用两个实例，一个具有“type-a”命令行参数，另一个具有“type-b”命令行参数：
java -classpath ... FooMain type-a &
java -classpath ... FooMain type-b &
trace
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on conflict out to pivot 4720754, during read.
Hint: The transaction might succeed if retried.
Where: SQL statement "SELECT 1 FROM ONLY "public"."mastertable" x WHERE "typename"::pg_catalog.text OPERATOR(pg_catalog.=) $1::pg_catalog.text AND "i" OPERATOR(pg_catalog.=) $2 FOR SHARE OF x"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at Testing.addDetailRow(FooMain.java:98)
at FooMain.main(FooMain.java:18)
