如何正确回滚事务 [英] How do I correctly rollback a transaction

问题描述

我有一个将应用于2个表的事务。表2有字段

'图像'

是唯一的。当第一次尝试执行以下代码时，它正确执行但在尝试再次重新执行时（显示回滚效果），table2中发生重复错误，并且事务已正确应用于table1并已提交table1承诺记录没有回滚。



我尝试过：



这是我的代码：

I have a transaction that will be applied to 2 tables. Table 2 has field
'image'
that is unique. when trying to execute the following code in the first time, it is executed correctly but when trying to Re-execute it again (to show effect of rollback), a duplicate error occurred in table2 and the transaction was applied correctly to table1 and committed so the table1 committed record was not rolled back.

What I have tried:

Here is my code:

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure ()
BEGIN
START TRANSACTION;
INSERT INTO `mschema`.`table1`
(`maxbudget`,
`blocked`,
`d_percentage`,
`max discount`)
VALUES
('2250',
'0',
'.9',
'.99');
if  (@@error_count = 0 ) then    
    INSERT INTO `mschema`.`table2`
     (`name`,`image`,`date`,`fKey_id`)
     values
         ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
      if (@@error_count = 0 ) then 
          commit;
      else 
          rollback;
      end if;
else rollback;
end if;
commit;
END;
$$
DELIMITER ;

推荐答案

创建 PROCEDURE mprocedure（）
BEGIN
START TRANSACTION ;
INSERT INTO `mschema` .table1`
（`maxbudget`，
`closed`，
`d_percentage`，
`max discount`）
VALUES
（< span class =code-string>' 2250'，
' 0'，
' 。9'，
' 。99'）;
if （ @@ error_count = 0 ）然后
INSERT INTO `mschema` .table2`
（`name`，`image`，`date`，`fKey_id`）
values
（' jhon'，' jfdd'，' 2018-01-01 00 ：00：00'，LAST_INSERT_ID（））;
if （ @@ error_count = 0 ）然后
commit ;
else
rollback ;
end if ;
else rollback ;
end if ;
commit ;
END ;

CREATE PROCEDURE mprocedure () BEGIN START TRANSACTION; INSERT INTO `mschema`.`table1` (`maxbudget`, `blocked`, `d_percentage`, `max discount`) VALUES ('2250', '0', '.9', '.99'); if (@@error_count = 0 ) then INSERT INTO `mschema`.`table2` (`name`,`image`,`date`,`fKey_id`) values ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID()); if (@@error_count = 0 ) then commit; else rollback; end if; else rollback; end if; commit; END;

DELIMITER;

DELIMITER ;

尝试声明退出处理程序。像下面的东西。对不起打字错误，我手边还没有MySql

Try declaring an exit handler. Something like below. Sorry for any typos, I don't have MySql at hand at the moment

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER

这篇关于如何正确回滚事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！