java - 如何使用jdbc读取未提交的数据？

Question

我想测试 JDBC 事务的工作原理。特别是，我想查看未提交数据的读取。我已经使用本地安装的 PostgreSQL 数据库在 spring boot 环境中编写了一个集成测试。

我正在尝试向表中插入一行，从一个事务中读取它，然后在不提交它的情况下从另一个事务中更新它，然后再次读取它希望它会发生变化。

测试表(DDL):

create table users
(
    id          integer default nextval('user_id_sequence'::regclass) not null
        constraint users_pkey
            primary key,
    first_name  varchar(255)                                          not null,
    second_name varchar(255)                                          not null,
    email       varchar(255)
);

alter table users
    owner to postgres;

测试:

    public void testHealthCheck() throws SQLException {
        Connection zeroConnection = dataSource.getConnection();
        Integer insertedUserId = insertUserSilently(zeroConnection, new User()
                .setFirstName("John")
                .setSecondName("Doe")
                .setEmail("johndoe@gmail.com"));
        zeroConnection.close();

        Connection firstConnection = dataSource.getConnection();
        firstConnection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        firstConnection.setAutoCommit(false);

        Connection secondConnection = dataSource.getConnection();
        secondConnection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        secondConnection.setAutoCommit(false);

        List<User> users = getAllUsersSilently(firstConnection);
        log.info("Got users: {}", silentToJsonString(users));

        PersistenceUtils.updateUserEmailSilently(secondConnection, insertedUserId, "johndoe@yahoo.com");

        users = getAllUsersSilently(firstConnection);
        log.info("Got users: {}", silentToJsonString(users));

        secondConnection.rollback();
        secondConnection.close();

        users = getAllUsersSilently(firstConnection);
        log.info("Got users: {}", silentToJsonString(users));

        firstConnection.close();
    }

实用类:

    private static final String INSERT_USER_SQL = "insert into users(first_name, second_name, email) values (?, ?, ?)";
    private static final String UPDATE_USER_SQL = "update users set email = ? where id = ?;";
    private static final String SELECT_ALL_USERS_SQL = "select * from users";

    public static List<User> extractUsersSilently(ResultSet resultSet) {
        List<User> resultList = newArrayList();
        try {
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String firstName = resultSet.getString(2);
                String secondName = resultSet.getString(3);
                String email = resultSet.getString(4);
                resultList.add(new User(id, firstName, secondName, email));
            }
        } catch (SQLException e) {
            log.error("Error while extracting result set", e);
            return emptyList();
        }
        return resultList;
    }

    public static Integer insertUserSilently(Connection connection, User user) {
        try {
            PreparedStatement insertStatement = connection.prepareStatement(INSERT_USER_SQL, Statement.RETURN_GENERATED_KEYS);
            insertStatement.setString(1, user.getFirstName());
            insertStatement.setString(2, user.getSecondName());
            insertStatement.setString(3, user.getEmail());
            insertStatement.execute();
            ResultSet resultSet = insertStatement.getGeneratedKeys();
            resultSet.next();
            return resultSet.getInt(1);
        } catch (Exception exception) {
            log.error(format("Exception while inserting user %s", user), exception);
            return -1;
        }
    }

    public static List<User> getAllUsersSilently(Connection connection) {
        try {
            PreparedStatement selectStatement = connection.prepareStatement(SELECT_ALL_USERS_SQL);
            selectStatement.execute();
            return extractUsersSilently(selectStatement.getResultSet());
        } catch (Exception exception) {
            log.error("Exception while getting all users", exception);
            return Collections.emptyList();
        }
    }

    public static void updateUserEmailSilently(Connection connection, Integer userId, String userEmail) {
        try {
            PreparedStatement updateStatement = connection.prepareStatement(UPDATE_USER_SQL);
            updateStatement.setString(1, userEmail);
            updateStatement.setInt(2, userId);
            updateStatement.execute();
        } catch (Exception exception) {
            log.error(format("Exception while updating user %d", userId), exception);
        }
    }
}

实际结果是(测试前需要手动清表):

获得用户:

[{"id":55,"firstName":"John","secondName":"Doe","email":"johndoe@gmail.com"}]

获得用户:

[{"id":55,"firstName":"John","secondName":"Doe","email":"johndoe@gmail.com"}]

获得用户:

[{"id":55,"firstName":"John","secondName":"Doe","email":"johndoe@gmail.com"}]

虽然第二次阅读应该看到未提交的电子邮件更改。

Answer 1

无法读取 Postgres 中未提交的数据

参见 13.2. Transaction Isolation 部分PostgreSQL 文档:

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

这意味着如果您想测试 TRANSACTION_READ_UNCOMMITTED，您需要 PostgreSQL 以外的 DBMS。