MySQL Alter 表防止回滚 DML(？)

Question

我有一个严重依赖 MySQL 回滚进行单元测试的 Spring/JDBC 应用程序。我发现，如果我在这些事务中执行某些 DDL 操作(即使是在临时表上)，即使在正常的 DML 语句上，回滚也会失败。例如:

@Test
@Rollback(true)
public void testRollbackProblem() {
    template.update("create temporary table foo (id INTEGER )");
    template.update("update forms set form_name = 'blah' where form_id = 1412");
    template.update("alter table foo add (name text)");
}

此测试完成后，中间语句将被持久化，不会回滚。有没有办法防止这种情况发生？也许某些参数传递给 alter 语句？

Answer 1

据我所知，这是 MySQL 的限制。 CREATE TABLE 和 ALTER TABLE 语句导致隐式提交并且无法回滚:

The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.

另请参阅 MySQL 文档:https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

已更新:因为您正在使用 CREATE TEMPORARY TABLE 它实际上不应该进行隐式提交但它发生是因为当您执行 ALTER TABLE 时:

CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as ALTER TABLE and CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back, which means that the use of such statements causes transactional atomicity to be violated. For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence.