LESSON 5.4
Transactions and constraints
ACID in plain words, the anomalies isolation levels allow, and how constraints and conditional writes keep data correct under concurrency.
6 min read
Interviews love problems where two people want the same thing at once: the last ticket, the same username, a bank balance. Getting these right is mostly about knowing what the database guarantees and using it well.
ACID in plain words
A transaction groups several reads and writes into one unit. Relational databases promise four things about it:
- Atomicity. All of its writes happen, or none do. A crash halfway leaves no half-finished transfer.
- Consistency. Constraints you declared (unique, foreign keys, checks) hold after every transaction.
- Isolation. Concurrent transactions don't see each other's half-done work. How strictly is a setting, covered next.
- Durability. Once committed, it survives a crash, because it's on disk (and often on replicas).
Isolation levels and their anomalies
Full isolation is expensive, so databases offer levels that allow some anomalies in exchange for speed.
| Level | Prevents | Still allows |
|---|---|---|
| Read committed (Postgres default) | Reading uncommitted data | Lost updates and non-repeatable reads, unless you use locks or conditions |
| Repeatable read / snapshot | Non-repeatable reads; Postgres also detects lost updates | Write skew |
| Serializable | Everything: results match some one-at-a-time order | Some transactions abort under contention and must retry |
You don't need to memorize the table. What matters is recognizing the lost update: two transactions read the same value, compute from it, and the second write erases the first. It's the bug behind oversold tickets and wrong balances.
Four tools against races
- Constraints. A
uniqueindex on usernames means two simultaneous signups for "ana" can't both succeed; the second gets an error. The database is the referee. - Conditional (atomic) updates. Put the check inside the write:
UPDATE seats SET status = 'held' WHERE id = 7 AND status = 'available'. Exactly one caller changes a row; the rest see zero rows updated. - Pessimistic locks.
SELECT ... FOR UPDATElocks the rows you read until commit, so others wait. Simple and strong; under heavy contention, waiting becomes the bottleneck. - Optimistic concurrency. Read a
versionwith the row, and update onlyWHERE version = :read. If someone else got there first, re-read and retry. Great when conflicts are rare.
Conditional update
- One statement
- No waiting
- Only works when the rule fits in a WHERE clause
Pick it when: Holding seats, decrementing stock, claiming jobs.
Row lock (FOR UPDATE)
- Any logic between read and write
- Waiting and deadlock risk under contention
Pick it when: Short multi-step changes to a few rows.
Optimistic version check
- No locks held
- Works across a user's edit session
- Retries under contention
Pick it when: Editing documents and settings, low contention.
When the data spans services or shards
Transactions are easy inside one database. Across two services or shards they get hard: a payment service and an inventory service can't share one transaction. Module 20 covers the patterns for that (sagas, the outbox, two-phase commit). For now, a strong design keeps data that must change together in the same database whenever it can.
Step through a prepared transaction, then revive its coordinator to finish one durable decision.
Try it
Event Ticketing (Ticketmaster) HLD
Hold seats under heavy contention without ever selling one twice.