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

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

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.