InnoDB handling of transaction errors

from the Artful MySQL Tips List


To put it mildly, InnoDB transaction error handling does not conform to the SQL standard. See http://dev.mysql.com/doc/refman/5.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 |
+---+---+

DECLARE HANDLER will handle most such errors, 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;
  start transaction;
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND ROLLBACK;
      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
    END;
  commit;
  select * from t;
end ;
go
delimiter ;
call t();                                 -- entire tx is rolled back


Return to the Artful MySQL Tips page