Databases, Data Modeling, and Data Access
View All Tags
An INSERT adds a brand-new row. So what could it possibly be waiting for? Under MySQL's default isolation level, it can be waiting for a gap: the space between rows, locked by a transaction that asked about a row that isn't there. Here's a gap lock, the insert it blocks, and how READ COMMITTED removes it.
InnoDB has row locks, so an UPDATE of one row can't get in the way of another row. Except that it can. InnoDB locks the rows it looks at, not the rows it changes, so when the WHERE can't use an index, it looks at all of them. Here's one row's update blocking the whole table, and the index that fixes it.
Something is stuck: a query that won't finish, or a forgotten transaction blocking everyone else. MySQL's KILL ends it, and it comes in two strengths. Here's how to find the connection, what each form does, and what the session on the other end sees.
A statement that can't get a row lock waits, and by default it waits 50 seconds. That's a long time for a web request to hang. innodb_lock_wait_timeout changes the limit, per session or for the whole server. And what happens when the limit passes isn't what most people expect.
MySQL's status report keeps one deadlock, the most recent. If deadlocks come in bursts at 3 a.m., that's no use. One setting writes every one of them to the error log, with the same detail, so you can read them the next morning.
A deadlock error tells you that you lost, and not to whom. MySQL keeps the full story of its latest deadlock: both transactions, what each held, what each wanted, and which one it chose to roll back. Here's how to get that report, and how to read a real one.
A program starts a transaction and never ends it, because of a forgotten COMMIT, a crash in the middle, or a person who went to lunch. Nothing looks wrong until someone else needs one of its rows. information_schema.innodb_trx shows every transaction that's open, so you can find it first.
When a lock problem makes no sense, stop guessing and look at the locks. performance_schema.data_locks lists each one InnoDB holds. Run the same UPDATE three ways, by primary key, by unique index and by a plain index, and you get three different sets, which explains most of the surprises people have with row locks.
An UPDATE that normally takes a millisecond has been sitting there for a minute. Nothing is wrong with the statement: another transaction holds the row. MySQL's sys schema can tell you who, in one query, along with the statement to get rid of them.
SELECT ... FOR UPDATE waits for a row that someone else holds. Sometimes waiting is the wrong thing to do: you'd rather say "busy" right away, or take the next row instead. NOWAIT and SKIP LOCKED do those two things, and the second one turns a plain table into a job queue.
