mysql中的SavePoint问题
mysql中的SavePoint问题
提问于 2010-12-28 21:08:15
我正在尝试使用MySQL中的保存点，似乎有些地方出了问题。
我得到了一个错误，如下所示：
错误1305 (42000)：保存点sp_prc_work不存在
不管有没有保存点，我的程序的工作原理完全一样。我所期望的是值'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;回答 1
Stack Overflow用户
回答已采纳
发布于 2011-01-03 17:00:23
更改这一行
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
数据中心
我已经把你的例子改写成了你真正想要的
请注意begin交易
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; 数据中心
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4551628
复制相关文章
相似问题
腾讯云开发者
