迁移Spring Batch的MyISAM数据顺序表为InnoDB [英] Migrating Spring Batch MyISAM Sequence Tables to InnoDB

问题描述

春季一批使用了一些顺序表，这在MySQL的情况下使用MyISAM存储引擎。我现在面临的问题是，我使用的是亚马逊网络服务RDS数据库，和他们的时间点数据库恢复功能不包含MyISAM表的数据库发挥很好。

Spring batch uses a few sequence tables, which in the case of MySQL use the MyISAM storage engine. The issue I'm facing is that I'm using an Amazon Web Services RDS database, and their "Point in Time" database restore feature does not play nicely with databases containing MyISAM tables.

我在寻找，这将让我转出这些Spring Batch的MyISAM数据顺序表的解决方案，并与InnoDB表代替他们，而不是与启用AWS RDS时间点数据库恢复功能的目的。

I'm looking for a solution that will let me switch out these Spring Batch MyISAM sequence tables and replacing them with InnoDB tables instead, with the goal of enabling the AWS RDS "Point in Time" database restoration feature.

编辑：

每@迈克尔的响应，这里是从Java类的注释 MySQLMaxValueIncrementer 读取顺序：​​

Per @Michael's response, here's a comment from the Java class MySQLMaxValueIncrementerthat reads the sequence:

The sequence is kept in a table; there should be one sequence table per
table that needs an auto-generated key. The table type of the sequence table
should be MyISAM so the sequences are allocated without regard to any
transactions that might be in progress.

所以，我的具体问题是什么是最简单的方法来消除MyISAM的序列表，并保持Spring Batch的同时哼唱？

So my specific question is "what is the simplest possible way to remove the MyISAM sequence table" and keep Spring Batch humming along?

推荐答案

我证实，仅仅改变的MyISAM顺序表为InnoDB导致要在之后的更新序列表创建更新锁...设置... = LAST_INSERT_ID（）语句，但交易一直致力于之前。使用MyISAM序列时，不创建这些锁。因此，易的方式可能会对性能产生负面影响。

I confirmed that merely changing the MyISAM sequence tables to InnoDB causes update locks to be created on the sequence tables after the update...set...=last_insert_id() statement but before the transaction has been committed. These locks are not created when using the MyISAM sequences. So the "easy" approach could have a negative impact on performance.

下面是我想出了。不知道，这是最简单的方法，但它的工作。

Here's what I came up with. Not sure that this is the easiest way, but it is working.

1. 在每这个回答，删除现有的序列表和与单个列 UID重新定义它们BIGINT（20）UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
2. 创建一个存储的过程：1）取序列名作为参数，b）当插入到序列，以及c）返回LAST_INSERT_ID（）
3. 在编写扩展 MySQLMaxValueIncrementer ，并呼吁在 getNextKey（）方法的存储过程的Java类。我使用的是 SimpleJdbcCall 实例来做到这一点。
4. 在编写一个Java类实现 DataFieldMaxValueIncrementerFactory 和步骤＃3从 getIncrementer（）方法返回一个实例
5. 在批量配置，更新 org.springframework.batch.core.repository.support.JobRepositoryFactoryBean 配置步骤＃使用增量厂4

1. Per this answer, drop the existing sequence tables and redefine them with a single column uid BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment
2. Create a stored procedure that: a) takes the sequence name as an argument, b) does the insert into the sequence, and c) returns LAST_INSERT_ID()
3. Write a java class that extends MySQLMaxValueIncrementer and calls the stored procedure in the getNextKey() method. I'm using a SimpleJdbcCall instance to do this.
4. Write a java class that implements DataFieldMaxValueIncrementerFactory and returns an instance from step #3 from the getIncrementer() method
5. In the batch configuration, update the org.springframework.batch.core.repository.support.JobRepositoryFactoryBean configuration to use the incrementer factory from step #4

这篇关于迁移Spring Batch的MyISAM数据顺序表为InnoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！