Writing at Scale: Transactions, Locks, Contention

Writing at Scale is where the queue from Chapter 2 stops being a pool of workers and becomes a single row in a table. When two transactions want the same row, one of them waits, and no amount of worker capacity changes that. Concurrency at the row is one.

Key takeaways

• MySQL's manual states that InnoDB always locks index records, using a hidden clustered index for tables without one, which makes index design and lock design the same activity.
• The same page states that if the searched column is not indexed or has a non-unique index, the statement also locks the preceding gap, so an unindexed update blocks inserts you never thought about.
• InnoDB's default isolation level is REPEATABLE READ, under which it locks the index range scanned using gap or next-key locks; READ COMMITTED locks only index records, not the gaps before them.
• PostgreSQL's manual says it detects deadlocks automatically and resolves them by aborting one transaction, and that the best defence is acquiring locks on multiple objects in a consistent order.
• Laravel's DB::transaction($closure, attempts: 5) retries on deadlock and rethrows once attempts are exhausted, which absorbs a collision rather than preventing it.

Read this after Chapter 2, whose utilisation arithmetic explains why a serialised row is worse than a slow query, and after Chapter 3, whose index decisions turn out to determine what your writes lock. Chapter 7 is where the same contention arrives from queue workers instead of web requests, which is the version that scales itself into a wall.

Order volume goes up by half. Checkout starts timing out at peak, and only at peak.

The database is not busy. CPU is low, the disks are idle, and every individual statement in the slow query log completes quickly. What the metrics do show is lock wait time, on one table.

Every order was incrementing a counter row to generate its reference number. One row. Every checkout in the system, in a line, waiting for it.

Lock scope is the only variable you control

You do not get to choose whether writes lock. You choose three things about the lock, and every technique in this chapter is one of the three.

How many rows it covers, which is determined by your indexes and your predicates rather than by your intent. How long it is held, which is determined by where the transaction begins and ends. And how strong it is, which is determined by the lock mode you asked for or the one the engine inferred.

Duration is the one with the most leverage and the least attention. A transaction that opens, locks a row, makes an HTTP call to a payment provider, then commits, holds that row for the duration of somebody else's network. The database is fine. Your throughput is not.

Move the slow work out of the lock window. That single move fixes more contention than every index in the chapter.

InnoDB locks index records, not rows

This is the sentence that reorganises how you read your own schema. MySQL's manual states that InnoDB always locks index records, and that for a table with no indexes at all it uses a hidden clustered index for the locking.

Which means the lock follows the access path. Update a row through a unique index and you lock one index record. Update the same logical row through a scan and you lock everything the scan touched, because the engine cannot know which records matched until it has read them.

The row worth staring at is the AUTO-INC one, because it is table scoped rather than row scoped. Bulk insert paths that felt independent are not.

Gap locks are where the surprise lives

Gap locks are the mechanism behind most contention that teams describe as impossible. MySQL's manual describes them as purely inhibitive, meaning their only purpose is to prevent inserts into the gap, and notes that gap locks from different transactions can co-exist.

Then it gives the sentence that explains the field reports. If the searched column is not indexed or has a non-unique index, the statement locks the preceding gap. So an update filtered on an unindexed column does not just lock the matching rows. It locks ranges where matching rows could have been.

The visible consequence is an insert blocking on an update that touched a different row entirely. Nobody reading the two statements would predict it. Everybody who has seen it once remembers. Add the index, and the gap shrinks to the record.

The manual also states that gap locking can be disabled explicitly, by changing the isolation level to READ COMMITTED, after which gap locking is used only for foreign-key and duplicate-key checking. Note what that means. The lock scope depends on a setting most teams have never read.

REPEATABLE READ is the default, and it locks ranges

MySQL's isolation documentation states that InnoDB's default isolation level is REPEATABLE READ. That default is a good one for correctness and it is the expensive one for contention, so it is worth knowing what you are getting.

Under REPEATABLE READ, consistent reads within a transaction read the snapshot established by the first read. For locking reads with search conditions, the documentation states that InnoDB locks the index range scanned, using gap locks or next-key locks. Range predicates therefore lock more than they match.

Under READ COMMITTED, the same page states that locking reads, UPDATE and DELETE lock only index records, and not the gaps before them. The level is set with --transaction-isolation or per transaction with SET TRANSACTION.

That is a real trade rather than a free win. READ COMMITTED gives up repeatable reads inside a transaction, which some application logic quietly depends on. Change it deliberately, per connection or per transaction, and write down which behaviour you gave up.

PostgreSQL names four row lock modes, in strength order

If you are on PostgreSQL the vocabulary differs and the discipline does not. Its explicit locking documentation lists four row-level modes in descending strength: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE.

The two middle modes are the useful ones nobody reaches for. A transaction that needs to prevent deletion and concurrent updates, but does not touch key columns, can take a weaker mode and block fewer neighbours. Strength is a dial, not a switch. Most code reaches for the strongest one by reflex.

