Error code: 1422. Explicit or implicit commit is not allowed in the stored function or trigger

Question

Wherever I look, it seems that MySQL stored procedures can perform transactions. But when I declare my stored function

create function test( a int ) returns int MODIFIES SQL DATA BEGIN START TRANSACTION ; update t set col='some value' where id=a ; COMMIT ; return 0 ; END // 

I get

Error code: 1422. Explicit or implicit commit is not allowed in the stored function or trigger.

Answer 1

In fact, you are not allowed transactions inside stored functions. Transactions allowed inside stored procedures .

 create procedure test( a int ) MODIFIES SQL DATA BEGIN START TRANSACTION ; update t set col='some value' where id=a ; COMMIT ; END // 

To return values ​​from SP, use the output parameters or use the result set from the last select statement in SP.