Mysql implementation Transaction Commit and rollback instance _mysql

The official syntax for MySQL to create a stored procedure is:

Copy Code code as follows:

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [and [No] CHAIN] [[No] release]
ROLLBACK [WORK] [and [No] CHAIN] [[No] release]
SET autocommit = {0 | 1}

I'm here to describe the MySQL transaction that handles the rollback of multiple SQL statements. For example, to start a transaction in a stored procedure, this transaction inserts data into three tables at the same time, each table needs to determine whether the operation is successful, if not successful, you need to roll back, the last table to determine the success of the commit after the insert. Note here that you cannot use the Collback of a transaction directly, so that you cannot rollback it, or you may have unexpected errors.

So what we need is a conditional judgment, such as loop, because MySQL is automatically submitted by default, so we don't have to worry about rollback after the condition exits without a commit.

The specific MySQL statement is as follows:

Copy Code code as follows:

Begin
Loop_lable:loop
Start transaction;
Insert INTO table1 (f_user_id) values (user_id);
If Row_count () < 1 Then
Set @ret =-1;
Rollback
Leave Loop_label;
End If;
Insert into table2 (f_user_id) values (user_id);
If Row_count () < 1 Then
Set @ret =-1;
Rollback
Leave Loop_label;
End If;
Insert into Table3 (f_user_id) values (user_id);
If Row_count () < 1 Then
Set @ret =-1;
Rollback
Leave Loop_label;
Else
Set @ret = 0;
Commit
Leave Loop_label;
End If;
End Loop;
Select @ret;
End