InnoDB handling of transaction errors

from the Artful MySQL Tips List


InnoDB transaction error handling doesn't conform entirely to the SQL standard. See http://dev.mysql.com/doc/refman/8.0/en/innodb-error-handling.html. For example:
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 |
+---+---+
A stored procedure permits declaration of an error handler and Rollback/Commit flow control, for example ...
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
... Or ...
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
.. or the above plus logic to re-raise error signals nuked by an exit handler...
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


Return to the Artful MySQL Tips page