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. 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.
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
SHOW PROCESSLIST. In addition
to server-level locks, any storage engine that supports row-level locks,
such as InnoDB, implements its own locks. In MySQL 5.0 and earlier
versions, the server is unaware of such locks, and they’re mostly hidden
from users and database administrators. There’s more visibility in MySQL
5.1 and later versions.
A lock wait can happen at either the server level or the storage engine level. (Application-level locks could be a problem too, but we’re focusing on MySQL.) Here are the kinds of locks the MySQL server uses:
Tables can be locked with explicit read and write
locks. There are a couple of variations on these locks, such as
local read locks. You can learn about the variations in the
LOCK TABLES section ...