Changin DB transaction control in flyway with hsql

In HSQL to change TRANSACTION CONTROL there can't be any active transactions. Flyway, in turn, after committing migration X and before executing SQL from migration X, sets autocommitt=false and executes some of its own statements. So if the migration contains SET DATABASE TRANSACTION CONTROL statement it will wait for those uncommitted statements forever causing application to hang.

(Side note: The statements executed by flyway before migration varies from version to version e.g. in 1.7 that were pure selects so changing from LOCK to MVCC was possible but after I had MVCC any subsequent DDL statements in further migrations hanged; in flyway 2.0 it was select for update on schema_version table so any transaction control change hanged; in 2.2 select for update was changed to explicit lock with the same effect as in 2.0)

So basically it is not possible to change transaction control in flyway migrations. On the other hand flyway discourages changes outside of its migration. Any idea then how to change transaction control in with flyway/hsql?

Update Another observation is that when database control is set to MVCC then any DDL statement in flyway migration hangs application too. So I would just set LOCKS before each migration and restore MVCC after it. Would that be clean solution from Flyway perspective?

import com.googlecode.flyway.core.util.jdbc.JdbcUtils;
public void migrate() {
    setDbTransactionControl("LOCKS");
    flyway.migrate();
    setDbTransactionControl("MVCC");
}

private void setDbTransactionControl(String mode) {
    Connection connection = null;
    try {
        connection = JdbcUtils.openConnection(ds);
        connection.createStatement().execute("SET DATABASE TRANSACTION CONTROL " + mode);
    } catch (SQLException e) {
        //log it
        JdbcUtils.closeConnection(connection);
    } finally {
        JdbcUtils.closeConnection(connection);
    }
}

Answers


This is not possible inside a Flyway migration.

Before Flyway starts a migration, it opens a transaction in a separate connection to acquire a lock on its metadata table. So you will never be able to execute a statement that absolutely must be run without any other transactions.

Your best option is probably to set it on the datasource, so it can init each connection this way upon create.


Need Your Help

Read-only form field formatting

django forms admin

I want to display a field as read only in a ModelAdmin form, so I added it to the readonly_fields attribute.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.