重复键错误不会取消/回滚mysql事务 [英] duplicate key error does not cancel/rollback mysql transaction

问题描述

在mysql innodb事务中，我期望重复的键错误会导致回滚.不会，相反，它只是引发一个错误并继续执行下一个命令.到达COMMIT命令后，如果没有引起重复密钥的命令，则将提交事务.

When in a mysql innodb transaction, I would expect a duplicate key error to cause a rollback. It doesn't, instead it simply throws an error and continues on to the next command. Once the COMMIT command is reached, the transaction will be committed, sans the duplicate key causing command.

这是预期的行为吗?如果是这样，当发生此类错误时，如何进行设置以使事务回滚而不是提交?

Is this the expected behaviour? If so, how would one go about setting it up so that the transaction is rolled back instead of committed when such an error occurs?

测试环境:

CREATE TABLE `test` (  
  `id` int(11) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

BEGIN;
     INSERT INTO test VALUES (5);
     INSERT INTO test VALUES (5);
COMMIT;

预期结果:表test为空

实际结果:表test包含一条值为5的记录

actual result: table test contains one record with value 5

推荐答案

如果发生错误，MySql(和其他SQL引擎AFAIK)不会自动回滚事务.

MySql (and other sql engine AFAIK) does not automatically rollback a transaction if an error occurs.

您必须声明一个错误处理程序，该错误处理程序将回滚事务:

You have to declare an error handler that will Rollback the transaction:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN 
  ROLLBACK; 
  CALL ERROR_ROLLBACK_OCCURRED; 
END;

这篇关于重复键错误不会取消/回滚mysql事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！