How come transaction didn't work?
Hello,
We experienced a very dangerous problem when used transaction in a stored procedure. We couldn't reproduce it on demand, but it happened sometimes, during real work, in, maybe, 1% of all cases.
Here is the stored procedure we had the problem with. Yes, I know, it's a very bad design, but it's what our previous programmer left to us:
------------------------------------------------------------------
CREATE PROCEDURE `DebitCustomer`(IN CustomerID_ INTEGER(11), IN Summ_ DOUBLE, IN Curr_ varCHAR(4), IN Rate_ DOUBLE, IN Source_ varCHAR(30), IN DebitDate_ DATETIME)
BEGIN
START TRANSACTION;
INSERT INTO custdebits SET
CustomerID = CustomerID_,
Summ = Summ_,
Curr = Curr_,
Rate = Rate_,
Source = Source_,
DebitDate = DebitDate_;
UPDATE customers SET Balans = ROUND(Balans + Summ_ / Rate_, 2) WHERE ID = CustomerID_;
COMMIT;
END
------------------------------------------------------------------
So, this procedure had to add a debit record in custdebits table and then update (increase) customer balance value in customers table.
But sometimes (very rare) we noticed that debit record was created and the balance didn't change. It's very serious. How can it be?
Is there a mistake in the stored procedure above, that could lead to the bug?
Or is it a problem of mysql engine?