MySQL Relational Database Server and Its Tools
View All Tags
Your application's logs say what it meant to send. MySQL's general query log says what arrived, including what the driver adds by itself, such as BEGIN, SET and savepoints. It's the tool I reach for when something about a transaction makes no sense. Use it for a moment, and turn it off again.
Some MySQL statements can't run inside a transaction, so MySQL commits the transaction first and doesn't mention it. Your next ROLLBACK then has nothing to undo. It's why a failed migration can leave half its changes behind. Here's the behaviour, and the statements that cause it.
ROLLBACK throws away a whole transaction. Sometimes you only want to undo the last part: try something, and if it doesn't work, carry on with what you've already done. A savepoint is a marker inside the transaction that lets you do just that.
A deadlock needs a cycle: I hold what you want, and you hold what I want. If everyone takes rows in the same order, the cycle can't form, and the second transaction waits instead of failing. This is the most effective fix for deadlocks, and it's a small change.
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.
