Two copies of your app are running, and only one of them should run the nightly report. There's no row to lock. GET_LOCK gives you a lock on a name you make up, held by one connection at a time, and it's the same tool EF Core uses to stop two migrations running at once.
An ALTER TABLE is stuck, and everything that arrives after it queues up behind it. The row-lock views are empty, because this is a different kind of lock: a metadata lock, which protects a table's structure. The holder is often something harmless looking, such as a transaction that ran one SELECT and never finished.
A report runs several queries, and if the numbers move underneath it, they won't add up. A read-only transaction with a consistent snapshot pins down what it sees and guarantees it can't change anything by accident. Here's what each part does.
A report with a bad join can run for hours and take the server down with it. max_execution_time tells MySQL to stop a SELECT after a set time. The limit lives on the server, so it works no matter which program sent the query.
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.
