Why do functions in MySQL not allow transactions to be committed?

Question

I have a function in MySQL that, if it doesn't have a transaction, works normally. When it is converted into a procedure with a transaction it also works. But, when it is a function with a transaction, MySQL returns an error that commits are not allowed in functions or triggers. Is there any reason for this bizarre behavior?

The function who works:

DELIMITER |
CREATE OR REPLACE FUNCTION TesteFunction() RETURNS INT  DETERMINISTIC MODIFIES SQL DATA
BEGIN
    DECLARE Retorno INT;
 
    UPDATE Produto SET CD_Extra = "CD00472-5" WHERE CD_Codigo = 6;
    SELECT ROW_COUNT() INTO Retorno;
 
    RETURN Retorno;
END |

The procedure who works:

DELIMITER |
CREATE OR REPLACE PROCEDURE TesteFunction() DETERMINISTIC MODIFIES SQL DATA
BEGIN
    START TRANSACTION READ WRITE;
    UPDATE Produto SET CD_Extra = "CD00472-5" WHERE CD_Codigo = 6;
    SELECT ROW_COUNT();
    COMMIT;
END |

The function who doesn't work:

DELIMITER |
CREATE OR REPLACE FUNCTION TesteFunction() RETURNS INT DETERMINISTIC MODIFIES SQL DATA
BEGIN
    DECLARE Retorno INT;
 
    START TRANSACTION READ WRITE;
    UPDATE Produto SET CD_Extra = "CD00472-5" WHERE CD_Codigo = 6;
    SELECT ROW_COUNT() INTO Retorno;
    COMMIT;
 
    RETURN Retorno;
END |

Answer 1

If you create a function with transaction control statements, there is nothing preventing a client from running a query like this:

SELECT ... FROM MyTable WHERE TesteFunction() = 12345

This would invoke the function once for each row examined during the query. A given query is atomic, i.e. it cannot be partially committed.

But it would make no sense to COMMIT a transaction during execution of a query. You can't do that.

Whereas you cannot call a procedure in that manner. You can only call a procedure from a CALL statement like this:

CALL TesteProc();

You can't use CALL in a subquery or an expression within another SQL query. It must be a standalone statement. So it can't have the same problem of committing during a query.

You might say, "but what if I don't use the function in a query's WHERE clause?"

You may not, but any other client can do so in the future. So the implementation must not allow such a paradox to occur, and the safest way to do that is to disallow transaction control statements in a stored function.

FWIW, you can't use transaction control within a trigger for the same reason.