MySQL Bugs: #2264: client hangs when table is locked

Bug #2264	client hangs when table is locked
Submitted:	3 Jan 2004 1:13	Modified:	19 Aug 2005 3:13
Reporter:	Ralf Hauser
Status:	Closed
Category:	Server: Docs	Severity:	S4 (Feature request)
Version:	4.0.16	OS:	Linux (Redhat 9)
Assigned to:		Target Version:
Triage:	D4 (Minor)

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:
.

see also http://nagoya.apache.org/bugzilla/show_bug.cgi?id=26062 for the apache commons
dbcp side to this

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?

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

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?

- 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?

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.

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.