drop table if exists t;
create table t(i int primary key, j int not null) engine=innodb;
start transaction;
  insert into t values(1,1),(2,2);
  select * from t;
  update t set j=j+1;
  select * from t;
  insert into t values(2,null); -- illegal null does not rollback transaction
commit;
select * from t;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 3 |
+---+---+

drop procedure if exists t;
delimiter go
create procedure t()
begin
  drop table if exists u,t;
  create table t(i int primary key, d datetime not null) engine=innodb;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK;
  start transaction;
    insert into t values(1,now()),(2,now());
    update t set i=i+1 order by i desc;
    insert into t values(2,null);       -- illegal null
  commit;
  select * from t;
end ;
go
delimiter ;
call t();                               -- entire tx is rolled back

delimiter go;
create procedure p(...)
begin
  declare _rollback bool default 0;
  declare continue handler for sqlexception set _rollback=1;
  start transaction;
    insert ...
    select ...
    update t set ...
  if _rollback then
    rollback;
  else
    commit;
end;
go

create procedure p(...)
begin
  declare _err int default 0;
  declare exit handler for sqlexception
    begin
      rollback;  -- rollback all changes in transaction
      resignal;  -- re-raise sql exception signal to caller
      set _err=1;
    end;
  start transaction;
    insert into ...
    if _err then
      signal sqlstate '45000' 
      set message_text = '...';
    end if;
    insert into ...  -- will not execute on error
  commit;            -- will not execute on error
end;

Last updated 5 Sep 2019