MySQL Relational Database Server and Its Tools
View All Tags
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.
FOR UPDATE locks a row so only you can touch it. FOR SHARE locks it so nobody can change it, while others can hold the same lock with you. They look like small variations, and picking the wrong one can deadlock two sessions that seem to be doing nothing wrong.
You read a row, decide something, and write it back. Between the read and the write, someone else could change it. SELECT ... FOR UPDATE closes that gap: it locks the row while you think. Here's how it behaves from both sides, with real timings.
Under one isolation level, a transaction reads 50 twice while another session has already saved 40. Under the other, its second read says 40. Neither is a bug, and the right choice depends on what your code does with the number. Here are the two levels running side by side, and what a write does under both.
An isolation level decides what a transaction can see of other transactions' work. MySQL has a default and three scopes to change it in, and the three statements look almost identical. This guide checks the current level and changes it at each scope, then proves which level a transaction really ran at.
You run an UPDATE, and it's saved. You didn't ask for that, and that's autocommit doing its job. It's on by default, and turning it off changes what every later statement does in that session. Here's each setting at work, and the surprise waiting when you close the client.
Move 10 units from one product to another, and that's two UPDATE statements. If only the first one runs, 10 units have vanished. A transaction groups statements so they all take effect together, or none of them do. This guide runs one to the end and one back, and shows what another session sees while it's open.
