How to create Flyway schema history with snapshot isolation level?

Question

I'm investigating whether it would be possible to use Flyway for our database schema migration, on the project I've been working on... Database migrations are done manually and I would really like to start using Flyway.

It is a Spring Boot (v2.2.0) application and I'm trying to configure migration with Flyway version v.7.1.1.

I've generated existing schema from the database and set it as V1 base version. However, when trying to run the application (with baseline-on-migrate: true), migration fails due to exception:

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration  failed
-----------------
SQL State  : S0001
Error Code : 3964
Message    : Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.
Location   :  ()
Line       : 1
Statement  : CREATE TABLE [TestDB].[dbo].[flyway_schema_history] (
    [installed_rank] INT NOT NULL,
    [version] NVARCHAR(50),
    [description] NVARCHAR(200),
    [type] NVARCHAR(20) NOT NULL,
    [script] NVARCHAR(1000) NOT NULL,
    [checksum] INT,
    [installed_by] NVARCHAR(100) NOT NULL,
    [installed_on] DATETIME NOT NULL DEFAULT GETDATE(),
    [execution_time] INT NOT NULL,
    [success] BIT NOT NULL
);

From the app configuration, HikariCP datasource configuration is set to use:

transaction-isolation: TRANSACTION_SQL_SERVER_SNAPSHOT_ISOLATION_LEVEL

If transaction isolation is changed to e.g. TRANSACTION_READ_COMMITTED migration is successful. I could execute all other migrations as expected. Software architect told me reason behind snapshot isolation level is because application is used for generation of huge reports, that could take several hours and snapshot isolation level "protects us from application freeze and locks".

What I have tried is to go around this with creating callbacks where I could change transaction-isolation in runtime, in beforeMigrate and afterMigrate Java callbacks, but HikariDataSource configuration can't be changed in runtime.

Can someone help with some advice, whether migration (and creating baseline schema) is possible with snapshot isolation?

Answer 1

I've managed to bypass application's Hikari data source isolation level by creating separate data source for Flyway migration, where transaction isolation is set to READ_COMMITED. Flyway migration is run successfully and primary data source configuration is untouched (with SNAPSHOT isolation).

@Bean
@FlywayDataSource
public DataSource flywayDataSource(@Autowired @Qualifier(value = "primaryDataSource") DataSource primaryDataSource) {
    HikariDataSource hds = (HikariDataSource) primaryDataSource;
    HikariConfig flywayHikariConfig = new HikariConfig();
    hds.copyStateTo(flywayHikariConfig);
    flywayHikariConfig.setTransactionIsolation(IsolationLevel.TRANSACTION_READ_COMMITTED.toString());
    return new HikariDataSource(flywayHikariConfig);
}

I wasn't able to apply similar logic with Flyway callbacks, Java nor SQL, where ISO level is changed. However, I'm thinking this can be done differently, by setting @Transactional(isolation=Isolation.READ_COMMITTED) on some Flyway configuration bean, but I couldn't make it work (e.g. tried with FlywayMigrationStrategy).