使用HSQL在flyway中进行Changin DB事务控制 [英] Changin DB transaction control in flyway with hsql

问题描述

在HSQL中更改事务控制有'不会是任何活跃交易. 反过来，在提交迁移X之后和从迁移X执行SQL之前，Flyway会设置autocommitt = false并执行其自己的某些语句.因此，如果迁移包含SET DATABASE TRANSACTION CONTROL语句，它将永远等待那些未提交的语句导致应用程序挂起.

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.

(附带说明:迁移之前flyway执行的语句因版本而异，例如在1.7中是纯选择，因此可以从LOCK更改为MVCC，但在我拥有MVCC之后，进一步迁移中的任何后续DDL语句都将挂起；在flyway中2.0选择了schema_version表上的更新，因此任何事务控制更改都被挂起；在2.2中，选择更新已更改为显式锁，其效果与2.0中相同)

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

因此，从根本上讲，无法在飞行路线迁移中更改事务控制.另一方面，飞行路线不鼓励其迁移以外的变化.然后知道如何使用flyway/hsql更改事务控制吗?

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?

更新 另一个观察结果是，当数据库控制设置为MVCC时，flyway迁移中的任何DDL语句也会挂起应用程序.因此，我只是在每次迁移之前设置LOCKS，然后在它之后恢复MVCC.从Flyway的角度来看，这将是干净的解决方案吗?

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

推荐答案

尝试使用 Flyway回调 beforeMigrate 和 afterMigrate .两者都与迁移事务分开. MVCC应该用于我的应用程序，因此JDBC URL包含hsqldb.tx=mvcc.在Flyway迁移期间，我可以使用 beforeMigrate.sql SET DATABASE TRANSACTION CONTROL LOCKS;和 afterMigrate.sql SET DATABASE TRANSACTION CONTROL MVCC;成功地更改事务模型.还有回调的Java版本.我正在使用HSQLDB 2.3.3和Flyway 3.2.1.

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.

这篇关于使用HSQL在flyway中进行Changin DB事务控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！