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: | |
Category: | Connector / NET | Severity: | S4 (Feature request) |
Version: | 6.4.x | OS: | Any |
Assigned to: | Julio Casal | CPU Architecture: | Any |
Tags: | commit, timeout, transaction |
[5 Nov 2010 4:57]
Bogdan Degtyariov
[24 Mar 2011 17:50]
Julio Casal
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?
[25 Mar 2011 12:07]
Bogdan Degtyariov
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.
[25 Mar 2011 14:19]
Julio Casal
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?
[12 Apr 2011 11:54]
Bogdan Degtyariov
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
[25 Apr 2011 19:51]
Julio Casal
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
[27 Apr 2011 10:22]
Bogdan Degtyariov
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 :)
[9 May 2011 18:16]
Julio Casal
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
[14 Jun 2011 8:45]
Fre Weers
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.