读取已提交的隔离级别不起作用。

huangapple go评论75阅读模式
英文:

Read Commited Isolation level not working

问题

我正在尝试理解ACID隔离原则。我已编写了以下代码部分:

ExecutorService executorService = Executors.newSingleThreadExecutor();

Runnable t2 = () -> {
    System.out.println("Thread: " + Thread.currentThread().getName());
    Connection connection;

    try {
        connection = this.iDatabaseConnector.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM mssmbank.mssmbank.bankaccounts WHERE id = ?");
        preparedStatement.setInt(1, 490);

        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();

        System.out.println("Result: " + resultSet.getDouble("BALANCE"));

        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
};

Runnable t1 = () -> {
    System.out.println("Thread: " + Thread.currentThread().getName());
    Connection connection;

    try {
        connection = this.iDatabaseConnector.getConnection();
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        connection.setAutoCommit(false);

        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE mssmbank.mssmbank.bankaccounts " + "SET balance = ? WHERE id = ?");
        preparedStatement.setDouble(1, (new Random()).nextInt(1000));
        preparedStatement.setInt(2, 490);
        preparedStatement.executeUpdate();

        ExecutorService executorService1 = Executors.newSingleThreadExecutor();
        executorService1.execute(t2);

        Thread.sleep(3_000);

        connection.commit();
        connection.close();

        executorService1.shutdown();
        executorService1.awaitTermination(20, TimeUnit.SECONDS);
    } catch (SQLException | InterruptedException e) {
        e.printStackTrace();
    }
};

executorService.execute(t1);
executorService.shutdown();
executorService.awaitTermination(20, TimeUnit.SECONDS);

有两个线程,t1t2t1 应该更新数据库中的银行账户行,而 t2 应该读取其余额。

下面是情景描述:t1 启动,打开连接,进行更新但不提交。它触发 t2休眠3秒。

t2 启动,打开连接,读取余额并打印出来(基本上是旧的余额,因为 t1 尚未提交),然后关闭连接。

然后 t1 通过提交更新完成,程序结束。

我期望发生的是,t2 被阻塞,直到 t1 完成提交,因为 t2 正在访问相同的银行账户行(t1 应该已锁定该行以进行更新)。因此,t2 应该打印银行账户 #490 的更新余额,而不是之前的余额。

为什么 t1 没有锁定该行?为什么 t2 没有被阻塞?
注意,隔离级别(isolation Level)设置为 TRANSACTION_READ_COMMITTED

英文:

I'm trying to understand the ACID isolation principal. I have coded this portion of code:

ExecutorService executorService = Executors.newSingleThreadExecutor();

        Runnable t2 = () -> {
            System.out.println("Thread: " + Thread.currentThread().getName());
            Connection connection;

            try {
                connection = this.iDatabaseConnector.getConnection();

                PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM mssmbank.mssmbank.bankaccounts WHERE id = ?");
                preparedStatement.setInt(1, 490);

                ResultSet resultSet = preparedStatement.executeQuery();
                resultSet.next();

                System.out.println("Result: " + resultSet.getDouble("BALANCE"));

                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        };

        Runnable t1 = () -> {
            System.out.println("Thread: " + Thread.currentThread().getName());
            Connection connection;

            try {
                connection = this.iDatabaseConnector.getConnection();
                connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                connection.setAutoCommit(false);

                PreparedStatement preparedStatement = connection.prepareStatement("UPDATE mssmbank.mssmbank.bankaccounts " + "SET balance = ? WHERE id = ?");
                preparedStatement.setDouble(1, (new Random()).nextInt(1000));
                preparedStatement.setInt(2, 490);
                preparedStatement.executeUpdate();

                ExecutorService executorService1 = Executors.newSingleThreadExecutor();
                executorService1.execute(t2);

                Thread.sleep(3_000);

                connection.commit();
                connection.close();

                executorService1.shutdown();
                executorService1.awaitTermination(20, TimeUnit.SECONDS);
            } catch (SQLException | InterruptedException e) {
                e.printStackTrace();
            }
        };

        executorService.execute(t1);
        executorService.shutdown();
        executorService.awaitTermination(20, TimeUnit.SECONDS);

There are 2 threads, t1 and t2. t1 is supposed to update a bank account row in the database, and t2 is supposed to read its balance.

Here's the scenario: t1 start, open a connection, makes an update and doesn't commit. It triggers t2 and sleeps for 3 seconds.

t2 starts, opens a connection, reads the balance and prints it (basically the old one because t1 hasn't committed yet) then close the connection.

Then t1 finishes by committing the update and the program ends.

What I'm expecting to happen, is that t2 gets blocked until t1 has finished committing because t2 is accessing the same bank account row (t1 should have locked the row for update). Therefore, t2 should have printed the updated balance of the bank account #490, not the previous one.

Why doesn't t1 lock the row? why isn't t2 getting blocked?
Note that the isolation Level is set to TRANSACTION_READ_COMMITTED.

答案1

得分: 1

在t2中的选择语句没有被阻塞,因为它只尝试读取而不是写入。如果您尝试执行更新(或select...for update),那么如果超时设置为在放弃之前等待一段时间,它可能会被阻塞。您没有提到您正在使用哪个数据库,但是这里有一些关于Postgres如何处理锁定的详细文档:
https://www.postgresql.org/docs/current/explicit-locking.html

英文:

The select statement in t2 is not getting blocked because it is only attempting to read not write. If you were to attempt to perform an update (or select...for update), then it could block if your timeout is set to wait for some length of time before giving up. You don't mention which database you're using, but here's some nice docs on how Postgres handles locking:
https://www.postgresql.org/docs/current/explicit-locking.html

huangapple
  • 本文由 发表于 2020年5月4日 00:52:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/61578285.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定