Mix betwenn storage procedures and transactions

[maxime debeauregard]

Mix betwenn storage procedures and transactions
Hi,

I've written a storage procedure. Because this procedure need to much ressources, I've received the error
"mysql : Lost connection to MySQL server during query"

I don't have this error when I execute a querry, because each sql command is auto commited (it means no transaction).

To solve the problem, I would make some intermediates commits, it means
integrate transactions in a stored procedure.

How can I do ? The following code is executable, but don't make intermediates commits.

(note : fn_update is a stored function)


drop procedure if exists my_stored_procedure;
delimiter ;;

CREATE DEFINER = `root`@`localhost` PROCEDURE `my_stored_procedure`()
LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''

BEGIN


START TRANSACTION;
update table_1 set column1= fn_update(column1) ;
commit;


START TRANSACTION;
update table_1 set column2= fn_update(column2) ;
commit;


START TRANSACTION;
update table_1 set column3= fn_update(column3) ;
commit;

end;;


delimiter ;





Thanks for your help.