The same page documents advisory locks, which are application-defined, can be session or transaction scoped, and are not enforced by the system. They are the right tool for coordinating work that is not a row: a nightly recalculation, a single-writer import, a leader election you did not want to add a service for.

Not enforced by the system is the important clause. An advisory lock protects you only from code that also takes it.

Pessimistic locks belong inside a transaction

Laravel exposes two pessimistic locks on the query builder, and its documentation is specific about how to use them.

sharedLock prevents the selected rows from being modified until your transaction commits. lockForUpdate also prevents them from being selected with another shared lock. Both are recommended inside a transaction, because that is what guarantees the locks are released if anything rolls back.

The failure people ship is taking the lock and then doing the slow thing. Read the row, lock it, call the external service, write, commit: correct, and it serialises your whole checkout on someone else's latency. Lock late. Commit early. Nothing slow inside the window.

The illustrative shape, not runnable as written:

DB::transaction(function () use ($id) { Wallet::lockForUpdate()->find($id)->debit(500); }, attempts: 5);

Deadlock retry absorbs the collision, ordering removes it

Deadlocks are not an error condition to be eliminated. Under concurrency they are expected, and both engines are built to handle them.

PostgreSQL's documentation states that it automatically detects deadlock situations and resolves them by aborting one of the transactions involved. It then gives the actual fix, which is not a setting: the best defence is being certain that all applications acquire locks on multiple objects in a consistent order, taking the most restrictive mode first.

Laravel's side of this is documented on the database page. DB::transaction accepts an attempts argument and retries the closure when a deadlock occurs, rethrowing once attempts are exhausted. Set it, and treat a rising retry count as a signal rather than a solved problem.

The order rule is worth writing into your codebase as a convention. If every code path that touches wallets and ledgers locks the wallet first, deadlocks between those paths become impossible rather than rare. That is a five-line comment in a service class doing more work than any tuning parameter.

The hot row that serialises everything, composited

The details here are composited from ordinary production shapes rather than one system, and the shape is exact.

A hot row is any row that most transactions must touch: a sequence counter, an aggregate balance, a settings record read with a lock, a per-tenant total. Its effect is exactly Chapter 2's arithmetic with a pool size of one. Whatever your worker count, throughput on that path is capped at one transaction per lock hold time.

The first reading is lock wait time attributed per table, taken from the engine's own metrics. The second is the same figure after the hot row is removed from the critical path, by moving the counter to a database sequence, or writing an append-only row per event and aggregating on read, or sharding the counter across a fixed set of rows and summing.

The two readings differ in a structural way rather than by a percentage: contention that concentrated on one object no longer does. That is the finding. Anything about response time needs a separate measurement at the response layer, and Chapter 12 is where you take it.

The objection: wrap it in a transaction to be safe

Transactions are correctness machinery, so the instinct to use more of them feels responsible. The habit that causes trouble is the wide transaction opened out of caution rather than need.

Three things go wrong. The lock window stretches to cover work that never needed protection, including HTTP calls, file writes and template rendering. Queued jobs dispatched inside the transaction can be picked up by a worker before the commit lands, which is why Laravel provides the after_commit behaviour Chapter 7 uses. And a rollback now discards work the user believes succeeded, because the boundary was drawn around too much.

The narrower habit: a transaction covers the smallest set of statements that must succeed or fail together, and nothing else. Everything that can happen before it, happens before it. Everything that can happen after, happens after.

That rule is also the cheapest contention fix available, because it costs no schema change and no new infrastructure. It is a change to where two lines sit.

Chapter summary

Write contention is a queue in front of a row, so Chapter 2's arithmetic applies with a pool size of one, and lock scope is the only part you control: how many rows, for how long, at what strength. MySQL's manual states that InnoDB always locks index records, using a hidden clustered index where none exists, which makes the access path determine the lock, and it states that a search on an unindexed or non-unique column also locks the preceding gap, which is why an insert can block on an update to an unrelated row. Gap locks are purely inhibitive and can co-exist, and the manual notes gap locking is disabled by moving to READ COMMITTED, after which it applies only to foreign-key and duplicate-key checking. REPEATABLE READ is InnoDB's default and locks the index range scanned with gap or next-key locks, while READ COMMITTED locks only index records, a trade that costs repeatable reads inside a transaction. PostgreSQL offers four row lock modes in descending strength plus advisory locks that are not enforced by the system, and its manual names consistent lock ordering, most restrictive mode first, as the real defence against deadlocks. Laravel's sharedLock and lockForUpdate belong inside a transaction so locks release on rollback, and DB::transaction with an attempts argument retries deadlocks and rethrows when exhausted, which absorbs collisions rather than preventing them. And the highest-leverage change is almost always duration: move external calls and rendering outside the transaction, lock late, commit early.

Moving slow work out of the request is the obvious next step, and it relocates the contention rather than removing it. Chapter 6 first handles the layer teams reach for instead, which is the cache, and the invalidation rule that decides whether it helps.