Bug 248091

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

How reproducible:

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.
  
Actual results:
A phantom read lock prevents the slave sql thread from updating the database.

Expected results:
The read lock should be removed as soon as 'unlock tables' is issued by the client.

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