On TransactionIsolation（3）Postgresql

postgresql的测试需要将connection方法改成以下

private static Connection connection() {
        try {
            Connection out=DriverManager.getConnection("jdbc:postgresql://172.23.47.6:5432/postgres","postgres","mima");
            out.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            return out;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

需要注意的是无论是哪个隔离级别，都是和mysql测试的READ_COMMITED的结果一致，这也意味着没有read_uncommited这个级别。

下面我们把代码改成如下情况

public class Main {
    private static final String[] ids={"valueA","valueB"};
    public static void main(String[] args) throws InterruptedException, SQLException {
        Lock lock=new ReentrantLock();
        initValue();
        lock.lock();
        AtomicBoolean finish=new AtomicBoolean();
        Callable<?> read=new Callable<Object>() {
            @Override
            public Object call() throws Exception {
                Connection connection = connection();
                while (!finish.get()) {
                    lock.lock();
                    printDB(connection);
                    var update=connection.prepareStatement("update transaction_table set curr_value=curr_value+10");
                    update.executeUpdate();
                    printDB(connection);
                    lock.unlock();
                    Thread.sleep(1000);
                }
                connection.close();
                return null;
            }
        };
        var pool=Executors.newCachedThreadPool();
        pool.submit(read);
        doWrite(lock);
        finish.set(true);
        pool.shutdown();
    }

    private static void doWrite(Lock lock) throws SQLException, InterruptedException {
        boolean lockd=true;
        Connection connection=connection();
        connection.setAutoCommit(false);
        var update="update transaction_table set curr_value=? where id=?";
        var prepareStatement=connection.prepareStatement(update);
        for(String id:ids) {
            for (int i = 1; i < 4; i++) {
                if(lockd){
                    lockd =false;
                }else {
                    lock.lock();
                }
                prepareStatement.setInt(1, i);
                prepareStatement.setString(2, id);
                prepareStatement.executeUpdate();
                System.out.println(update.replace("id=?","id="+id).replace('?',(char)(i+'0')));
                lock.unlock();
                Thread.sleep(1000);
            }
            connection.commit();
        }
        connection.close();
    }

    private static void printDB(Connection connection) throws SQLException {
        PreparedStatement select = connection.prepareStatement("select * from transaction_table");
        ResultSet resultSet = select.executeQuery();
        while (resultSet.next()) {
            System.out.println("\t" + resultSet.getString(1) + " | " + resultSet.getInt(2));
        }
    }

    private static void initValue() throws SQLException {
        Connection connection=connection();
        connection.setAutoCommit(false);
        connection.createStatement().execute("drop table if exists transaction_table");
        connection.createStatement().execute("create table transaction_table(id varchar(15),curr_value int )");
        var insert= connection.prepareStatement("insert into transaction_table values(?,0)");
        for(String id:ids) {
            insert.setString(1, id);
            insert.executeUpdate();
        }
        connection.commit();
    }

    private static Connection connection() {
        try {
            Class.forName(org.postgresql.Driver.class.getName());
            Connection out=DriverManager.getConnection("jdbc:postgresql://172.23.47.6:5432/postgres","postgres","mima");
            out.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            return out;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

结果如下

update transaction_table set curr_value=1 where id=valueA
	valueA | 0
	valueB | 0

看上去是停留在update transaction_table set curr_value=curr_value+10这一句了。如果把这一句改成update transaction_table set curr_value=curr_value+10 where id='valueB'，在隔离级别是TRANSACTION_SERIALIZABLE的情况下会报错

update transaction_table set curr_value=1 where id=valueA
	valueA | 0
	valueB | 0
	valueA | 0
	valueB | 10
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
  详细：Reason code: Canceled on identification as a pivot, during conflict out checking.
  建议：The transaction might succeed if retried.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
	at com.qbit.Main.doWrite(Main.java:57)
	at com.qbit.Main.main(Main.java:37)

如果这个时候查看数据库发现valueA的值是0，而valueB的值是10，说明先开始的transaction被放弃了，后提交的（注意call里面的connection是自动提交的）

在TRANSACTION_READ_COMMITTED这一隔离级别的情况下并不会报错，而是卡在下面这个地方

update transaction_table set curr_value=1 where id=valueA
	valueA | 0
	valueB | 0
	valueA | 0
	valueB | 10
update transaction_table set curr_value=2 where id=valueA
	valueA | 0
	valueB | 10
	valueA | 0
	valueB | 20
update transaction_table set curr_value=3 where id=valueA
	valueA | 0
	valueB | 20
	valueA | 0
	valueB | 30
update transaction_table set curr_value=1 where id=valueB
	valueA | 3
	valueB | 30

这说明两个session在各自行处理的时候是岁月静好，但是当二者冲突的时候就会卡住。如果将这一句改成update transaction_table set curr_value=curr_value+10 where curr_value=0则会更早的卡住。

update transaction_table set curr_value=1 where id=valueA
	valueA | 0
	valueB | 0

过一会main方法就执行完了，没有异常，也没有其他打印。