Mysql implements transaction commit and rollback instances

The official syntax for creating a stored procedure in mysql is:

Copy codeThe Code is as follows:
Start transaction | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
Set autocommit = {0 | 1}

Here I want to explain the rollback of mysql transaction processing multiple SQL statements. For example, if you start a transaction in a stored procedure, this transaction inserts data into the three tables at the same time. After each inserted Table, You need to determine whether the operation is successful. If the operation is unsuccessful, You need to roll back, the last table determines whether it is successfully inserted and then commit. Here, you must note that you cannot directly use the collback of the transaction. In this way, rollback cannot be implemented or unexpected errors may occur.

So what we need is a condition judgment, such as loop, because MySql will automatically submit by default, so we don't have to worry about exit without commit after rollback.

The specific mysql statement is as follows:

Copy codeThe Code is 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