Description of problem:
Occasionally we are seeing a problem with a read lock that cannot be removed.
The server is a replication slave and so the read lock that is applied to all
tables is blocking the sql slave thread and hence replication fails. The read
lock is put in place by a database backup script that runs every ten minutes and
then removes the lock.
Version-Release number of selected component (if applicable):
5.0.18-4.el4s1.1 from the Red Hat Web Application Stack Beta 1.0 Channel
It does not occur frequently. Every few days we may see the problem, and
'mysqladmin debug' does not offer any good diagnostics to show what is holding
open the lock.
Steps to Reproduce:
1. The script that takes the backup runs every ten minutes
2. In one thread it issues 'FLUSH TABLES WITH READ LOCK', then it starts a
mysqlbackup of all databases with no locks. Finally, 'UNLOCK TABLES' is issued
in the first thread.
3. 'show processlist' shows that the slave sql thread is waiting on a read lock
to make an insert or update from the replication data. However there are no
other threads running which have the outstanding lock.
4. 'show slave status' shows the seconds from master steadily increasing.
5. Issuing 'unlock tables' in a mysql interactive session does not appear to
remove the lock as the slave sql thread still mentions it is waiting on a read lock.
6. Issuing 'stop slave' hangs, and isn't able to stop the slave.
7. Mysql must be restarted to get rid of the lock.
A phantom read lock prevents the slave sql thread from updating the database.
The read lock should be removed as soon as 'unlock tables' is issued by the client.
There doesn't appear to be any way to diagnose the state of locks on the MySQL
server, which could be used to determine the origin of this problem.
May be related to this bug:
The currently shipping Application Stack release of mysql is 5.0.30. Please update and see whether the
problem still exists.
The Red Hat Web Application Stack 1.0 Beta (for ES v. 4 AMD64/EM64T) channel
that mysql-server-5.0.18-4.el4s1.1.x86_64 is the latest available package.
I do not know why you are subscribed to a beta channel for a product that was released many months ago,
but I cannot provide support for an ancient beta version. Please update to the current release and let me
know whether it still shows the bug.
Closing for lack of response.