由于事务之间存在读/写依赖关系，java无法序列化访问

Question

最后，我设法将this SO question中的序列化问题复制到一个SSCCE（最短的自包含完整示例）。我正在使用jdbc和java标记，尽管我认为这不是Java或JDBC特有的

所以，我有两张表MASTERTABLE和DETAILTABLE。DETAILTABLE中的行挂在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);

然后我有一段代码（最后追加），它在一个无休止的循环中执行以下操作：

1. 在主表中插入具有给定typename的行
2. 在步骤#1中添加的主表行下的详细信息表中插入100行

当我在两个独立的实例中启动代码，同时运行时，使用不同的typename参数（比如一个使用type-a，另一个使用type-b），几分钟后，我得到了“could not serialize access due to read/write dependencies among transactions”消息（末尾的跟踪）

我不明白的是，为什么PostgreSQL对这两个并发事务感到困惑，因为它们显然在访问表的不同“切片”：

• 一个实例是读取和写入typename值为“type-a”的行
• 另一个实例是使用typename值“type-b”读取和写入行

此外，PostgreSQL知道typename是主表和明细表主键的一部分，因此访问的行集保证是不相交的

我在代码下面附加了导致错误的典型调用和抛出的跟踪

密码

如果将下面的代码修改为使用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 &

痕迹

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)