Mysql stored procedure (1) -- atomicity

MySQL
Medium, single
Store procedure (SP)
Not an atomic operation,
Oracle
It is atomic. The following stored procedure, even if the statement
2
Failed, statement
1
Will still be
Commit
To the database:

SQL code 1-1
:

Create table testproc (id int (4) primary key, name varchar (100); </p> <p> create procedure test_proc_ins (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100) <br/> BEGIN <br/> insert into testproc VALUES (I _id, I _name ); -- Statement 1 <br/> insert into testproc VALUES (I _id, I _name); -- Statement 2 (this statement will fail because the id is PK ). <Br/> END;

To make the entire stored procedure an atomic operation, specify to start a transaction at the beginning of the stored procedure subject. Statement
2
Failed, statement
1
Will not be
Commit
In the database, the stored procedure throws an exception during the call.

SQL code 1-2
:

Create procedure test_proc_ins (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100) <br/> BEGIN <br/> start transaction; -- the entire stored procedure is specified as a transaction <br/> insert into testproc VALUES (I _id, I _name); -- Statement 1 <br/> insert into testproc VALUES (I _id, I _name ); -- Statement 2 (because the id is PK, this statement will fail ). <Br/> END;

 
When the entire stored procedure is specified as a transaction, you must take the initiative
Commit
Or
Rollback
To end the transaction, the following code, if the statement is missing
1
And will not be
Commit
To the database.

SQL code 1-3:

Create procedure test_proc_ins (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100) <br/> BEGIN <br/> start transaction; -- the entire stored procedure is specified as a transaction <br/> insert into testproc VALUES (I _id, I _name); <br/> insert into testproc VALUES (I _id + 1, I _name ); -- Here id + 1 is used to avoid primary key conflicts <br/> commit; -- Statement 1. Must actively submit <br/> END;

 
Another strange thing is that, in the stored procedure
Commit
The subsequent statements become non-transactional control.
1
Location
Commit
, The following statement
2
Succeeded, but the statement
3
And statement
2
Of
PK
Yes, the entire stored procedure should be thrown
PK
Duplicate exception. After execution, the statement
2
The result is normal.
Commit
To the database, and the statement
3
Throw an exception:

SQL code 1-4:

Create procedure test_proc_ins (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100), <br/> OUT o_ret INT) <br/> BEGIN <br/> start transaction; <br/> insert into testproc VALUES (I _id, I _name); <br/> insert into testproc VALUES (I _id + 1, I _name); <br/> commit; -- Statement 1 <br/> insert into testproc VALUES (I _id + 2, I _name ); -- Statement 2 <br/> insert into testproc VALUES (I _id + 2, I _name); -- Statement 3 <br/> set o_ret = 1; <br/> END;

 
If you want
SQL code 1-4
Statements in
2
And statement
3
As a whole, you need to re-enable a transaction, the following code, so that when calling, the statement
1
Normal
Commit
, Statement
3
If an error occurs, the stored procedure throws an exception instead of the statement.
2
Execution result
Commit
To database:

SQL code 1-5:

Create procedure test_proc_ins (<br/> IN I _id INT, <br/> IN I _name VARCHAR (100), <br/> OUT o_ret INT) <br/> BEGIN <br/> start transaction; <br/> insert into testproc VALUES (I _id, I _name); <br/> insert into testproc VALUES (I _id + 1, I _name); <br/> commit; -- Statement 1. The transaction has been committed. <br/> set o_ret = 1; <br/> start transaction; -- start another transaction <br/> insert into testproc VALUES (I _id + 2, I _name); -- Statement 2 <br/> insert into testproc VALUES (I _id + 2, I _name ); -- Statement 3 <br/> set o_ret = 2; <br/> commit; -- if the data is normal, commit needs to be performed again to END the transaction <br/> END;