Flyway 4.2.0 Multiple Nodes In Parallel Failure with Oracle 11g

Question

I have multiple application servers configured to run flyway at startup. Each server attempts to apply the same set of migrations across multiple schemas in the same Oracle 11g database. These servers are started at the same time. This works most of the time. On occasion, however, a server fails during migration because it encounters a unique constraint violation.

Unable to insert row for version '0' in metadata table "FOO"."SCHEMA_VERSION"

SQL State : 23000 Error Code : 1 Message : ORA-00001: unique constraint (FOO.SCHEMA_VERSION_pk) violated

    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.addAppliedMigration(MetaDataTableImpl.java:242)
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.addBaselineMarker(MetaDataTableImpl.java:334)
    at org.flywaydb.core.internal.command.DbBaseline$2.call(DbBaseline.java:135)
    at org.flywaydb.core.internal.command.DbBaseline$2.call(DbBaseline.java:112)
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75)
    at org.flywaydb.core.internal.command.DbBaseline.baseline(DbBaseline.java:112)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:990)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971)

...

I thought that flyway would be able to handle this situation based on the following:

https://flywaydb.org/documentation/faq#parallel

Shouldn't a flyway instance detect that the schema version table is locked and move onto the next schema?

Is there a setting that can ensure the schema version is locked or is this a bug?

The OracleTable class locks the table in exclusive mode. Should it add the NOWAIT clause and handle any resulting Oracle exception?

Axel Fontaine Axel Fontaine · Accepted Answer · 2017-07-14T20:32:52

It should work. We test this with every build and the behavior without NOWAIT is the one we desired (block until lock is released). If you can reliably reproduce this or see a clear mistake in our code, then please by all means do file a bug with the necessary details in the issue tracker.

Flyway 4.2.0 Multiple Nodes In Parallel Failure with Oracle 11g

1 Answers