mysql savepoint问题

首先确认当前客户端的事务提交方式

select @@autocommit

1表示自动提交, 0标识手动提交

对于1(自动提交)的情况, 要手动创建事务, 并在事务中记录savepoint和回滚savepoint, 举个栗子:

begin

savepoint p1

insert into tmp_sleest values(1)

rollback to p1

#Rollback  Or Commit to Complete This Transaction Prevent From TABLE_LOCKING.

这样做是允许的,也会正常回滚到p1的点上, 但是如果把savepoint p1放到begin前, 或者begin前还有个p2, 此时在事务中rollback p1或者p2都是不可识别的, 即doesn't exists.

对于0(手动提交)的情况, 客户端等于默认加了begin, 直到Commit或者Rollback才完成一个事务, 此时上述代码不用写begin 也是可以运行.

为你搜到的，希望对你有用

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`handlerdemo`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`()

BEGIN

DECLARE X2 INT

DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2= 1

DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2= 3

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4

START TRANSACTION

SAVEPOINT savepoint_1

BEGIN

INSERT INTO eugene VALUES (1)

INSERT INTO eugene VALUES (2)

SELECT X2

end

ROLLBACK TO SAVEPOINT savepoint_1

iNSERT INTO eugene VALUES (3)

END$$

DELIMITER

#测试表

CREATE TABLE `eugene` (

t1` int(4) default NULL

)

---