多个应用实例尝试同时迁移同一数据库 [英] Several app instances trying to migrate the same database at the same time

问题描述

我刚开始在带有MySQL数据库的标准Spring 2.5 Web应用程序中使用flyway.它已经投入生产多年，并且拥有大量数据.

I just started using flyway in a pretty standard spring 2.5 web application with a mysql databse. It has been in production for years and has a lot of data.

我将其连接起来以在启动时自动迁移

I hooked it up to auto-migrate on startup with

<bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate">
    <property name="dataSource" ref="dataSource"/>
</bean>

并使Spring的持久性单元管理器(使用jpa)依赖于它，以便在JPA初始化之前就可以迁移数据库.

and the made the Spring's persistence unit manager (using jpa) depend on it so the database would be migrated before JPA initializes.

这很好.

在生产环境中，我们有几个节点希望同时更新. 因此，如果每个实例长时间运行，它们将尝试执行相同的迁移. Flyway的锁定功能应避免这种情况.

In our production environment we have several nodes which we like to update concurrently. Thus each instance will try to execute the same migrations if they are long-running. Flyway's locking functionality should prevent that.

我喜欢在生产前做任何事情之前对这些东西进行测试，所以我做到了.并且当两个应用程序实例同时启动并进行了长时间迁移(6分钟)时发生的情况是，第二个实例在一段时间后因以下原因而失败:

I like to test these things before doing anything in production so I did; and what happened when two app instances were starting at the same time with a long running migration (6 minutes) is that after a while the second instance failed with:

 Caused by: com.googlecode.flyway.core.exception.FlywayException: Unable to lock metadata table 'schema_version' in schema 'dbschema'
    at com.googlecode.flyway.core.metadatatable.MetaDataTable.lock(MetaDataTable.java:148)
    at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:116)
    at com.googlecode.flyway.core.migration.DbMigrator$1.doInTransaction(DbMigrator.java:114)
    at com.googlecode.flyway.core.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:54)
    at com.googlecode.flyway.core.migration.DbMigrator.migrate(DbMigrator.java:113)
    ...

在堆栈跟踪中更深处是SQLException:

Deeper down in the stack trace is the SQLException:

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    ...

基本上看来，它超时了.

Essentially it seems to timeout.

我们正在使用mysql和afaik，它试图通过以下方式锁定元数据表

We're using mysql and afaik it's trying to lock the metadata table with

select * from dbschema.schema_version for update

我检查了它试图迁移的数据库及其锁定等待超时:

I checked the db it was trying to migrate and its lock wait timeout:

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120   |
+--------------------------+-------+

明显是罪魁祸首. 但是，我不确定是否要增加该数量，因为如果由于其他原因而发生这种情况，我可能希望它超时.

Which is the obvious culprit. However, I'm not sure I want to increase the amount since I might want it to timeout if such an occurrence were to happen for some other reason.

我可能会尝试在迁移过程中将其设置为更高的值. 除了它似乎是一个只读变量:

I might try to set it to a higher value for the duration of the migration. Except that it seems to be a read only variable:

SET innodb_lock_wait_timeout = 240;
ERROR 1238 (HY000): Variable 'innodb_lock_wait_timeout' is a read only variable

如果我知道例如要进行长期迁移，我当然可以半手动进行部署.只需更新一个实例并等待直到它通过迁移，然后再更新其他实例即可.

I could of course do deployment semi-manually if I know there are going to be long running migrations, by e.g. just updating one instance and wait til it passes migration and then update the other instances.

还有其他建议吗?

推荐答案

有3种可能性:

• 移动到MySQL 5.5，您可以在其中设置在运行时innodb_lock_wait_timeout
• 在服务器配置中增加全局innodb_lock_wait_timeout
• 拆分迁移，使其不会超过超时时间

这篇关于多个应用实例尝试同时迁移同一数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！