11

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.

bobobobo
  • 64,917
  • 62
  • 258
  • 363

1 Answers1

17

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.

bobobobo
  • 64,917
  • 62
  • 258
  • 363
  • 13
    Also worth noting is that if you call the procedure from within a trigger, you will still be rewarded with an '_Explicit or implicit commit is not allowed in stored function or trigger_' error. Frustrating. – aroth Jan 24 '14 at 06:27
  • 4
    hey, this is a bit old, but I'm running into this problem right now. Is there any way around it? – craigmiller160 Nov 05 '15 at 20:18