Idle connection plus schema-modifying query causing locked database

Question

As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden danger to this approach that we hadn't thought about.

A few days ago we managed to end up with the database for (the staging version of) our web app stuck in a horribly hung state after the following sequence of events:

1. I connect to the remote database from our office (via Python's MySQLdb, as it happens) and run a few SELECT queries on the Foo table.
2. I leave the connection open, because I'm lazy.
3. My boss commits some changes on his laptop, pushes to the remote repo on the web server, and goes to lunch without looking at the output
4. The deployment hook on the web server tries to update the triggers and stored procedures in the database, but isn't able to even DROP the first trigger because the trigger involves the Foo table, which my currently sleeping connection had previously done some SELECTs from.
5. Now nobody can SELECT from the Foo table at all, because the connection trying to DROP the trigger has already taken out a lock on the Foo table that prevents any other connections from accessing the Foo table in any way - even though it's still waiting for the sleeping connection to be closed before it can actually do anything.
6. Crucial business processes relying upon the Foo table grind to a halt, alarms sound, and our web app stops serving customers. My boss flies into a rage and declares that heads will roll if the cause of the problem is not found and fixed so that this can never happen again. (Just kidding, it was only our staging server and my boss is very friendly.)

What's interesting is that this scenario wasn't caused by any kind of deadlock; it was caused by a sleeping connection implicitly holding some kind of lock that prevented the DROP TRIGGER statement from executing, just by virtue of having done a SELECT on the same table previously. None of the anti-deadlock features of MySQL could automatically kill a process and save the situation, because ultimately everything could continue as soon as my original process - the idle one that had only ever done SELECTs - was killed. The fact that MySQL locks behave this way by default seems perverse to me, but that's not the point. I'm trying to figure out a way to ensure that the disaster scenario described above can't ever recur (especially on our live server). How would you suggest I do this?

We've talked the problem over in the office, and there are a couple of hypothetical solutions we saw:

• Change some config setting somewhere so that sleeping processes time out after 10 seconds by default, so that a sleeping process can never sit on locks. Better yet, have them just release all locks after 10 seconds so that I can still go to lunch and leave my MySQL shell open, or my Python window open with a MySQLdb connection active, then come back and use it, without fear of breaking anything.

  • This might be really irritating when trying to run queries manually, especially ones that require grouping into a transaction.

• Work some magic on the queries that try to replace the triggers and stored procedures so that the acquisition of locks required for the relevant DROPs and CREATEs is made into an atomic operation - something like, if the query can't acquire all the locks it needs immediately in sequence, then it releases them and tries again periodically until it works.

  • This might just make our deployment process never complete, though, if the database is too busy for it to be able to grab all the locks in one go.

• Drastically reduce the frequency of schema-modifying queries we make (it only seems to be these that can be blocked from starting by a connection that's only done SELECTs), for instance by having our deployment script check whether a stored procedure or trigger in source control has changed from the version in the database before DROPping and reCREATEing the one on the database.

  • This only mitigates the problem, it doesn't actually eliminate it.

We're not sure if either of the first two solutions we considered are even possible in MySQL, though, or if we're missing a better solution (we're developers, not DBAs, and this is outside of our comfort zone). What would you recommend?

Answer 1

This is probably because auto commit is off by default, as specified by PEP 249. This seems to cause any SELECT to lock the metadata table. You can probably turn auto commit on (as long as that's safe based on your application code), which will close the implicit transaction associated with the SELECT immediately. Alternatively, use explicit transactions.

Answer 2

The fact that MySQL locks behave this way by default seems perverse to me, but that's not the point.

Actually, that's totally the point, because locks from SELECT statements is something MySQL doesn't ordinarily do... so by some as-yet-unknown mechanism, you've asked it to do that.

The most likely explanation is that you (or whatever you are using as a client, possibly unintentionally from your perspective) started a transaction that you did not commit or roll back and did the selects in the context of that transaction, or you have autocommit disabled.

If all you did was SELECT statements, this is the only explanation I can come up with, because outside a transaction, this couldn't happen and there's no other reason inside a transaction for InnoDB to have locked the table metadata due to simple SELECT. In fact, so far, I have only been able to duplicate this by using the SERIALIZABLE isolation level.

Most of the rest of this discussion assumes you're using InnoDB. If that's not the case, then I'm truly at a loss, because there's nothing about a SELECT that could lock a table in a non-transactional storage engine.

Understanding what actually caused those locks should get you closest to avoiding the problem in the future.

Change some config setting somewhere so that sleeping processes time out after 10 seconds by default, so that a sleeping process can never sit on locks.

You technically can do it, but don't do it. If you do, then you lose any real value from connection pooling and you absolutely can never, ever, safely allow auto-reconnect, because you will find transactions you thought were open are now gone, locks you thought you held are now missing, session variables you thought you'd set are now NULL. No, don't try this.

Better yet, have them just release all locks after 10 seconds

Luckily, this one is impossible, which is good, because then, you'd have nothing to tell you whether you still hold the locks you thought you held intentionally.

But, neither of these things should be a necessity if you can identify what your client is doing that's causing locks on SELECT.

Your server most likely has the information_schema.innodb_trx table, and a query of that table would be a good -- though not fail-safe -- test prior to cycling your schema changes. If there are any transactions, then you should probably wait until there aren't.

You should almost certainly be locking your tables with WRITE locks before you begin dropping triggers, since there's always the possibility that a query could insert/update/delete during the short window of time that the trigger is gone until you put it back.

If you lock all of the tables with one statement, it will obtain locks individually as they become available, and block until all of the locks can be obtained... but if you lock one table at a time, make the changes, unlock the table, and then proceed to the next table, you should be good, assuming your transactions in other sessions do what transactions should do -- get in, do work, get out, don't hang around -- but in any event, everything (lock, drop trigger, create trigger, unlock table) has to be done in one session -- on the same connection -- where you obtained the lock(s).

Less messy might be to lock one table, make changes, and unlock it again.

There's phrase in the documentation on table lock and transaction interaction that is ambiguous:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

The "any" in that sentence is deceptive. It doesn't mean any transaction on the server, it only refers to any transaction you have active in the session where you issue the LOCK TABLES statement, which there shouldn't be any.

having our deployment script check whether a stored procedure or trigger in source control has changed from the version in the database

This is actually a really good idea, anyway, because the more you tinker with a live system, the more likely it is that things could go awry... and dropping and recreating triggers and procedures unnecessarily is a recipe for unexpected application failures and data inconsistencies that can arise during those tiny timing windows, since a combination of DROP and CREATE can't be done together, atomically.