In MVCC mode changes committed in one transaction are not available in parallel transaction

[ismagilov]

I try to implement transfer between two accounts, so that it works correctly in concurrent transactions. But for some reason, these two transactions a) run sequentially b) the second transaction doesn't see changes made by the first one. As a result, balances are not updated correctly.

Is it expected behaviour?...

H2 database settings

• version 1.4.196
• MVCC engine is turned on by default
• Transactions isolation levels are ignored
• Multithreading mode is turned on: MULTI_THREADED=1

Test case

• Initial balances:
  1. acc1 = 300
  2. acc2 = 400
• Make two parallel transfers (the second one is delayed for 200ms):
  1. 50 from acc1 to acc2
  2. 200 from acc2 to acc1
• Expected final balance:
  1. acc1 = 450 (minus 50, plus 200)
  2. acc2 = 250 (plus 50, minus 200)
• Actual final balances:
  1. acc1 = 500
  2. acc2 = 200

SQL Schema

CREATE TABLE ACCOUNT (
  ID BIGINT PRIMARY KEY AUTO_INCREMENT,
  NUMBER VARCHAR(25) NOT NULL,
  BALANCE DECIMAL(19,2) DEFAULT 0 CHECK (BALANCE >= 0)
);

INSERT INTO ACCOUNT(NUMBER, BALANCE) VALUES('acc1', 300);
INSERT INTO ACCOUNT(NUMBER, BALANCE) VALUES('acc2', 400);

Unit test

public class ConcurrentTransfersTest {
    @Test
    void testForStackOverflow() throws Exception {
        final String url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MULTI_THREADED=1;" +
                "INIT=RUNSCRIPT FROM 'classpath:/h2/schema.sql'\\;RUNSCRIPT FROM 'classpath:/h2/test-data.sql'";

        JdbcConnectionPool pool = JdbcConnectionPool.create(url, "sa", "");

        assertEquals(BigDecimal.valueOf(30000, 2), readData(pool, 1));
        assertEquals(BigDecimal.valueOf(40000, 2), readData(pool, 2));

        Thread t1 = new Thread(() -> updateData("t1", pool, 1, 2, BigDecimal.valueOf(50)));
        Thread t2 = new Thread(() -> updateData("t2", pool, 2, 1, BigDecimal.valueOf(200)));

        t1.start();
        Thread.sleep(200);
        t2.start();

        t1.join();
        t2.join();

        assertEquals(BigDecimal.valueOf(40000, 2), readData(pool, 1));
        assertEquals(BigDecimal.valueOf(30000, 2), readData(pool, 2));

        try (Connection c = pool.getConnection(); Statement st = c.createStatement()) {
            st.execute("SHUTDOWN IMMEDIATELY");
        }
    }

