Mysql 中,单个 Store Procedure(SP) 不是原子操作,而 oracle 则是原子的。如下的存储过程,即使语句 2 失败,语句 1 仍然会被 commit 到数据库中:
Sql code 1-1 :
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
到数据库中,存储过程将会在调用时抛出一个异常。
Sql code 1-2
:
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); -- 语句1 INSERT INTO testproc VALUES (i_id, i_name); -- 语句2(因为id为PK,此语句将出错)。 END;
在指定了整个存储过程作为一个事务的情况下,最后必须要主动
commit 或者 rollback
以结束该事务,如下的代码,如果缺少语句 1
,最终也是不会被 commit
到数据库中的。
Sql code 1-3:
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;
还有一个比较诡异的地方,就是,如果在存储过程中 commit
了,接下来的语句就均变成非事务控制的了,如下代码,在语句
1 处 commit
,接下来语句 2 成功,但是语句
3 和语句
2 的 PK
是一样,整个存储过程应该抛 PK 重复的异常。而执行后发现,语句
2 的结果被正常
commit 到数据库中了,而语句 3
抛出一个异常:
Sql code 1-4:
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 INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句2 INSERT INTO testproc VALUES (i_id+2,i_name); -- 语句3 set o_ret = 1; END;
如果要想 Sql code 1-4
中的语句 2 和语句
3 作为一个整体,则需要再重新启用一个事务,如下代码,这样,调用的时候,语句
1 可以正常
commit ,语句 3
出错,存储过程会抛异常而不会把语句 2
的执行结果 commit
到数据库:
Sql code 1-5:
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;