MySQL存储过程 事务transaction


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

create table testproc(id int(4) primary key, name varchar(100));
 
CREATE PROCEDURE test_proc_ins(
IN i_id INT,
IN i_name VARCHAR(100)
)
BEGIN
           INSERT INTO testproc VALUES (i_id, i_name);  -- 语句1
           INSERT INTO testproc VALUES (i_id, i_name);  -- 语句2(因为id为PK,此语句将出错)。
END;

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

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

CREATE PROCEDURE test_proc_ins(
IN i_id INT,
IN i_name VARCHAR(100),
OUT o_ret INT)
BEGIN
start transaction;
           INSERT INTO testproc VALUES (i_id, i_name);
           INSERT INTO testproc VALUES (i_id+1,i_name);
           commit; -- 语句1,提交后,事务已结束
           set o_ret = 1;
           start transaction; -- 再启一个事务
           INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句2
           INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句3
           set o_ret = 2;
           commit; -- 数据正常的情况下,需要再次commit以结束事务
END;


MySQL的回滚事物的操作

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

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 INTO test VALUES('1', 'test sql 002');     
  
        IF t_error = 1 THEN  
            ROLLBACK;  
        ELSE  
            COMMIT;  
        END IF;  
   select t_error;   //返回标识位的结果集;
END




已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页