MySQL Performance: MySQL => Using transaction in C# lock time out problem

Using transaction in C# lock time out problem [message #1129]

Sat, 21 April 2007 15:27

newtomysql
Messages: 30
Registered: February 2007

Member

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.

Report message to a moderator