转 mysql 事务在存储过程中的应用

很多资料中，对于事务的处理都很简单，抓捕SQL错误，仅仅是一个 @@error_count 吗？ 肯定不是。@@error_count 只记录上一次SQL操作的结果，对于存储过程中多条语句肯定是不行的。

那么，怎么才能保证在存储过程中正确的回滚呢？让我们先来看个例子：

view plaincopy to clipboardprint?
CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,  
    `content` varchar(100), PRIMARY KEY (`id`) );  
 
DELIMITER $$  
DROP PROCEDURE IF EXISTS  test_sp1 $$  
CREATE PROCEDURE test_sp1( )  
    BEGIN 
        START TRANSACTION;  
            INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */  
            INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */   
 
        IF @@error_count = 0 THEN 
            COMMIT;  
        ELSE 
            ROLLBACK;  
        END IF;    
 
    END$$  
DELIMITER ; 

CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,
 `content` varchar(100), PRIMARY KEY (`id`) );

DELIMITER $$
DROP PROCEDURE IF EXISTS  test_sp1 $$
CREATE PROCEDURE test_sp1( )
    BEGIN
        START TRANSACTION;
   INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */
   INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */

        IF @@error_count = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF; 

    END$$
DELIMITER ;
我们在MySQL客户端执行 CALL test_sp1(); 之后会发现，存储过程中第一条insert成功的执行了，而第二条执行没有执行，但是注意！ 这里发生了错误，而作为判断的 @@error_count 没有反应, ROLLBACK 不能回滚！

在我上一篇文章 《执行一半的存储过程 – MySQL邯郸学步》中存储过程执行了一半， 而很不幸的，这里的 test_sp1() 也执行了一般，就好比我们PHP、JAVA、C++程序遇到错误一样，抛出error, 然后停止执行。

之前使用PDO，在处理事务时，使用SQLException捕获SQL错误，然后处理； 按照这个推论，我们必须在MySQL存储过程中捕获SQL错误，最后判断是回滚(ROLLBACK)还是提交(COMMIT)。 所以存储过程可以改为：

view plaincopy to clipboardprint?
DELIMITER $$  
DROP PROCEDURE IF EXISTS  test_sp1 $$  
CREATE PROCEDURE test_sp1( )  
    BEGIN 
    DECLARE t_error INTEGER DEFAULT 0;  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  
 
        START TRANSACTION;  
            INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */  
            INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */   
 
        F txn_error = 1 THEN 
            ROLLBACK;  
        ELSE 
            COMMIT;  
        END IF;  
 
    END$$  
DELIMITER ; 

DELIMITER $$
DROP PROCEDURE IF EXISTS  test_sp1 $$
CREATE PROCEDURE test_sp1( )
    BEGIN
 DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;

  START TRANSACTION;
   INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */
   INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */

        F txn_error = 1 THEN
   ROLLBACK;
  ELSE
   COMMIT;
  END IF;

    END$$
DELIMITER ;
第二个例子中，我们为test_sp1() 定义了一个 SQLEXCEPTION 参数 t_error， 在遇到SQL错误时，继续执行(CONTINUE); 如果执行状态没有错误，则提交，反之回滚！

如果存储过程A包含B， 那么最好只在A中定义事务； 记住，保证事务的原子性。

下面是我摘抄的，值得挤公车时慢慢品味：

1，保持事务短小
2，尽量避免事务中rollback
3，尽量避免savepoint
4，默认情况下，依赖于悲观锁
5，为吞吐量要求苛刻的事务考虑乐观锁
6，显示声明打开事务
7，锁的行越少越好，锁的时间越短越好

转自：http://mifunny.info/simple-transaction-in-procedure-289.html