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

来源：互联网发布：网络视听许可证编辑：程序博客网时间：2024/05/01 02:28

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))BEGINstart 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))BEGINstart 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:

如果要想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)BEGINstart 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;