Problems with transaction support and H2 database

[steffen . eichenberg]

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);
    }
  }
}

-- 
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.