Problems with transaction support and H2 database

[steffen.e...@gmail.com]

I stumbled upon a problem while trying to write some tests again a h2 in-memory database using jooq. I do not know if the cause for the problem is my code, h2 or jooq.

I am using the transaction management from jooq (create.transaction(TransactionalRunnable ...)). When issuing a conflicting command (primary key violation), jooq tries to rollback the transaction, but without success. Jooq reports an error, leaves the transaction open and the row is still locked, resulting in further problems.

You can find a maven project with a unit test demonstrating the problem here: https://github.com/scameronde/jooq-rollbacktest

It is the second test that is not working. The first test is there just to demonstrate that the happy path is working.

The lines from the stack trace that seem to indicate the problem are:

Suppressed: org.jooq.exception.DataAccessException: Cannot rollback transaction

at org.jooq.impl.DefaultConnectionProvider.rollback(DefaultConnectionProvider.java:142)

at org.jooq.impl.DefaultTransactionProvider.rollback(DefaultTransactionProvider.java:220)

at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:344)

... 30 more

Caused by: org.h2.jdbc.JdbcSQLException: Savepoint ist ungültig: "SYSTEM_SAVEPOINT_0"

Savepoint is invalid: "SYSTEM_SAVEPOINT_0"; SQL statement:

ROLLBACK TO SAVEPOINT SYSTEM_SAVEPOINT_0 [90063-190]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.get(DbException.java:155)

at org.h2.engine.Session.rollbackToSavepoint(Session.java:946)

at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:59)

at org.h2.command.CommandContainer.update(CommandContainer.java:78)

at org.h2.command.Command.executeUpdate(Command.java:253)

at org.h2.jdbc.JdbcSavepoint.rollback(JdbcSavepoint.java:66)

at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:1037)

at org.jooq.impl.DefaultConnectionProvider.rollback(DefaultConnectionProvider.java:139)

... 32 more

Greetings

Steffen

package h2rollbacktest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.jooq.SQLDialect;
import org.jooq.TransactionalRunnable;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.junit.After;
import org.junit.Before;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;

@FixMethodOrder(MethodSorters.NAME_ASCENDING)
public class RollbackTest {

  @Before
  public void setup() {
    withTransaction(configuration -> {
      DSL.using(configuration)
         .createTable("TEST")
         .column("ID", SQLDataType.BIGINT.nullable(false))
         .execute();
      DSL.using(configuration)
         .alterTable("TEST")
         .add(DSL.constraint("PK").primaryKey("ID"))
         .execute();
    });
  }

  @After
  public void cleanup() {
    withTransaction(configuration -> {
      DSL.using(configuration)
         .dropTable("TEST")
         .execute();
    });
  }

  @Test
  public void testDelete() {
    withTransaction(configuration -> {
      DSL.using(configuration)
         .query("insert into TEST (ID) values (1)")
         .execute();
    });
    withTransaction(configuration -> {
      DSL.using(configuration)
         .query("delete from TEST where ID=1")
         .execute();
    });
  }

  @Test
  public void testDeleteAfterRollback() {
    withTransaction(configuration -> {
      DSL.using(configuration)
         .query("insert into TEST (ID) values (1)")
         .execute();
    });
    withTransaction(configuration -> {
      DSL.using(configuration)
         .query("insert into TEST (ID) values (1)")
         .execute();
    });
  }

  private Connection get() {
    String url = "jdbc:h2:mem:runtime;DB_CLOSE_DELAY=-1";
    Properties props = new Properties();

    try {
      Connection connection = DriverManager.getConnection(url, props);
      connection.setAutoCommit(false);
      return connection;
    }
    catch (SQLException e) {
      throw new DataAccessException("no connection possible", e);
    }
  }

  private void withTransaction(TransactionalRunnable transactionalCode) {
    try (Connection connection = get()) {
      DSL.using(connection, SQLDialect.H2).transaction(transactionalCode);
    }
    catch (SQLException e) {
      throw new DataAccessException("no transaction possible", e);
    }
  }
}

[Lukas Eder]

Hi Steffen,

Thanks for your report. Off-topic, I didn't know about

@FixMethodOrder(MethodSorters.NAME_ASCENDING)

That's very useful! Thanks for letting us know :)

I can reproduce the issue that you've mentioned with the in-memory JDBC URL for H2, but not with the file-based URL, e.g. "jdbc:h2:~/rollback-test", so my first guess would be that there's an issue in H2, or in the way you're using the DB_CLOSE_DELAY=-1 argument. If you really prefer the in-memory mode, I think you should keep the Connection open for the complete test suite, i.e.

  Connection connection;

  private Connection get() {

    if (connection == null) {

      try {

        connection = DriverManager.getConnection("jdbc:h2:mem:runtime;DB_CLOSE_DELAY=-1", new Properties());

        connection.setAutoCommit(false);

      }

      catch (SQLException e) {

        throw new DataAccessException("no connection possible", e);

      }

    }

    return connection;

  }

  private void withTransaction(TransactionalRunnable transactionalCode) {

    DSL.using(get(), SQLDialect.H2).transaction(transactionalCode);

  }

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Steffen Eichenberg]

Hello Lukas,

thank you for having a look at my problem yourself. I wanted to make sure, that the rollback problems had their origin in H2 and not in my understanding of the transaction handling of jooq.

I will switch to file mode for H2 for our tests.

And regarding the @FixMethodOrder annotation of JUnit: I discovered this annotation while trying to distill my problem for this help request. Years of using JUnit and there are still lots of things to discover :)

Greetings

Steffen