MySQL Bugs: #58004: Transaction commit timeout

Bug #58004	Transaction commit timeout
Submitted:	5 Nov 2010 4:57	Modified:	11 May 2011 16:38
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	6.4.x	OS:	Any
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	commit, timeout, transaction

Description:
Standard IDbTransaction interface has only one function for committing transaction:

void Commit()

and there is no way to set the timeout for this operation.
It would be good to have a MySQL-specific function, which could set the timeout for commit:

public void Commit(int timeout)

How to repeat:
Try to set the non-default timeout for commit.

Suggested fix:
=== modified file 'MySql.Data/Provider/Source/transaction.cs'
--- MySql.Data/Provider/Source/transaction.cs	2010-08-18 19:48:34 +0000
+++ MySql.Data/Provider/Source/transaction.cs	2010-11-05 04:50:13 +0000
@@ -89,15 +89,28 @@
         /// <include file='docs/MySqlTransaction.xml' path='docs/Commit/*'/>
         public override void Commit()
         {
+          // suppose the default timeout
+          this.Commit(-1);
+        }
+
+
+        public void Commit(int timeout)
+        {
             if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed))
                 throw new InvalidOperationException("Connection must be valid and open to commit transaction");
             if (!open)
                 throw new InvalidOperationException("Transaction has already been committed or is not pending");
             MySqlCommand cmd = new MySqlCommand("COMMIT", conn);
+            
+            // set only non-negative timeout
+            if (timeout > -1)
+                cmd.CommandTimeout = timeout;
+
             cmd.ExecuteNonQuery();
             open = false;
         }
 
+
         /// <include file='docs/MySqlTransaction.xml' path='docs/Rollback/*'/>
         public override void Rollback()
         {

Bogdan, I have tried to reproduce this bug using the test code from Issue 51376, however I never receive the mentioned timeout on transaction commit, even increasing the binarySize up to 50,000,000. Do you have any other details that might help me reproduce the issue?

Julio,

I set builder.DefaultCommandTimeout to 5 sec and run few I/O intensive parallel operations (copy a 20G file).

Here is the exception message:

{"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."}

Of course setting such command timeout most likely would result in a timeout exception, but it shows the necessity of having a Commit() function with timeout parameter.

Thank you Bogdan. I just tried this: 
-  In main thread, set builder.DefaultCommandTimeout = 5
-  Also in main thread, start a transaction and do a command.ExecuteNonQuery() to write a 50MB record to DB.
-  Fire 50 other threads to copy 50 1.4 GB files
-  Wait main thread for 5 secs
-  Do a transaction.Commit() on main thread

The result is that the Commit happens in like 1/10 of a second with no issues.

I totally understand the need for the commit timeout parameter, and the suggested implementation looks good, but I first need to have a reproducible test of this timeout issue before been able to introduce the fix.

I'm wondering if there is some specific MySql variables or configurations you have setup that I might have not taken into account?

Julio,

How much time does it take in total to perform one run?
For me the application shows this:

Required Number Of Runs: 1
Required Number Of Rows Per Run: 100
Truncate Result:
Insert: 100
Took: 180.4151
Per Second: 0.6

Here my results Bogdan:

Required Number Of Runs :1
Required Number Of Rows Per Run :100
Truncate Result : Insert 100 Took 56.9041; Per Second 1.8 
Test Result : Select 100 Took 14.4670; Per Second 6.9

Julio,

Your host is 3 times faster than mine, that might be the problem why you cannot repeat the timeout exception :)
Try running it in VirtualBox to reduce the processing speed :)

I have just executed a test in a Virtual Box machine with the following environment:
OS: Windows Vista Business 64
Processors: 1 Intel Core i5 CPU
RAM: 512GB
MySQL Server version: 5.1.49
Connector/Net version: 6.3.3

The test concluded in a timeout, but such timeout was not in the call to Commit but during a DataReader.Read() call that happens after calling Commit. Commit executed with no issues. Then I still cannot reproduce this issue.

Here the partial results I got before hitting the exception:

Required Number Of Runs :1
Required Number Of Rows Per Run :100
Truncate Result : Insert 100 Took 110.4960; Per Second 0.9

During fail-over testing with Connector/J I ran into this issue as well.
A connection waiting for a commit-answer will only time-out when socketTimeout has been set. I expected that the queryTimeOut would have worked here. 
The problem with socketTimeout is that it works on all queries on the connection, so it must be a high value to prevent time-out errors on long-running queries.
The result is that it can take a long time for a connection-pool to remove the connections to the 'failed' database. A commit with a time-out option would certainly help here.

Fail-over testing was done by moving the IP-address for the database-server from one datbase-server to another database server. As far as I know, this is the only way to get 'ghost'-connections that do not throw an error and leave the client thinking the connection is still open, but an answer will never arrive.