write lock all tables in mysql for a moment

Question

I need to perform some scripted actions, which may take a while (like maybe a minute). At the beginning of these actions, I take some measures from the MySQL DB and it's important that they do not change until the actions are done. The DB has dozens of tables since it belongs to a quite old fashioned but huge CMS, and the CMS users have a dozen options to modify it.

I do not even want to change anything in the time my scripts runs in the DB myself, it just shall be frozen. It's not a Dump or Update. But tables should be kept open for reading for everyone, to prevent visitors of the connected homepage from getting errors.

If the database altering actions, which may be performed by other CMS users in the meantime would be triggered after the DB is unlocked again, it would be perfect, but if they fail, I would not mind.

So I thought at the beginning of the script I lock the tables down with

lock first_table write;
lock second_table write;
...

And after I do

unlock tables

I think that should do exactly what I want. But can I archive this for all tables of the db without naming them explicitly, to make this more futureproof?

This does not work for sure:

lock tables (select TABLE_NAME from information_schema.tables
where table_schema='whatever') write;

Another question would be, if someone can answer this on the fly, if I would have to perfom the lock/unlock with another MYSQL user than the one used by the CMS. If I understood this right, then yes.

Answer 1

Below is the statement to lock all tables (actually it creates a single global lock):

FLUSH TABLES WITH READ LOCK;

Then release it with:

UNLOCK TABLES;

Mysqldump does this, for example, unless you are backing up only transactional tables and use the --single-transaction option.

Read http://dev.mysql.com/doc/refman/5.7/en/flush.html for more details about FLUSH TABLES.

---

Re your comment:

Yes, this takes a global READ LOCK on all tables. Even your own session cannot write. My apologies for overlooking this requirement of yours.

There is no equivalent global statement to give you a write lock. You'll have to lock tables by name explicitly.

There's no syntax for wildcard table names, nor is there syntax for putting a subquery in the LOCK TABLES statement.

You'll have to get a list of table names and build a dynamic SQL query.