MySQL存储过程 事务transaction

MySQL 中，单个 Store Procedure(SP) 不是原子操作，而 Oracle 则是原子的。如下的存储过程，即使语句2 失败，语句 1 仍然会被 commit 到数据库中：

[sql]   view plain  copy

1. create table testproc(id int(4) primary key, name varchar(100));  
2.    
3. CREATE PROCEDURE test_proc_ins(  
4. IN i_id INT,  
5. IN i_name VARCHAR(100)  
6. )  
7. BEGIN  
8.            INSERT INTO testproc VALUES (i_id, i_name);  -- 语句1  
9.            INSERT INTO testproc VALUES (i_id, i_name);  -- 语句2（因为id为PK，此语句将出错）。  
10. END;  

要使整个存储过程成为一个原子操作的办法是：在存储过程主体开始部分，指定开始一个事务。语句 2 失败，语句 1 不会被 commit 到数据库中，存储过程将会在调用时抛出一个异常。

[sql]   view plain  copy

1. CREATE PROCEDURE test_proc_ins(  
2. IN i_id INT,  
3. IN i_name VARCHAR(100)  
4. )  
5. BEGIN  
6. start transaction; --整个存储过程指定为一个事务  
7.            INSERT INTO testproc VALUES (i_id, i_name);  
8.            INSERT INTO testproc VALUES (i_id+1, i_name); -- 这里把id+1，避免主键冲突  
9. commit; -- 语句1。必须主动提交  
10. END;  

[sql]   view plain  copy

1. CREATE PROCEDURE test_proc_ins(  
2. IN i_id INT,  
3. IN i_name VARCHAR(100),  
4. OUT o_ret INT)  
5. BEGIN  
6. start transaction;  
7.            INSERT INTO testproc VALUES (i_id, i_name);  
8.            INSERT INTO testproc VALUES (i_id+1,i_name);  
9.            commit; -- 语句1，提交后，事务已结束  
10.            set o_ret = 1;  
11.            start transaction; -- 再启一个事务  
12.            INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句2  
13.            INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句3  
14.            set o_ret = 2;  
15.            commit; -- 数据正常的情况下，需要再次commit以结束事务  
16. END;  

mysql的回滚事物的操作

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

[sql]   view plain  copy

1. DROP PROCEDURE IF EXISTS  test_sp1   
2. CREATE PROCEDURE test_sp1( )    
3.     BEGIN    
4.     DECLARE t_error INTEGER DEFAULT 0;    
5.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;    
6.     
7.         START TRANSACTION;    
8.             INSERT INTO test VALUES(NULL, 'test sql 001');       
9.             INSERT INTO test VALUES('1', 'test sql 002');       
10.     
11.         IF t_error = 1 THEN    
12.             ROLLBACK;    
13.         ELSE    
14.             COMMIT;    
15.         END IF;    
16.    select t_error;   //返回标识位的结果集；  
17. END  

mysql事物处理实例

MYSQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit 事务确认
2.直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
    set autocommit = 0 禁止自动提交
    set autocommit = 1 开启自动提交
    来实现事务的处理。
但要注意当用set autocommit = 0 的时候，你以后所有的sql都将作为事务处理，直到你用commit确认或 rollback结束，注意当你结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!
MYSQL只有 INNODB和BDB类型的数据表才支持事务处理，其他的类型是不支持的!