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.
May be related to this bug: http://bugs.mysql.com/bug.php?id=17741
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 (https://rhn.redhat.com/network/software/channels/details.pxt?cid=5271) shows 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.