Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being locked up by FLUSH TABLES WITH READ LOCK

03:21:31 select type_id, count(*) from guid_target_infos group by type_id
02:38:11 select type_id, count(*) from guid_infos group by type_id
02:24:29 FLUSH TABLES WITH READ LOCK

But i did not start this command. can someone tell me why it was started automatically?

share|improve this question

migrated from stackoverflow.com Oct 30 '11 at 13:33

This question came from our site for professional and enthusiast programmers.

    
This should be asked on serverfault.com, I have voted to move it there. –  lexu May 13 '10 at 6:39
    
moved, thanks for comment! –  ming yeow May 13 '10 at 16:13
add comment

1 Answer 1

Some people use FLUSH TABLES WITH READ LOCK to lock a DB Server and perform mysqldumps. Doing this will trigger a full flush of any InnoDB table that has dirty pages in the InnoDB Buffer Pool.

Over the years, I have scripted some backups using mysqldump prefaced with FLUSH TABLES WITH READ LOCK. Within one DB thread, I would run FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);. I would then capture the MySQL ProcessID running the SLEEP function. When the mysqldump was finished, I would kill the MySQL ProcessID running the SLEEP function.

I knew some clients that manually ran the FLUSH TABLES WITH READ LOCK by hand in one session and ran the mysqldump in a separate ssh session.

Either way, FLUSH TABLES WITH READ LOCK was issued with this purpose in mind. Please look over the slow query logs for SLEEP function calls. Also, check coworkers for any crontab jobs doing backups with it or any adhoc backups requiring FLUSH TABLES WITH READ LOCK. The mysqld process would not issue that command on its own by design.

share|improve this answer
    
I feel like I'm missing something. Why would you sleep? Just to have enough time to find an id in the process list? Why not just run unlock tables from the session that issues the lock? –  atxdba Dec 30 '11 at 2:39
    
I wrote a script on how do this : dba.stackexchange.com/a/8613/877. The idea is not to sleep for 86400 sec (24 hours). The SLEEP is to maintain the session that issued the lock. Then, the backup is finished, you kill the process ID of the session that issued the lock. –  RolandoMySQLDBA Dec 30 '11 at 3:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.