Dear All,
I am using Innodb.I have grid and a list of item. Then I run a loop and based on it i will run an update statement which is all in block of transaction. A snippet of the code is as below .
MySqlConnection connectionCentral;
MySqlTransaction transactionCentral = null;
connectionCentral = new MySqlConnection(“Address=‘localhost’;Database=‘trial1’;User Name=‘root’;Password=‘12345’”);
connectionCentral.Open();
transactionCentral = connectionCentral .BeginTransaction();
for (int j = 0; j < gridReceiveTransfer.RowCount; j++)
{
String myUpdateQuery8 = “Update tblProduct " +
“Set tblProduct.branch1 = tblProduct.branch1 +” + Convert.ToInt32(this.gridReceiveTransfer[8, j].Value.ToString()) +
" Where tblProduct.productID=” + Convert.ToInt32(this.gridReceiveTransfer[0, j].Value.ToString());
MySqlCommand myCommandCentral1 = new MySqlCommand(myUpdateQuery8);
myCommandCentral1.Connection = connectionCentral;
myCommandCentral1.Transaction = transactionCentral;
myCommandCentral1.ExecuteNonQuery();
}
This how i have actually code my code in C#. The error I get is “Lock wait timeout exceeded; try restarting transaction”. I know why I get the error is sometime when the update is for the same productID within the same loop. So how can I overcome this problem where I want to keep all the updates within the loop so incase there is one error I want to rollback the whole thing. Thanks for the kind help.