Hi,

In the project which I'm working on, we need to run non-transactional migration, but it seems that spring flyway autoconfigure doesn't allow configuring the option flyway.postgresql.transactional.lock - flyway docs.

Is there any other option how for configuring transaction lock with spring config?

Comment From: wilkinsona

As described in the Spring Boot documentation, you can use a FlywayConfigurationCustomizer bean to fine-tune Flyway's configuration. The customizer can use the API that Flyway documents.

If you have any further questions, please follow up on Stack Overflow or Gitter. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements.

Comment From: wleese

@wilkinsona , this issue (https://github.com/flyway/flyway/issues/3492) seems to be quite a big one. I think it would be a good idea to have this property, as I'm assuming that as of Spring Boot 3.x, it will be an issue for many users.

Comment From: wleese

In our Spring Boot based framework, we provided a FlywayConfigurationCustomizer bean that set flyway.postgresql.transactional.lock to false. We see however that when our users use e.g. @JdbcTest for integration tests, they can accidentally run into issues again where flyway.postgresql.transactional.lock is true (the default).

Note that the migration script that was hanging was due to a CREATE INDEX statement, which has been documented as being incompatible with the default flyway.postgresql.transactional.lock setting.

Comment From: radek-los-s1

You can put in gradle.properties:

flyway.postgresql.transactional.lock=false

Comment From: wleese

Thanks @radek-los-s1 , however:

There are 3 ways I'm aware of how to run flyway migrations:

  • Via a build (maven/gradle) plugin
  • Via the flyway binary
  • Programmatically, like how Spring Boot runs your migrations during application startup

Out of all the methods of configuring flyway migrations (https://flywaydb.org/documentation/configuration/parameters/postgresqlTransactionalLock), the only way to change a Spring Boot triggered flyway migration configuration, is via the provided Spring Boot Flyway properties / programmatically via the api.

We'd strongly prefer Spring Boot provides a property to configure this setting, as it has become a common source of issues for users.

Comment From: philwebb

There is a configuration(Map<String,String> ...) method on FluentConfiguration. We might be able to surface properties to fill this map, however, we're not totally sure yet of the format theses properties take and if they'll fit with our application.properties style.

Comment From: wilkinsona

The map takes properties in the standard Flyway format which is dot-separated with camel case parts.

If we had a Map<String, String> spring.flyway.configuration property, you would disable the transactional lock by configuring spring.flyway.configuration.flyway.postgresql.transactional.lock=false.

The map would allow any Flyway property to be configured, the vast majority of which would clash with our existing properties, for example:

spring.flyway.connect-retries=2
spring.flyway.configuration.flyway.connectRetries=3

Here connect retries would be either 2 or 3 depending on whether we applied the single spring.flyway.connect-retries property or the spring.flyway.configuration map of properties first.

My feeling is that if we're going to do something here, it should be for specific properties rather than opening up the Map<String, String> option.

Comment From: mathieu-pousse

For information, this works fine: 

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer() {
        return configuration -> configuration.configuration(Map.of("flyway.postgresql.transactional.lock", "false"));
    }

As org.springframework.boot.autoconfigure.flyway.FlywayProperties does not have any field to receive additional settings (to gather spring.flyway.configuration.*) and pass them to Flyway, I think that using a customizer is the only way so far

Comment From: nunotavaresbmw

How do you use this property in application.properties?

flyway.postgresql.transactional.lock=false

Comment From: wilkinsona

The application property is spring.flyway.postgresql.transactional-lock. This is also mentioned in the release notes which are recommended reading when upgrading to a new version of Spring Boot.

Comment From: jluiz20

I am using Spring Boot 3.2.4 and Flyway 9.22.3 and have tested both spring.flyway.postgresql.transactional-lock=false from https://github.com/spring-projects/spring-boot/issues/32629#issuecomment-1729261726 and flyway.postgresql.transactional.lock=false from https://github.com/flyway/flyway/issues/3682#issuecomment-2012211547 but nothing worked.

I have even tried 

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer() {
        return configuration -> configuration.configuration(Map.of("flyway.postgresql.transactional.lock", "false"));
    }

from https://github.com/spring-projects/spring-boot/issues/32629#issuecomment-1589223825

However, my issue was that I was using a custom Flyway Bean, and because of that, the properties were not being loaded automatically.

For me, what worked was to build up from the previous answers, so I added .configuration(Map.of("flyway.postgresql.transactional.lock", "false")) to my bean and it worked.

Below is a reduced form of it.

 @Bean
    public Flyway flyway(FlywayProperties flywayProperties)
    {

        Flyway flyway = Flyway.configure()
               // previous configurations

               // below the added line
                .configuration(Map.of("flyway.postgresql.transactional.lock", "false"))
                .load();

        flyway.migrate();

        return flyway;
    }

Not sure if this is the best approach, but it worked for me. If you guys have improvement suggestions, they are welcome.

Comment From: enricojonas

If it helps someone, we are using Micronaut and after a lot of digging we finally found a way to configure it in the application properties yaml / bootstrap.yml. Basically it's a configuration for the datasource.

flyway:
  datasources:
    default:
      properties:
        flyway:
          postgresql:
            transactional:
              lock: false

Comment From: albertscholtz

Solution provided by @jluiz20 was the only one that worked for me, after trying Gradle and Spring properties related suggestions, i.e. I had to create a custom Flyway bean. Gist

Using Spring Boot 3.2.4 and Flyway 10.11.0. I'm guessing it depends whether flyway is run from the Gradle plugin or spring boot app during startup which solution will have the desired effect.

Comment From: htpaf

I have debugged the property spring.flyway.postgresql.transactional-lock and the property is indeed set on https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayProperties.java#L915 for SpringBoot v.3.3.5. Flyway v9.22.3 is in use at the moment and is important depending on the chosen solution "in the end" as described below.

However, https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L162 and https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L538 are not run/used here -> https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L177

The configuration setting and application use of the setting seem disconnected.

The only reason for this not working that I can think of is that https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L160 is not present on the classpath. IF that is how all of this is supposed to connect (?).

This is the reason online searches "always" turn up the

    @Bean
    public FlywayConfigurationCustomizer flywayCustomizer() {
        return (FluentConfiguration configuration) -> configuration.configuration(
            Map.of("flyway.postgresql.transactional.lock", "false"));
    }

-solution, or some variation thereof. Because it inserts itself in the https://github.com/spring-projects/spring-boot/blob/2316ce688c31166a6c4f3909b9e3ce8d37efdc5c/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration.java#L177 execution path.

Given that https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-3.2.0-M1-Release-Notes#miscellaneous declares the supported Flyway version is at 9.20, one can tend to believe that.

However, Flyway modularized its code in v10 so flyway-core no longer contains the SQL dialect code, e.g. PostgreSQL. 

  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-dependencies</artifactId>
  <version>3.3.5</version>
  <packaging>pom</packaging>

has <flyway.version>10.10.0</flyway.version> so if you use 

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
          <version>${flyway.version}</version>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-database-postgresql</artifactId>
          <version>${flyway.version}</version>
        </dependency>

given that you have somehow imported or inherited spring-boot-dependencies , typically using spring-boot-starter-parent v.3.3.5 for example, you will inherit ${flyway.version} (if your parent is spring-boot-starter-parent then you should not need explicitly set version at all).

Now the previous pointer to the class conditional is fulfilled:

   FlywayAutoConfiguration.FlywayConfiguration#postgresqlFlywayConfigurationCustomizer matched:
      - @ConditionalOnClass found required class org.flywaydb.database.postgresql.PostgreSQLConfigurationExtension' (OnClassCondition)

Now you can use spring.flyway.postgresql.transactional-lock: false in your application.yml without the need for your configuration code like

 @Bean
    public FlywayConfigurationCustomizer ...

Summary: spring.flyway.postgresql.transactional-lock: false can be used with

<artifactId>spring-boot-starter-parent<artifactId> v.3.3.5, 
<artifactId>flyway-core<artifactId> v10.10.0
<artifactId>flyway-database-postgresql<artifactId> v10.10.0

Possibly related https://github.com/flyway/flyway/issues/3682 https://github.com/flyway/flyway/issues/3961