While working on MySQL locks, I have found one weird thing. I got one alert from Nagios that there are many queries which are locked. When I have checked “Show Processlist” I found that many queries were waiting for release read lock with below status.
“Waiting for release of readlock”
I have tried to run “UNLOCK TABLES” but it didn’t work. When read in MySQL reference manual I have found that UNLOCK TABLES explicitly releases any table locks held by the current session. It means it can not unlock those tables which are locked by some other user or other session. But UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. Means If any one will lock all tables or DBs with “FLUSH TABLES WITH READ LOCK” command than only we can unlock tables from another or any session otherwise we can’t release that lock.
Finally, I had to restart MySQL instance and than locks are released. It should not be done like that but it happened. I’m still working on this and trying to found out it really works like that or there is some bug on MySQL version.