How to wrap a mysql stored function in a transaction -

drop function if exists my_func; delimiter || create function my_func(arg_id int) returns varchar(200) begin     declare local_id int;     declare local_name varchar(200);     start transaction; /* <-- allowed */         select      id, name                local_id, local_name                table               id = arg_id             update;         select my_other_function(local_name) local_name;         update table set name = local_name;     commit; /* <-- not allowed         , yields "explicit or         implicit commit not         allowed..." error */     return local_name; end|| delimiter ; 

are return statements implicit commits?

how supposed ensure function runs as a transaction?

wrap in transaction?

start transaction;     select my_func(33); /* <-- no transaction statements inside function */ commit; 

start transaction inside function , commit?

select my_func(33); /* <-- contains start transaction line */ commit; 

also i've researched issue , found info said along lines of: "statements in stored functions not guaranteed executed in declared order can make binary logging (and implicitly replication) inconsistent".

i had use before , worked me:

option 1

set autocommit = {0 | 1} 

after have completed want do, can re-enable or call explicit commit.

option 2 disable autocommit mode implicitly single series of statements, use start transaction statement:

start transaction; select @a:=sum(salary) table1 type=1; update table2 set summary=@a type=1; commit; 

i got mysql website - mysql

---

for situation, think setting autocommit false before function trick (starting transaction same thing if @ description of above). way, during transaction, function, doesn't autcommit.