Mysql存储过程不是原子性，oracle是原子性的

Mysql存储过程（一）——原子性

 

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); -- 语句1

           INSERT INTO testproc VALUES (i_id, i_name); -- 语句2（因为id为PK，此语句将出错）。

END;

 

 

 

 

  在指定了整个存储过程作为一个事务的情况下，最后必须要主动 commit 或者 rollback 以结束该事务，如下的代码，如果缺少语句 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;

 

 

还有一个比较诡异的地方，就是，如果在存储过程中 commit 了，接下来的语句就均变成非事务控制的了，如下代码，在语句 1 处 commit ，接下来语句 2 成功，但是语句 3 和语句 2 的 PK 是一样，整个存储过程应该抛 PK 重复的异常。而执行后发现，语句 2 的结果被正常 commit 到数据库中了，而语句 3 抛出一个异常：

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 到数据库：

 

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