flyway迁移hsqldb到mysql,使用HSQL在flyway中进行Changin DB事务控制

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

}

}

解决方案

Try to use the Flyway callbacks beforeMigrate and afterMigrate. Both run apart from the migration transactions. MVCC should be used for my application so the the JDBC URL contains hsqldb.tx=mvcc. I could sucessfully change the transaction model during the Flyway migration with beforeMigrate.sql SET DATABASE TRANSACTION CONTROL LOCKS; and afterMigrate.sql SET DATABASE TRANSACTION CONTROL MVCC;. There are also Java versions of the callbacks. I'm using HSQLDB 2.3.3 and Flyway 3.2.1.