Bug #2264 client hangs when table is locked
Submitted: 3 Jan 2004 0:13 Modified: 19 Aug 2005 1:13
Reporter: Ralf Hauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:4.0.16 OS:Linux (Redhat 9)
Assigned to: CPU Architecture:Any

[3 Jan 2004 0:13] Ralf Hauser
Description:
I am planning to use mysqlhotcopy to backup my data.

As the DB is used in a web application, I want to minimize the impact to users.
So, I guess read locks are what is appropriate.

However, it appears that when a read-lock is set, the default client hangs for long time (probably forever).

Therefore my request for enhancement:
Add to http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOCK_TABLES
some hints 
- how to set a reasonable time-out.
- what the user will see in case of a time-out hopefully (not just timeout, but some further hint on the root cause like "table locked, possibly backup ongoing" or something alike)

How to repeat:
.

Suggested fix:
.
[12 Jan 2004 7:48] Ralf Hauser
see also http://nagoya.apache.org/bugzilla/show_bug.cgi?id=26062 for the apache commons dbcp side to this
[13 Jan 2004 23:41] Ralf Hauser
Interesting - no reaction to http://lists.mysql.com/list.php?6:mss:6773:200401:momdckhdanadclcjmmfc either.
Is this really a non-problem?
Or are the answers to hibernate or postgresql?
[3 Jul 2004 21:38] Erik
I am planning to implement something similar to mysqlhotcopy.

If the client hangs as described, then that is a very serious problem and should be throughly documented.  After all, it really defeats the purpose of doing a hotcopy...  You'd be better off just shutting down the server and doing a fast file copy.

I stumbled across this bug entry while looking for something unrelated.  I am glad to have the heads up that I may be heading into trouble with my hotsync plans.

I'm surprised that there has not been any action/response to this bug so far.  By contrast, a doc bug that I entered a few days ago was fixed and closed in about 5 minutes from when I entered it.  Wow!

-- Erik
[25 Nov 2004 19:51] Ralf Hauser
might a cluster help to allow for a backup without service interruption?

Unfortunately, http://dev.mysql.com/doc/mysql/en/MySQL_Cluster_Backup_Concepts.html doesn't say whether the database is locked in this case.

Or would one rather http://dev.mysql.com/doc/mysql/en/STOP_SLAVE.html, backup and the restart the slave?
Probably, the slave in this case would not have to be stopped, but only to be temporarily disconnected from master updates and not available for other clients?
[11 Aug 2005 16:12] Mike Hillyer
- what the user will see in case of a time-out hopefully (not just timeout, but
some further hint on the root cause like "table locked, possibly backup ongoing"
or something alike)

Do you mean in the application itself?
[11 Aug 2005 18:26] Ralf Hauser
Ok, it is over 1.5 years since:
- I guess I should have written "write-lock" instead of "read lock".

Obiously, the clients mysql command-line, jdbc determine what they pass on to the application, which in turn possibly adds i18n support etc.

However, it would be great, if the server would not just lets the clients hang forever, but fails queries and provide some hint why it is not moving on - after some configurable timeout period.
[19 Aug 2005 1:13] Paul DuBois
The manual already indicates that if a table is locked,
LOCK TABLES blocks until the lock can be acquired:

http://dev.mysql.com/doc/mysql/en/lock-tables.html

We can't indicate how to set a timeout, because there
is no syntax for doing so.