MySQL存储过程 事务transaction

转自：https://blog.csdn.net/xinyuan_java/article/details/50005519

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

 

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 到数据库中，存储过程将会在调用时抛出一个异常。

 

 

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;

 

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)。

 

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