CODEWITHSUNDEEP
h2spring-data-r2dbcr2dbc
user3747396
user3747396

Reputation: 151

How to setup r2dbc transaction properly

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]

Upvotes: 0

Views: 1845

Answers (1)

user3747396
user3747396

Reputation: 151

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.

Upvotes: 1

Related Questions