mysql - mysql 中的 SavePoint 问题

Question

我正在尝试使用 MySQL 中的保存点，但似乎出了点问题。

MySQL transaction conundrum

我收到如下所示的错误:

ERROR 1305 (42000): SAVEPOINT sp_prc_work does not exist

无论有没有保存点，我的程序都完全一样。我期望值 '4', 'pqr' 不应该出现在表中，因为整个事务将被回滚。但同时插入了 3 和 4 ID。我明白为什么条目“3”、“pqr”在那里，但我想 ID“4”不应该在那里。

drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;

CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;

insert into test.savepoint_test values ('1', 'abc');
insert into test.savepoint_test values ('2', 'xyz');

select * from test.savepoint_test;

delimiter $$

CREATE PROCEDURE second_fail()
BEGIN
        INSERT  into test.savepoint_test values ('3', 'pqr');
        INSERT  into test.savepoint_test values ('2', 'mnp');
END;

$$

CREATE PROCEDURE prc_work()
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
        SAVEPOINT sp_prc_work;
        INSERT  into test.savepoint_test values ('4', 'pqr');
        INSERT  into test.savepoint_test values ('2', 'mnp');
END;

$$

delimiter ;


call second_fail();

select * from test.savepoint_test;

call prc_work();

select * from test.savepoint_test;

Answer 1

改变这一行

    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
    SAVEPOINT sp_prc_work;

到

    SAVEPOINT sp_prc_work;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;

这应该可以解决问题，您是在告诉 mysql 回滚到一个不存在的保存点

见 http://dev.mysql.com/doc/refman/5.0/en/savepoint.html

直流

我已将您的示例修改为我认为您真正想要的

请注意开始交易

drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;

CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;

delimiter $$

CREATE PROCEDURE second_fail()
BEGIN
        INSERT  into test.savepoint_test values ('3', 'pqr');
        INSERT  into test.savepoint_test values ('4', 'mnp');
END;

$$

CREATE PROCEDURE prc_work()
BEGIN
        SAVEPOINT sp_prc_work;
        BEGIN
                DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
                INSERT  into test.savepoint_test values ('5', 'cat');
                INSERT  into test.savepoint_test values ('2', 'dog');
        END;
        RELEASE SAVEPOINT sp_prc_work;
END;

$$

delimiter ;

START TRANSACTION;

select 'test point 1' as ``;

insert into test.savepoint_test values ('1', 'abc');      
insert into test.savepoint_test values ('2', 'xyz');

select * from test.savepoint_test;

select 'test point 2' as ``;

call second_fail();    

select * from test.savepoint_test;

select 'test point 3'  as ``;

call prc_work();

select * from test.savepoint_test;

select 'test point 4' as ``;

COMMIT;  

直流