MySQL FLUSH TABLE – Resolving Active Locked Tables Issue

lockingMySQLmysql-5.6

If I execute these queries one by one, all fail except the first:

Query1 :

flush table tablename1 with read lock;

Works

Query2 :

flush table tablename2 with read lock;

Error : can't execute the given command because you have active locked tables mysql

If I execute, in below way, it works:

Query:

flush table tablename1,tablename2 with read lock;

What's happening inside?

Best Answer

This is because

flush table tablename1,tablename2 with read lock;

is contained within one transaction and

flush table tablename1 with read lock;

flush table tablename2 with read lock;

Is not.

In this second scenario the first table is locked for that session. That session can't acquire a second lock on another table because the session already has one table open. If you tried to access another different table by using a SELECT, within that same session, for example tablename2, that would also be refused.

MySQL [testlocks]> select * from tablename2;
ERROR 1100 (HY000): Table 'tablename2' was not locked with LOCK TABLES

So to make a long story short, by locking only one table you are saying that the current session only has access to that one table. At least that's how I understand MySQL's documentation.

Or you could just use

flush table tablename1,tablename2 with read lock;

as you already figured out and this is the syntax given by MySQL's documentation here

Related Solutions

MySQL : “FLUSH TABLES WITH READ LOCK” started automatically

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.

MySQL hang in load data infile command

Please look carfully at session 1.

Lock table v3_zone_date WRITE; 
SET AUTOCOMMIT=0;  
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date  
FIELDS TERMINATED BY  ',';  
COMMIT; 
SET AUTOCOMMIT=1;

should be

Lock table v3_zone_date WRITE; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date  
FIELDS TERMINATED BY  ',';  
UNLOCK TABLES;

No need to have these lines

SET AUTOCOMMIT=0;
COMMIT; 
SET AUTOCOMMIT=1;

because MySQL 5.0 Certification Study Guide, Chapter 29, Page 418 Bulletpoint #1 says

UNLOCK TABLES implicitly commits only if you have explicitly locked tables with LOCK TABLES. SET AUTOCOMMIT = 1 implicitly commits only if autocommit mode wasn't already enabled.

LOCK TABLES will hold up any transaction-based row locks that are trying to be acquired.

Since the tables are locked independently, you must just need to increase the size of your InnoDB Buffer Pool (innodb_buffer_pool_size) to make more room for row locks across all InnoDB tables.

Best Answer

Related Solutions

MySQL : “FLUSH TABLES WITH READ LOCK” started automatically

MySQL hang in load data infile command

Related Question