MySQL : “FLUSH TABLES WITH READ LOCK” started automatically

lockingMySQL

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?

Best Answer

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.

Related Solutions

Mysql – time out the scripted “FLUSH TABLES WITH READ LOCK;”

There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes.

However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM snapshot.

It is required if:

You are interested in point in time recovery (applying incremental restore via binlogs)
You have lots of non-transactional (ie MyISAM) tables being written to
You are running the snapshot against the master and want to create a slave based on the snapshot.

If your database is InnoDB oriented, and nothing interesting is hapenning with your MyISAM system tables (ie no one creating a procedure, GRANTing privileges etc.) then it's enough that you FLUSH only those tables.

All things InnoDB, you can just take the snapshot. There is no benefit in FLUSHing. InnoDB will make proper recovery anyhow.

The need for FLUSH TABLE comes from the requirement of getting the binary log file and position when you want to be able to rebuild a slave or otherwise use binary logs.

Does MySQL Guarantee Atomic Upgrade from Read Lock to Write Lock?

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back

Best Answer

Related Solutions

Mysql – time out the scripted “FLUSH TABLES WITH READ LOCK;”

Does MySQL Guarantee Atomic Upgrade from Read Lock to Write Lock?

Related Question