G
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.
