CODEWITHSUNDEEP
javamysqlspringspring-batch
lunaspeed
lunaspeed

Reputation: 175

Spring batch MySql Sequence "Could not obtain last_insert_id()" reguarding engine type

I am using Spring Batch 3.0.5. The same question regarding engine type for the MySql sequence tables. Almost the same question as spring-batch-storage-engine-as-myisam-than-innodb. However I am getting errors (using ENGINE = InnoDB) such as 

org.springframework.dao.DataAccessResourceFailureException: Could not obtain last_insert_id(); nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:118) ~[spring-jdbc-4.1.3.RELEASE.jar:4.1.3.RELEASE]

After looking around I found there is a contradiction between what Spring Batch is doing and what the document says on MySQLMaxValueIncrementer

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.

In order to keep using InnoDB for the sequence tables to support GTID replication, it seems like we have to implement our own strategy like this.

My question would be if Spring Batch want to be consistent, why doesn't Spring Batch provide a built-in way or an alternative to the default MySQLMaxValueIncrementer?

Or is there something I am missing?

Upvotes: 0

Views: 2801

Answers (1)

Vignesh Shiv
Vignesh Shiv

Reputation: 1157

Hope, this link will give you a idea about your queries.

If you are using MySQL DB, then Sequence table should be in ENGINE=MyISAM DEFAULT CHARSET=utf8; You can have innodb also. But MyISAM engine has a built in to increment with different values according to your specific column.

Upvotes: 1

Related Questions