Warning: MySQL LOCK TABLES command does an implicit commit

The MySQL LOCK TABLES (and UNLOCK TABLES) command has a nasty side-effect. It implicitly commits the current transaction.

This caused a bug in production code (a normally irrelevant temporary error, which should have normally caused a rollback, only rolled back to the last UNLOCK TABLES command due to its implicit commit, and thus left the database in an inconsistent state, meaning that when the request was retried the software found the database in a state it wasn't expecting and thus couldn't process the request even though the irrelevant temporary error had now been fixed. So the request got retried indefinitely, and the result was that a lot of orphan invoices were created in the database, which wouldn't have been a problem but invoice numbers were then consumed by these unused invoices, and Austrian law dictates that invoice numbers must be sequential, i.e. the numbering system shouldn't have huge holes in it. Or, as in our case, a number of huge holes, as the system was up and running for other requests during this bug (a feature, that one faulty request shouldn't take the whole system down), so other invoices did get correctly generated at the time, and their invoice numbers were then small islands in the sea of unused/invalid invoice numbers produced by the bug.)

I wish this had been more obviously documented (MySQL 5.1 LOCK TABLES documentation). It is there in the bullet points (and the comments!), but it's hardly well emphasized, and I missed it.

The trouble is that the LOCK TABLES command comes from the stone-age of MySQL before it supported transactions at all.

This has been "fixed" in MySQL 6.0 (MySQL 6.0 changelog), via the introduction of a new options IN SHARE MODE and IN EXCLUSIVE MODE.

In the mean-time, if you need to lock a table (which one needs to do in order to ensure table-wide sequential numbering, as a primary key isn't designed for that) I would recommend creating a table with a name like "mutux" and creating a row for each type of table-lock you might like to acquire, and then SELECT FOR UPDATE the row corresponding to your table. This creates an in-transaction row-level lock, doesn't do an implicit commit, and the lock will be automatically released when the transaction ends.