Locks, Latches & Transactions

The board that draws latches and locks side by side, the pair every “why is my database stuck” ticket confuses.

Before you start. The last lesson watched the database plan how to answer a query, the question your app sends it, before reading a single row (one record, such as one account). This lesson is about what happens once that plan runs and meets every other query touching the same rows at the same time.

The idea in 30 seconds

A lock guards your data - one record, such as an account - for as long as your whole transfer runs. A latch guards the engine’s own bookkeeping for microseconds. Different animal, different lifetime, and only one of them is ever what “the database is stuck” means.
Two transfers can each hold what the other one is waiting for, forever. That’s a deadlock, and nobody has to make a mistake for one to form.
The database doesn’t prevent deadlocks, it watches for transfers stuck waiting on each other, kills one side, and hands your app a retry. That’s the whole fix.
A transfer can read truthfully, write only its own rows, take no lock anyone else needed - and still leave a broken rule behind. That’s write skew, and the part of the database that watches for deadlocks never sees it.
How much of that a database blocks is one dial, the isolation level, and turning it all the way up used to leave too many transfers waiting on each other until a 2008 paper made it cheap.

Read the story · Skip to the numbers