MySQL Forums
Forum List  »  InnoDB

How come transaction didn't work?
Posted by: Pavel Bazanov
Date: November 11, 2008 08:53AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
How come transaction didn't work?
2683
November 11, 2008 08:53AM
1634
November 11, 2008 09:37PM
1656
November 12, 2008 04:48AM
1657
November 13, 2008 08:16PM
1591
November 14, 2008 04:26AM
1584
November 13, 2008 12:17PM
1551
November 15, 2008 12:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.