Description:
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
------------------------------------------------------------------
Tables DDL:
CREATE TABLE `customers` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) DEFAULT NULL,
`LastOrderN` int(10) DEFAULT '0',
`Telephone` varchar(50) DEFAULT NULL,
`Email` varchar(250) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL,
`Fax` varchar(50) DEFAULT '',
`Balans` double(15,5) DEFAULT '0.00000',
`DirhamRate` double(15,5) DEFAULT '3.66000',
`FinRate` double(15,5) DEFAULT '3.64000',
`LastInvoiceN` int(10) DEFAULT '0',
`InvoicesDirectory` varchar(500) NOT NULL DEFAULT '',
`DoSendBalance` tinyint(4) DEFAULT '0',
`LastBalanceSent` datetime DEFAULT '2001-01-01 00:00:00',
`CustomerLocked` tinyint(1) DEFAULT '0',
`AdditionalInfo` varchar(2000) DEFAULT NULL,
`InvoicesEmail` varchar(250) DEFAULT '',
`DoAutoNavar` tinyint(1) DEFAULT '1',
`AutoNavarPercent` double(15,3) DEFAULT '0.950',
`TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CustomerLevelID` int(11) DEFAULT '0',
`AutoManagePercents` tinyint(1) DEFAULT '0',
`LastTimePercentsRecalculated` date DEFAULT '2001-01-01',
`DaysBetweenPercentsRecalculation` int(11) DEFAULT '90',
`MultipleReportsInfo` varchar(2000) DEFAULT NULL,
`SignalOnOrder` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;
CREATE TABLE `custdebits` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CustomerID` int(10) DEFAULT '0',
`Summ` double(15,5) DEFAULT '0.00000',
`Curr` varchar(4) DEFAULT NULL,
`Rate` double(15,5) DEFAULT '1.00000',
`Source` varchar(30) DEFAULT NULL,
`DebitDate` datetime DEFAULT NULL,
`Checked` bit(1) DEFAULT '\0',
`TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2425 DEFAULT CHARSET=utf8;
-------------------------------------------------------------------------------
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?
How to repeat:
CREATE TABLE `customers` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) DEFAULT NULL,
`LastOrderN` int(10) DEFAULT '0',
`Telephone` varchar(50) DEFAULT NULL,
`Email` varchar(250) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL,
`Fax` varchar(50) DEFAULT '',
`Balans` double(15,5) DEFAULT '0.00000',
`DirhamRate` double(15,5) DEFAULT '3.66000',
`FinRate` double(15,5) DEFAULT '3.64000',
`LastInvoiceN` int(10) DEFAULT '0',
`InvoicesDirectory` varchar(500) NOT NULL DEFAULT '',
`DoSendBalance` tinyint(4) DEFAULT '0',
`LastBalanceSent` datetime DEFAULT '2001-01-01 00:00:00',
`CustomerLocked` tinyint(1) DEFAULT '0',
`AdditionalInfo` varchar(2000) DEFAULT NULL,
`InvoicesEmail` varchar(250) DEFAULT '',
`DoAutoNavar` tinyint(1) DEFAULT '1',
`AutoNavarPercent` double(15,3) DEFAULT '0.950',
`TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CustomerLevelID` int(11) DEFAULT '0',
`AutoManagePercents` tinyint(1) DEFAULT '0',
`LastTimePercentsRecalculated` date DEFAULT '2001-01-01',
`DaysBetweenPercentsRecalculation` int(11) DEFAULT '90',
`MultipleReportsInfo` varchar(2000) DEFAULT NULL,
`SignalOnOrder` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;
CREATE TABLE `custdebits` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CustomerID` int(10) DEFAULT '0',
`Summ` double(15,5) DEFAULT '0.00000',
`Curr` varchar(4) DEFAULT NULL,
`Rate` double(15,5) DEFAULT '1.00000',
`Source` varchar(30) DEFAULT NULL,
`DebitDate` datetime DEFAULT NULL,
`Checked` bit(1) DEFAULT '\0',
`TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2425 DEFAULT CHARSET=utf8;
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
