High Performance MySQL, 2nd Edition

Appendix D. Debugging Locks

Any system that uses locks to control shared access to resources can be hard to debug when a lock contention issue crops up. Perhaps you’re trying to add a column to a table, or just trying to run a query, when suddenly you find that your queries are blocked because something else is locking the table or rows you’re trying to use. This appendix shows you what to do when you encounter these situations in MySQL. Often all you will want to do is find out why your query is blocked, but sometimes you will want to know what’s blocking it, so you know which process to kill. This appendix shows you how to achieve both goals.

Lock Waits at the Server Level

A lock wait can happen at either the server level or the storage engine level. ^[133] (Application-level locks could be a problem too, but we’re focusing on MySQL.)

The MySQL server itself uses several types of locks. If a query is waiting for a lock at the server level, you can see evidence of it in the output of SHOW PROCESSLIST. In addition to server-level locks, any storage engine that supports row-level locks, such as InnoDB, implements its own locks, at least at the time of this writing. In MySQL 5.0 and earlier versions, the server is unaware of such locks, and they’re mostly hidden from users and database administrators. Future versions may expose more of these locks at the server level, probably through pluggable INFORMATION_SCHEMA tables.

Here are the kinds of locks the MySQL server uses:

Table locks

Tables ...