mysqldatabasestored-proceduresstored-functionsmysql-error

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

Everywhere I look it seems MySQL stored procedures can do transactions. Yet 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 stored function or trigger.

Solution

  • Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored procedures only.

    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 the SP, use output parameters or use the result set from the last select statement in the SP.