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 |
![]() |