How to setup r2dbc transaction properly

Question

I am using spring-r2dbc and h2 for writing a transaction but am not sure about the proper transaction boundary setup.

@SpringBootApplication
@EnableTransactionManagement
public class R2dbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(R2dbcApplication.class, args);
    }
}

@Component
class Runner {
    @Autowired
    ConnectionFactory cf;

    @EventListener(ApplicationStartedEvent.class)
    //@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class)
    public Mono<Void> runOnStartup(){       

        Mono emitter = Mono.from(cf.create())
                .flatMap(c-> Mono.from(c.setTransactionIsolationLevel(IsolationLevel.SERIALIZABLE))
                        .then(Mono.from(c.setAutoCommit(false)))
                        .then(Mono.from(c.beginTransaction()))
                        .then(Mono.from(c.createStatement(
                                        "CREATE TABLE CUSTOMER (id INTEGER, name VARCHAR(255));")
                                .execute()))
                        .then(Mono.delay(Duration.ofSeconds(60)))
                        .then(Mono.from(c.createStatement(
                                        "INSERT INTO CUSTOMER VALUES ('100','XXX');")
                                .execute()))
                        .then(Mono.from(c.commitTransaction()))
                        .onErrorResume(ex -> Mono.from(c.rollbackTransaction()).then(Mono.error(ex)))
                        .doFinally((st)->c.close()));

        emitter.subscribe();

        return Mono.empty();
    }
}

From the CREATE statement above, once the table is created in the database, I am altering the column as ALTER TABLE CUSTOMER DROP COLUMN name through h2 console while the Delay 60 seconds executes, so the INSERT will fail giving an exception. But the transaction is not rolling back after the exception. How do I ensure either the isolation strategy is locking the table and ALTER statement fails or that the transaction can rollback and create table sql is rolled back. reference doc I followed

application.yml

spring:
  jdbc:
    url: jdbc:h2:mem:~/db/testdb;DB_CLOSE_DELAY=-1;LOCK_MODE=3
    username: sa
    password:
    initialization-mode: always

  r2dbc:
    url: r2dbc:h2:mem:///~/db/testdb;DB_CLOSE_DELAY=-1;LOCK_MODE=3
    username: sa
    password:
    initialization-mode: always

Exception

reactor.core.Exceptions$ErrorCallbackNotImplemented: io.r2dbc.spi.R2dbcBadGrammarException: [21002] [21S02] Column count does not match; SQL statement:
INSERT INTO CUSTOMER VALUES ('100','XXX') [21002-214]

H2 doesn't support transactional DDL, `ALTER TABLE … DROP COLUMN …` commits an open transaction: https://h2database.com/html/commands.html#alter_table_drop_column You can rollback only DML commands (`INSERT`, `UPDATE`, `DELETE`, `MERGE`, etc.) — Evgenij Ryazanov, Jul 18 '22 at 00:13
@EvgenijRyazanov Will try DML commands as you suggested and will update shortly — user3747396, Jul 18 '22 at 01:41

score 1 · Accepted Answer · answered Jul 18 '22 at 05:14

As clearly indicated by @Evgenij in the comment, rollback worked on DML commands. For testing I tried throwing .then(Mono.error(new Exception("Expect Failure..."))) and verified the transaction rollback on simple DML and no transaction roll back for DDL commands.

How to setup r2dbc transaction properly

1 Answers1