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've looked around for solutions to lock a whole database, and the only one I found so far is FLUSH TABLES WITH READ LOCK.

This is fine for backups, but unfortunately I can't use that to prevent other accesses to the database while I'm patching my database with schema changes. If I run an ALTER TABLE after FLUSH TABLES WITH READ LOCK, I get the following message:

Can't execute the query because you have a conflicting read lock

Is there a way to just prevent other database connections from accessing the database temporarily while I'm patching the database?

I don't want to actively refuse the other connections, I'd just like to put them "on hold" until the patching is finished (a matter of seconds).

How can I do that?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

You should be able to lock all of the tables you intend to alter (or otherwise want to isolate, in order to modify triggers) with one statement, and your session will hold the lock while you make the changes, until you release the lock.

Example, I have 3 tables here, t1, t2, and t3. I'm going to make changes to t2 and t3, and I'm going to use t1 to illustrate whether I am actually still holding locks on t2 and t3.

Obtain write locks on t2 and t3. Any time you lock table you implicitly release any other table locks, so you have to lock them all together. Your session will block until you get all the locks, then your prompt returns.

mysql> lock table t2 write, t3 write;
Query OK, 0 rows affected (0.00 sec)

Table t2 is locked, and I can alter it.

mysql> alter table t2 add column foo int default 1;
Query OK, 25 rows affected (0.81 sec)
Records: 25  Duplicates: 0  Warnings: 0

I also have access to t3 (which happens to be empty):

mysql> select * from t3;
Empty set (0.02 sec)

However, I didn't lock t1, so I can't even select from it. When you hold one or more table locks, you can only access tables you have locks for.

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Now, to test creating a simple trigger on t2, which is still locked.

mysql> create trigger t2_ai after insert on t1 for each row set @foo = now();
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Ha! That's a typo: I incorrectly said "after insert on t1" instead of "t2" and the server stopped me from touching a table I didn't lock, while I hold other locks. I can, however, modify triggers on a table I have actually locked, if I type this correctly.

mysql> create trigger t2_ai after insert on t2 for each row set @foo = now();
Query OK, 0 rows affected (0.08 sec)

After all this, I can confirm that I still have locks on t2 and t3 by selecting from them, and I can also confirm that I have locks on "something" by trying to touch a table that I know I didn't lock, (t1 again):

mysql> select * from t1;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Changes complete, release the all of the locks you originally acquired.

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

The only caveat that comes to mind is that if something fairly serious goes wrong (not a syntax error, but a low level error like IO errors, data corruption, and OS permissions) while altering a table, the table can become implicitly unlocked... and the fact that, as I mentioned, you have to obtain all of the table locks in a single statement, because the next LOCK TABLE will unlock anything you already have locked.

Other threads trying to access the tables you've locked should block until you are done or lose your connection, which would implicitly release the locks, since they're associated with your connection/session.

share|improve this answer
    
Just realized that this wasn't working because obviously I had a ... READ lock, preventing me from modifying the schema. Because there is no such thing (AFAIK) as a FLUSH TABLES WITH WRITE LOCK, I have to manually lock all the tables (or just the ones I'm going to update, as you suggest). –  Benjamin Sep 18 '13 at 17:13

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.