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

Question

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.

Answer 1

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.

Answer 2

We cannot use transaction in a function while we can in a procedure.

So, I got the same error below:

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

Because I used transaction with START TRANSACTION; and COMMIT; in a function as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  START TRANSACTION;
    -- Statements
  COMMIT;
END$$ 

DELIMITER ;

But, I could use transaction with START TRANSACTION; and COMMIT; in a procedure without error as shown below:

DELIMITER $$

CREATE PROCEDURE my_proc()
BEGIN
  START TRANSACTION;
    -- Statements
  COMMIT;
END$$

DELIMITER ;

In addition, using BEGIN; instead of START TRANSACTION; in a procedure gets the error below:

DELIMITER $$

CREATE PROCEDURE my_proc()
BEGIN
  -- START TRANSACTION;
  BEGIN; -- Here
    -- Statements
  COMMIT;
END$$

DELIMITER ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';