MySQL Bugs: #28004: Using transaction in C# lock time out problem

Bug #28004	Using transaction in C# lock time out problem
Submitted:	21 Apr 2007 19:42	Modified:	23 Apr 2007 1:24
Reporter:	newbie Shai	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
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='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".                 
                 

How to repeat:
I get the error when the update is for the same productID within the same loop. So this happens when there is many updated to the same productID. 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. I am using MySQL Connector Net 1.0.8 ? Do you is this a problem with connector itself ?

Hi!

Where does your application commit its transaction?

InnoDB's default lock wait timeout is 50 seconds. I assume a single transaction in your application does not last that long?

You can use the innodb_lock_monitor to print locks held by different users. That may help to find what is wrong.

Regards,

Heikki

Dear Heikki Tuuri,
                  I commit after fulling running the for loop. Thus I will run this code below 
                 try
                    {
                        transactionCentral.Commit();
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        try
                        {
                        transactionCentral.Rollback();
                        }
                        catch (MySqlException ex1)
                        {
  MessageBox.Show("An exception of type " + ex.GetType() +" was encountered while inserting the data.");
                           
                        }
                    }
I am not sure how to use innodb_lock_monitor ? Can I get more help on this please ? I would like to master innodb I know I am new to it. When must I run innodb_lock_monitor while I am running the system or after the system. Thanks a lot in advance for all your help.

Hi!

Please look at http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html about how to use InnoDB monitors.

You can also use the SHOW INNODB STATUS\G command to spot any dangling, uncommitted transactions.

Regards,

Heikki

Innodb status output file

Attachment: innod.xml (text/xml), 3.16 KiB.

Dear Heikki Tuuri,
                  I tried using the statement SHOW INNODB STATUS\G but gives me a syntax error. So what shoud I do then? But the statment SHOW INNODB STATUS work. Since I working using the client sqlyog so I managed to import an xml file which I really dont know how to read.

                  I would like to know the details which is happening in my loop ? How does it work is it that each of my updates works immediately or only upon commit ? Why do you think my lock time out error is happening is it because I am updating the same row immediatly one after another is it ? Is that the row gets lock? I have attached the file too.Hope you can clear my doubts. Thanks.

Dear Heikki Tuuri,
                  I will like to give you some additional information actually why I am running transaction is that each update I update one copy in the local machine and another one is on another machine. So the error of lock time out is happening on the another machine. So maybe this information can help you see my problem better I guess. Many thanks.

Hi!

The SHOW INNODB STATUS does not show any dangling transaction nor any transaction waiting for a lock.

Please study more carefully, and reopen this bug report if you can find a MySQL/InnoDB bug.

Regards,

Heikki

Dear Heikki,
            I tried the SHOW INNODB STATUS\G it works from the command line. So in that got one section of transaction ? So how can I can know which one is dangling and how to go about the dangling transaction restart the mysql server is it ? Why is it that the SHOW INNODB STATUS\G doesnt work in mysql client ? Another question is where can I get the details of what is happenning actually in the loop. Thanks.