如何在mysql下实现事务的提交与回滚

如何在mysql下实现事务的提交与回滚

首先我们建一张tran_test表

CREATE TABLE tran_test(  
	f1 VARCHAR(10) NOT NULL,  
	f2 INT(1) DEFAULT NULL,  
	PRIMARY KEY (f1)  
)ENGINE=INNODB CHARSET=utf8  

我想对tran_test插入两条数据，但是为了防止插入中报错，因此我要把插入语句控制在一个事务内。

这时候，如果你查一下有些人的文章，许多时候会给出你这么一条答案。

START TRANSACTION;    
	INSERT INTO tran_test VALUES('A',1);  
	INSERT INTO tran_test VALUES('B',2);  
ROLLBACK;

或

START TRANSACTION;    
	INSERT INTO tran_test VALUES('A',1);  
	INSERT INTO tran_test VALUES('B',2);  
COMMIT; 

看上去很简单的sql语句，并且这两句也确实能实现提交或回滚。

然而这真的能达到我们的目的吗？答案是否定的。

比如第一段，它是将你在事务中的sql语句无论对错全部进行ROLLBACK。这样绝对的回滚使得你的sql没有任何意义了。

因此我们想要真正的控制好事务，我的思路是对要执行的sql进行异常检测。如果sql没有出现异常，COMMIT，如果捕获到了异常，则ROLLBACK。

这时候，我们就需要建一个存储过程来捕获异常。执行成功时进行COMMIT，sql执行失败时则进行ROLLBACK。

两种思路可以达到我想要的效果。

第一种是对我们要执行的sql进行异常捕获，我们再定义一个变量t_error，当捕获到异常的时候，让t_error=1。再对t_error进行条件判断，如果t_error=1则进行ROLLBACK，否则进行COMMIT。

DROP PROCEDURE IF EXISTS t_test;  
DELIMITER //  
CREATE PROCEDURE t_test()  
  BEGIN  
    DECLARE t_error INTEGER;  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;  
    START TRANSACTION;  
         INSERT INTO tran_test VALUES('A',1);  
     INSERT INTO tran_test VALUES('B',2);  
         IF t_error = 1 THEN  
             ROLLBACK;  
         ELSE  
             COMMIT;  
         END IF;  
END//  
CALL t_test();

另一只则是第一种的简化，即捕获到异常直接进行ROLLBACK，如果没捕获到异常，直接COMMIT

DROP PROCEDURE  IF EXISTS t_test;  
DELIMITER //  
CREATE PROCEDURE t_test()  
BEGIN  
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;  
START TRANSACTION;  
INSERT INTO tran_test VALUES('A',1);  
INSERT INTO tran_test VALUES('B',2);  
COMMIT;  
END//  
CALL t_test()

这样，这两个insert语句便真正的被控制在了一个事务内了。

---

事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

	BEGIN 开始一个事务
	ROLLBACK 事务回滚
	COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

	SET AUTOCOMMIT=0 禁止自动提交
	SET AUTOCOMMIT=1 开启自动提交