    private BigDecimal readData(DataSource ds, long id) {
        try (Connection c = ds.getConnection();
             PreparedStatement select = c.prepareStatement("SELECT BALANCE FROM ACCOUNT WHERE ID = ?")) {
            return readFromPreparedStatment(select, id);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    private BigDecimal readFromPreparedStatment(PreparedStatement select, long id) throws Exception {
        select.setLong(1, id);
        ResultSet rs = select.executeQuery();
        rs.next();
        BigDecimal b = rs.getBigDecimal(1);
        rs.close();

        return b;
    }

    private void updateData(String thread, DataSource ds, long from, long to, BigDecimal amount) {
        try {
            try (Connection c = ds.getConnection();
                 PreparedStatement minus = c.prepareStatement("UPDATE ACCOUNT SET BALANCE = BALANCE - ? WHERE ID = ?");
                 PreparedStatement plus = c.prepareStatement("UPDATE ACCOUNT SET BALANCE = BALANCE + ? WHERE ID = ?");
                 PreparedStatement select = c.prepareStatement("SELECT BALANCE FROM ACCOUNT WHERE ID = ?")
            ) {
                c.setAutoCommit(false);

                System.out.println(System.currentTimeMillis() + " " + thread + ": transfer " + amount + " " +
                                "from = " + readFromPreparedStatment(select, from) + " to = " + readFromPreparedStatment(select, to));

                minus.setBigDecimal(1, amount);
                minus.setLong(2, from);
                System.out.println(System.currentTimeMillis() + " " + thread + ": from minus " + amount);
                minus.executeUpdate();

                Thread.sleep(2000);

                plus.setBigDecimal(1, amount);
                plus.setLong(2, to);
                System.out.println(System.currentTimeMillis() + " " + thread + ": to plus " + amount);
                plus.executeUpdate();

                System.out.println(System.currentTimeMillis() + " " + thread + ": from = " + readFromPreparedStatment(select, from));
                System.out.println(System.currentTimeMillis() + " " + thread + ": to = " + readFromPreparedStatment(select, to));

                c.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Output

1508149269895 t1: transfer 50 from = 300.00 to = 400.00
1508149269895 t1: from minus 50
1508149271899 t1: to plus 50
1508149271900 t1: before commit from = 250.00
1508149271900 t1: before commit to = 450.00
1508149271913 t2: transfer 200 from = 400.00 to = 300.00
1508149271913 t2: from minus 200
1508149273918 t2: to plus 200
1508149273918 t2: before commit from = 200.00
1508149273918 t2: before commit to = 500.00

[tarasmurzenkovv]

Might be dumb assumption, but I think there is an issue with client code: did you try to put synchronized on the method updateData ?

Could you elaborate how did you know that

these two transactions a) run sequentially

?

[ismagilov]

did you try to put synchronized on the method updateData ?

If you use synchronized, two transactions run sequentially and the second one sees all changes made by the first one:

1508240821174 t1: transfer 50 from = 300.00 to = 400.00
1508240821175 t1: from minus 50
1508240823180 t1: to plus 50
1508240823180 t1: before commit from = 250.00
1508240823180 t1: before commit to = 450.00
1508240823181 t2: transfer 200 from = 450.00 to = 250.00 // all changes made by t1 are visible by t2 
1508240823181 t2: from minus 200
1508240825186 t2: to plus 200
1508240825186 t2: before commit from = 250.00
1508240825186 t2: before commit to = 450.00

Could you elaborate how did you know that: these two transactions a) run sequentially

Actually, they are not. It only looks like they run sequentially, if you observe output timings. They run in parallel. If you increase timeout between two updates Thread.sleep(2000);, you get org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "ACCOUNT";.

I would like to solve this concurrency issue using database facilities: locks, MVCC, isolation levels, etc. I read docs, run this test, and can't understand, why it works as it works?...

Here is the order of updates I try to emulate:

tx1	tx2
UPDATE ACCOUNT SET BALANCE = BALANCE - 50 WHERE ID = 1	...
...	UPDATE ACCOUNT SET BALANCE = BALANCE - 200 WHERE ID = 2
UPDATE ACCOUNT SET BALANCE = BALANCE + 50 WHERE ID = 2	...
...	UPDATE ACCOUNT SET BALANCE = BALANCE + 200 WHERE ID = 1
COMMIT	...
...	COMMIT

It seems that tx2 waits for some locks acquired by tx1, but it doesn't see changes made by tx1, when tx1 commits and tx2 proceeds. Could you please comment, which locks are acquired and what is visible for each of transactions?

P.S. If it somehow helps you, I can zip and attach a project with the test case.

[katzyn]

Latest version of H2 with MVStore engine in both multi-threaded (enabled by default) and single-threaded modes detects a deadlock in your test case and rolls back one of these transactions.

Deadlock detected. The current transaction was rolled back. Details: "ACCOUNT"; SQL statement:
UPDATE ACCOUNT SET BALANCE = BALANCE + ? WHERE ID = ? [40001-198]
Caused by: java.lang.IllegalStateException: Transaction 2 has been chosen as a deadlock victim. Details:
Transaction 1 attempts to update map <table.4> entry with key <2> modified by transaction 2(24) OPEN 3
Transaction 2 

Final balance is 250 / 450.

Such behavior is expected because your test case creates an ABBA deadlock.

In non-default PageStore mode both transactions are processed normally because PageStore locks entire tables. Such behavior is also expected.

So this issue is resolved, H2 doesn't return mixed results any more.

Logic in your code is not valid for real usage. You need to perform SELECT ID, BALANCE FROM ACCOUNT WHERE ID = ? OR ID = ? FOR UPDATE to lock both rows, but in your code two SELECT BALANCE FROM ACCOUNT WHERE ID = ? statements are executed, they do not lock rows so any concurrent transaction may change or lock them. PageStore engine actually needs it too if negative balances aren't allowed, because otherwise results of SELECT statements may be outdated. However, it's a good idea to have such test case, but it should be modified to accept possible rollback of transactions.