I am building a "bank" as an assignment for a database course I am taking. I have created a stored function which takes a few IN variables, such as account ID, customer ID and a PIN number, and does checks on this to see if the submitted data is valid. If the data is valid, the procedure updates the account balance to represent a monetary transaction. It then "returns" whether or not the submitted data was valid. Here is the code for the procedure:

DELIMITER //

CREATE PROCEDURE retrieveMoney (

IN holder INT,

IN pin VARCHAR(4),

IN account INT,

IN amount FLOAT,

OUT success INT

)

BEGIN

START TRANSACTION;

SELECT COUNT(id) INTO success FROM account_holder WHERE id=holder AND pin=pin;

IF success IS NOT NULL THEN

IF (SELECT balance-amount FROM account WHERE id=account) >= 0 THEN

UPDATE account SET balance = balance-amount WHERE id=account;

CALL logTransaction(account,NULL,amount);

COMMIT;

ELSE ROLLBACK;

END IF;

ELSE ROLLBACK;

END IF;

END//

DELIMITER ;

I wanted to make it easier to view the output of the procedure, and as I'm not allowed to use transactions within a function I chose to write a wrapper function, as follows:

DELIMITER //

CREATE FUNCTION retrieveMoney (

holder INT,

pin VARCHAR(4),

account INT,

amount FLOAT

)

RETURNS INT

BEGIN

CALL retrieveMoney(holder,pin,account,amount,@success);

RETURN @success;

END//

DELIMITER ;

Unfortunately this does not work, and I still get this following error:

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

Is this because I call the procedure, which contains transactions, inside the function?

解决方案

There are a number of statements that cause an implicit commit, and none of these can be used inside a stored function or a trigger, or in a stored procedure that is called from a stored function or trigger, because that is not really any different in its net effect.

A moment's reflection explains the reason for this: stored functions (and triggers) execute while a query is running. They always, without exception, begin executing after the query starts, and finish executing before the query finishes. They can also run multiple times during the execution of a single query, particularly when the query involves multiple rows.

In that light, it would not make sense if it were possible to COMMIT a transaction while a single query is running... and that's what START TRANSACTION does, if a transaction is running -- it implicitly commits the current transaction, and starts a new one.

This is fine in a stored procedure, as long as you're not calling it in the middle of another query (via a stored function or trigger, which is the only way to invoke a procedure in the middle of another query) but doing what you are doing here is not supported... even if there's not a transaction running, it still isn't possible to start a transaction in the middle of a running query.