Isolation level per connection or global?

[Knut Saua Mathiesen]

Hi.

I'm debugging an issue where we have an application which both uses READ_COMITTED and SERIALIZABLE.

In an integration testing scenario we pull up the entire application and start execute commands at it. Some of them will use READ_COMITTED and some SERIALIZABLE. It appears to me that H2 will set the isolation level globally and not per connection? Is this by design?

Here's a sample code:

var driver = new Driver();
Connection con1 = driver.connect("jdbc:h2:/tmp/h2-test;AUTO_SERVER=TRUE", null);
Connection con2 = driver.connect("jdbc:h2:/tmp/h2-test;AUTO_SERVER=TRUE", null);
con1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
System.out.println(con1.getTransactionIsolation()); // Outputs 2. Should output 8?
System.out.println(con2.getTransactionIsolation()); // Outputs 2

Thanks!

/Knut

[Evgenij Ryazanov]

Hello.

H2 with default MVStore engine supports different isolation levels only since the version 1.4.200, it looks like you use some older version.

Note that SERIALIZABLE isolation level in H2 is only partially supported, it prevents all three read phenomena, but doesn't ensure equivalence of sequential and concurrent execution of transactions.

https://h2database.com/html/advanced.html#transaction_isolation

You also shouldn't try to create Driver instances by yourself, it's a bad idea. If you need it due to some problem with DriverManager, use the instance returned by org.h2.Driver.load(). In normal cases you should use DriverManager.getConnection(), JdbcDataSource, or JdbcConnectionPool.

[Knut Saua Mathiesen]

> H2 with default MVStore engine supports different isolation levels only since the version 1.4.200, it looks like you use some older version.

I was using 1.4.196, updating to 1.4.200 appears solved the problem.

> You also shouldn't try to create Driver instances by yourself, it's a bad idea.

Good point.

Thanks!