Possible Duplicate:
Are nested transactions allowed in MySQL?
I have s stored procedure that uses transactions and inside a transaction calls another procedure that also uses transactions and updates a table. The second procedure is called inside a loop and every call updates a single row. This second procedure also creates a temporary table. Engine is InnoDB for permanent tables and MyISAM for temporary tables. MySQL version is 5.5.16.
What I want is to rollback all the updates made by the second procedure if an error happens.
Is this possible ? I know that an DDL statement and start transaction; will issue a commit but is there a way around it?
The code looks something like this: (rollback obviously doesn't work)
delimiter $$
drop procedure if exists `proc1`$$
create procedure `proc1`(
...#some variables
)
modifies sql data
begin
declare error int default 0;
declare continue handler for sqlexception
begin
set error=1;
end;
drop temporary table if exists table1;
create temporary table table1 (
id int unsigned,
col1 decimal(12,6) default 0,
col2 decimal (12,6) default 0,
col3 decimal (12,6),
primary key (id)) engine=MyISAM;
START TRANSACTION;
begin
declare id_1 int unsigned;
declare v1 decimal(12,6) default 0;
declare v2 decimal(12,6) default 0;
declare v3 decimal(12,6) default 0;
declare done int default 0;
declare cur cursor for select id, col1, col2 from table1;
declare continue handler for not found set done=1;
begin
open cur;
wh: while done=0 do
fetch cur into id, v1, v2;
if done=1 then
leave wh;
end if;
set v3=v1+v2 ;
update table1 set col3=v3 where id =id_1;
CALL proc2(id_1, v1, v2, v3);
end while wh;
close cur;
set done=0;
end;
end;
if error=0 then
commit;
set status=1;
else
rollback;
set status=-1;
end if;
end$$
