Commit and rollback of MySQL transactions

START TRANSACTION | BEGIN [Work]

COMMIT [Work] [and [No] CHAIN] [[No] RELEASE]

ROLLBACK [Work] [and [No] CHAIN] [[No] RELEASE]

SET autocommit = {0 | 1}

The above is the official syntax for MySQL to create stored procedures.
I want to illustrate here that the MySQL transaction handles the rollback of multiple SQL statements. For example, to start a transaction in a stored procedure, the 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. It is important to note that the collback of transactions cannot be used directly, so that rollback is not possible or unexpected errors may occur.

So what we need is a conditional judgment, like loop, because MySQL is automatically committed by default, so we don't have to worry about conditional exit without commit after rollback.

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

This article is from the "lake and Laughter" blog, please make sure to keep this source http://hashlinux.blog.51cto.com/9647696/1793738

Commit and rollback of MySQL transactions