Read Commited Isolation level not working

Question

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.

Answer 1

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