Using transaction in C# lock time out problem

  • Filter
  • Time
  • Show
Clear All
new posts

  • Using transaction in C# lock time out problem

    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='tri al1';User Name='root';Password='12345'");
    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;


    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.