Database & transactions

What a row lock actually locks

The mistake

Two things, and the second one costs more.

The first: that a lock covers the rows a statement changed. It covers the rows it had to look at. If the column in your WHERE has no index, the engine cannot know which rows match without reading all of them, and every row it read is locked until the transaction ends. An update that changed two rows can hold the whole table, and block a statement that touches a row it never went near.

The second: that a lock wait and a deadlock are the same accident wearing different numbers. A lock wait timeout fails one statement and leaves your transaction open, holding everything it had already done. A deadlock rolls back the whole transaction. Code that retries the same way after both is wrong after one of them.

The machine

Drive it

Five jobs, two connections, and no index on status. Connection A has already run one update.

• Read the top line. Two rows changed, five rows locked. A only touched the queued jobs, and it is holding every row in the table.
• Press the featured button twice. B updates row 5, a row A never changed, and blocks anyway. Then let it wait: error 1205, and only that statement is gone. B’s transaction is still open.
• Add the index and run them both again, alternating. The locks are tight now, and the two connections still meet in the middle: A holds the queued jobs and wants row 5, B holds row 5 and wants the queued jobs. That is error 1213, and this time the whole transaction goes.

The mechanism

InnoDB locks index records, not rows in a table you can point at. When your WHERE can use an index, the engine walks to the matching entries and locks those, plus a gap around them to stop anything being inserted into the range it just read. When there is no usable index, the only way to answer the question is a full scan, and the scan locks every record it passes. Under REPEATABLE READ, which is the default, the rows that turned out not to match are not released when the statement finishes. They are held until the transaction ends. That is why a connection reaching for a row your update never changed waits for your commit, not for your statement.

Reading performance_schema.data_locks on a real MySQL while that update is open makes it concrete: without an index on status, the two-row update holds six record locks, one for each of the five rows and one for the supremum, which is the marker for the end of the index. With an index, the same update holds the two matching entries, one gap lock and the two primary key rows. Rows 3 and 5 are free.

A lock wait is one transaction waiting for another to finish. Waiting is not itself an error, and it runs up to innodb_lock_wait_timeout, fifty seconds by default. If the other side commits first, it goes through and nobody ever knows. If the clock runs out you get error 1205, and because innodb_rollback_on_timeout is off by default, only the statement is rolled back. The transaction is still open. It still holds its locks. Everything it wrote before is still there, waiting for you to commit it or throw it away.

A deadlock is a circle: A is waiting on B and B is waiting on A, so no amount of waiting will help. InnoDB notices immediately and breaks the circle by choosing a victim and rolling its transaction back completely, with error 1213. Everything that transaction had done is gone, not just the statement that closed the circle. The other one carries on as though nothing happened.

In your code

Deadlocks are not a bug to eliminate, they are a condition to handle. The retry is on the whole transaction, because that is what was rolled back:

DB::transaction(function () use ($jobIds) {
    Job::whereKey($jobIds)->lockForUpdate()->get();
    // ... the work
}, attempts: 3);

The second argument to DB::transaction is the number of attempts, and it retries on deadlock. It re-runs the closure from the top, which is what a rolled back transaction needs.

Two habits prevent most of them:

// index whatever you filter on in a write, so the lock is the rows you meant
Schema::table('jobs', fn (Blueprint $t) => $t->index('status'));

// take locks in a consistent order everywhere, so no two paths can cross
$jobs = Job::whereIn('id', $ids)->orderBy('id')->lockForUpdate()->get();

The ordering one is the fix people skip. A deadlock needs two transactions reaching for the same rows in opposite orders. If every path in your code takes them in ascending id order, there is no opposite order to reach in.

The fine print

• Measured on MySQL 9.6 with InnoDB defaults: REPEATABLE READ, innodb_lock_wait_timeout 50, innodb_rollback_on_timeout off. The lock counts came from performance_schema.data_locks, and both failures were produced by running two real connections into each other. The manual linked below is the 8.4 one, a version behind what this was measured on. That is deliberate: 8.4 is the LTS release and its pages stay put, while the Innovation series keeps only the newest manual online, so a 9.6 link quietly redirects to whatever shipped last. The InnoDB locking chapter reads the same in both.
• The panel does not model gap locks, next-key locks or the supremum record, which is what the real lock list is made of. It models which rows are held, which is the part that decides who waits.
• Which transaction gets rolled back is not yours to choose. InnoDB picks the one it judges to have done less work. In the measured run it was the one whose statement closed the circle, and the panel follows that, but do not build anything on it.
• innodb_rollback_on_timeout can be turned on, and then a timeout rolls back the transaction too. It is off by default, so the difference described here is what you will meet unless someone changed it.
• Under READ COMMITTED the gap locks mostly go away and the scan releases the non-matching rows as soon as the statement finishes. Measured on the same table, the six record locks become two, and rows 2, 3 and 5 are free while the transaction is still open. It is still cheaper to index the column than to reason about which locks survive.
• SHOW ENGINE INNODB STATUS prints the last deadlock in full, including both transactions and which one was rolled back. It is the first thing to read when one shows up in production.

Further reading

• MySQL: InnoDB locking is the reference for record, gap and next-key locks, and what each statement takes.
• MySQL: deadlocks in InnoDB covers detection, the victim choice, and how to minimise them.
• Laravel: database transactions documents the attempts argument that retries a transaction after a deadlock.