1

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?

Milan
  • 11
  • 5

2 Answers2

1

I am a huge fan of Snapshot Isolation in SQL Server, but it is not a great fit as an isolation level for all of your deployments.

Here are three major things to consider:

1. Limits of snapshot isolation regarding DDL and explicit multi statement transactions

Snapshot isolation is incredibly useful, however SQL Server does limit what you can use it for, as the error message indicates:

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.

This doesn't ONLY apply to creating the flyway_schema_history table, SQL Server will also apply this rule to other DDL changes which you have in migration scripts.

More information is in the documentation here:

SQL Server does not support versioning of metadata. For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement.

2. Schema Modification locks are required for DDL operations no matter what

In addition, even under snapshot isolation, DDL changes will require a high level schema modification lock (SCH-M), which requires exclusive access on the table. There is more on this in that same paper:

The SQL Server Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

In other words, when it comes to any DDL operations you may have in your migration scripts, Snapshot Isolation can't/won't provide less blocking and application freezing.

3. If you use Snapshot Iso on data modifications, you need to be prepared to handle Update Conflicts

Snapshot may be used to update data in DML statements, but it does add complexity which you will need to add error handling for. Update conflicts may occur if data in a table may be modified by two processes at the same time:

If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. The update conflict is handled by the SQL Server Database Engine and there is no way to disable the update conflict detection.

Where Snapshot Isolation can help

From what you've said, I think it makes sense for the application/ reports to continue using Snapshot Isolation, but for you to run your deployments in Flyway from the default Read Committed Isolation level.

As long as the app/Reports are using snapshot isolation, this means that they will not be blocked by any inserts/updates/deletes you do from your flyway deployments.

Any DDL you do altering schema can potentially block the reports. However, using Snapshot Isolation in the deployments wouldn't change that for you-- you need SCH-M locks either way.

Kendra

Dharman
  • 30,962
  • 25
  • 85
  • 135
Kendra Little
  • 420
  • 2
  • 6
  • Hi kendra, thanks for your reply. It helped me better understand the problem. However, based on your answer I have few assumptions I'm not sure are correct. 1. This definitely means I can't use snapshot iso level for Flyway deployments, due to SQL Server's exception above? 2. Based on your suggestion, I could go with an option to use READ_COMMITED isolation level, while in application, where it makes sense, pass different Isolation level within @Transactional annotation? Confusing part to me, what happens when different ISO levels are set, one it RDBMS and other in Hikari CP – Milan Nov 09 '20 at 13:12
  • Hi -- you can use SNAPSHOT, but you would need to set it on and off in the migration scripts around the types of transactions/statements which SQL Server allows it for -- like: SET TRANSACTION ISOLATION SNAPSHOT ON; (and OFF) Isolation level is specific to each session, and you can change it throughout your session. The RDBMS impacts what is the default isolation level. Different sessions may be using different isolation levels concurrently. Hope that helps! – Kendra Little Nov 10 '20 at 14:10
0

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).

Milan
  • 11
  • 5