Dear James,
Below are my answer to your question.
1. Yes there is begin below are the logs showing the begin
130116 18:07:05 276 Connect root@localhost on mpcms12
276 Query SHOW VARIABLES
276 Query SHOW COLLATION
276 Query SET character_set_results=NULL
276 Init DB mpcms12
276 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
276 Query BEGIN
277 Connect root@localhost on mpcms12
277 Query SHOW VARIABLES
277 Query SHOW COLLATION
277 Query SET character_set_results=NULL
277 Init DB mpcms12
277 Query Select tblUpdateCentralCopy.updateCentralCopyID From tblUpdateCentralCopy
130116 18:07:09 277 Quit
278 Connect root@localhost on mpcms12
278 Query SHOW VARIABLES
278 Query SHOW COLLATION
278 Query SET character_set_results=NULL
278 Init DB mpcms12
278 Query Select tblUpdateCentral.updateCentralID From tblUpdateCentral
130116 18:07:14 278 Quit
279 Connect root@localhost on mpcms12
279 Query SHOW VARIABLES
279 Query SHOW COLLATION
279 Query SET character_set_results=NULL
2. After each statement I do an error check in the try and catch and in the catch I update a global variable to decide whether to commit or rollback.
3. Error after Commit I am not so clear in this where to check?
4. The version is 5.0.67-community-nt and .net connector version 5.2.3
5. Below the table
CREATE TABLE `tblproduct` (
`productID` int(5) NOT NULL auto_increment,
`productCategoryID` int(10) NOT NULL,
`productSubCategoryID` int(10) NOT NULL,
`productBrandID` int(10) NOT NULL,
`productGroupID` int(10) NOT NULL,
`productStatusID` int(10) NOT NULL,
`productCode` varchar(100) NOT NULL,
`productName` varchar(100) NOT NULL,
`productPrice` double(10,2) NOT NULL default '0.00',
`productType` enum('Accessory','Coupon','Imei') NOT NULL,
`productTotalStock` int(10) NOT NULL default '0',
`productTotalAmount` double(10,2) NOT NULL default '0.00',
`productBooked` int(5) NOT NULL default '0',
`productStatus` enum('y','n') NOT NULL default 'y',
`rrpMarkUpType` enum('Fixed','Amount','Percentage') NOT NULL,
`rrpMarkUpFixed` double(6,2) NOT NULL default '0.00',
`rrpMarkUpAmount` double(10,2) NOT NULL default '0.00',
`rrpMarkUpPercentage` double(5,2) NOT NULL default '0.00',
`branch1TotalStock` int(10) NOT NULL default '0',
`branch1TotalAmount` double(10,2) NOT NULL default '0.00',
`branch2TotalStock` int(10) NOT NULL default '0',
`branch2TotalAmount` double(10,2) NOT NULL default '0.00',
`branch3TotalStock` int(10) NOT NULL default '0',
`branch3TotalAmount` double(10,2) NOT NULL default '0.00',
`branch4TotalStock` int(10) NOT NULL default '0',
`branch4TotalAmount` double(10,2) NOT NULL default '0.00',
`branch5TotalStock` int(10) NOT NULL default '0',
`branch5TotalAmount` double(10,2) NOT NULL default '0.00',
`branch6TotalStock` int(10) NOT NULL default '0',
`branch6TotalAmount` double(10,2) NOT NULL default '0.00',
`branch7TotalStock` int(10) NOT NULL default '0',
`branch7TotalAmount` double(10,2) NOT NULL default '0.00',
`branch8TotalStock` int(10) NOT NULL default '0',
`branch8TotalAmount` double(10,2) NOT NULL default '0.00',
`branch11TotalStock` int(10) NOT NULL default '0',
`branch11TotalAmount` double(10,2) NOT NULL default '0.00',
`branch12TotalStock` int(10) NOT NULL default '0',
`branch12TotalAmount` double(10,2) NOT NULL default '0.00',
PRIMARY KEY (`productID`),
UNIQUE KEY `productName` (`productName`),
UNIQUE KEY `productCategoryCode` (`productCategoryID`,`productSubCategoryID`,`productBrandID`,`productGroupID`,`productStatusID`)
) ENGINE=InnoDB AUTO_INCREMENT=8444 DEFAULT CHARSET=latin1;
Just to give you some brief details the system consist of back-end and front-end. This operation is with regards to back-end but at the same time I notice the front-end was also operating on the same product 7921. Could it be due to this? It have been running smoothly over some long period only this one issue.