Transactions, ACID & Concurrent Updates
Imagine two people click "Book seat 14C" at the same moment. Or a payment service debits an account and then crashes before crediting the merchant. These are not rare edge cases. On any busy system they happen every day.
Databases give us one main tool for handling them: the transaction. This lesson explains what a transaction is, what the famous ACID letters really promise (and what they do not), and the two standard ways to stop concurrent users from overwriting each other's work.
What Is a Transaction?
A transaction is a group of database operations that the database treats as a single unit. Either all of them take effect, or none of them do.
The classic example is a bank transfer of 100 from Alice to Bob:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';
COMMIT;
Without a transaction, a crash between the two UPDATE statements would leave 100 missing from the world: taken from Alice, never given to Bob. With a transaction, the database guarantees you never see that half-finished state. If anything fails before COMMIT, you (or the database) run ROLLBACK and both changes are undone.
graph LR
B["BEGIN"] --> U1["Debit Alice"]
U1 --> U2["Credit Bob"]
U2 --> C{"All steps OK?"}
C -->|"Yes"| CM["COMMIT<br/>Both changes visible, permanently"]
C -->|"No, error or crash"| RB["ROLLBACK<br/>Neither change happened"]
What ACID Actually Promises
ACID is an acronym for four properties. They are often recited together, but each one promises something quite specific, and each has a common misconception.
| Letter | What it promises | Common misconception |
|---|---|---|
| Atomicity | All the transaction's changes happen, or none do. A failure midway rolls everything back. | "Atomic means other users can't see it or interfere." No, that is isolation. Atomicity is about all-or-nothing on failure. |
| Consistency | A transaction moves the database from one valid state to another, where "valid" means your constraints (NOT NULL, UNIQUE, foreign keys, CHECK) hold. | "The database keeps my data correct." It only enforces the rules you declared. If you never wrote CHECK (balance >= 0), it will happily store a negative balance. |
| Isolation | Concurrent transactions do not step on each other. Ideally each behaves as if it ran alone. | "Isolation is all-or-nothing." It comes in levels, and most databases default to a weaker one than you might assume. |
| Durability | Once COMMIT returns, the change survives a crash or power loss. | "Durable means it's on every replica." Usually it means flushed to this server's disk (via the write-ahead log). Replication is a separate concern. |
Two takeaways for beginners:
- Consistency is partly your job. Use constraints. They are the cheapest bug prevention you will ever write.
- Isolation is where the subtle bugs live. The rest of this lesson is mostly about it.
Durability, briefly
You saw this in How a Database Works: the database writes a log record to the WAL and flushes it to disk before replying "committed". That log-first rule is what the D in ACID rests on.
The Lost-Update Problem
Here is the most common concurrency bug in web applications. It happens with perfectly normal-looking code.
A concert has one seat left. The app's booking logic is:
# Read, decide in the app, then write
seats = db.query_one("SELECT seats_left FROM events WHERE id = %s", event_id)
if seats > 0:
db.execute("UPDATE events SET seats_left = %s WHERE id = %s", seats - 1, event_id)
db.execute("INSERT INTO bookings (event_id, user_id) VALUES (%s, %s)", event_id, user_id)
Now two users, Asha and Ben, click "Book" at the same time:
sequenceDiagram
participant A as Asha's request
participant DB as Database (seats_left = 1)
participant B as Ben's request
A->>DB: SELECT seats_left
DB-->>A: 1
B->>DB: SELECT seats_left
DB-->>B: 1
Note over A: 1 > 0, so book it
Note over B: 1 > 0, so book it
A->>DB: UPDATE seats_left = 0, INSERT booking
B->>DB: UPDATE seats_left = 0, INSERT booking
Note over DB: seats_left = 0, but TWO bookings exist
Both requests read 1, both decided there was room, both wrote 0. The database did exactly what it was told. The seat is now double booked.
The same shape appears everywhere:
- Two withdrawals both read a balance of 100, both decide 80 is affordable, and the account is overdrawn.
- Two admins edit the same product description, and the second save silently wipes out the first.
- Two workers increment a counter by reading it, adding 1 in code, and writing it back, so one increment disappears.
The root cause is always the same: read, decide in application code, then write, with a gap in between where someone else can sneak in.
Wrapping the code in BEGIN ... COMMIT does not fix this on its own under the default isolation level of PostgreSQL or Oracle (read committed). Both transactions can still read 1 before either writes. We need something more.
The simplest fix: let the database do the check
For simple counters, do the check and the change in one atomic statement:
UPDATE events SET seats_left = seats_left - 1
WHERE id = 42 AND seats_left > 0;
-- If this reports 0 rows updated, the event is sold out.
The row is locked while the UPDATE runs, so the second request sees the new value and matches zero rows. Reach for the locking techniques below when the decision is too complex to fit in one WHERE clause.
Fix #1: Pessimistic Locking
Pessimistic locking assumes conflicts are likely, so it locks the row before reading it. Anyone else who wants the same row must wait until you commit.
In SQL, you do this with SELECT ... FOR UPDATE:
BEGIN;
SELECT seats_left FROM events WHERE id = 42 FOR UPDATE; -- locks this row
-- app checks seats_left > 0
UPDATE events SET seats_left = seats_left - 1 WHERE id = 42;
INSERT INTO bookings (event_id, user_id) VALUES (42, 'asha');
COMMIT; -- lock released
with db.transaction():
seats = db.query_one(
"SELECT seats_left FROM events WHERE id = %s FOR UPDATE", event_id)
if seats <= 0:
raise SoldOut()
db.execute("UPDATE events SET seats_left = seats_left - 1 WHERE id = %s", event_id)
db.execute("INSERT INTO bookings (event_id, user_id) VALUES (%s, %s)", event_id, user_id)
Replaying the race: Asha's SELECT ... FOR UPDATE grabs the lock. Ben's identical query blocks. Asha books and commits, the lock is released, and Ben's query finally returns 0. Ben gets "sold out". Correct.
sequenceDiagram
participant A as Asha
participant DB as Database
participant B as Ben
A->>DB: SELECT ... FOR UPDATE
DB-->>A: seats_left = 1 (row locked)
B->>DB: SELECT ... FOR UPDATE
Note over B,DB: Ben waits for the lock
A->>DB: UPDATE, INSERT, COMMIT
DB-->>B: seats_left = 0
Note over B: Sold out, no double booking
Trade-offs:
- Simple to reason about, and conflicts are handled without retries.
- Waiting transactions hold connections, so long-held locks hurt throughput.
- Two transactions locking rows in different orders can deadlock. The database detects this and aborts one of them, so your code still needs to handle that error.
- Never hold a lock while doing something slow, like calling a payment gateway or waiting on the user.
Fix #2: Optimistic Locking
Optimistic locking assumes conflicts are rare. It takes no lock while reading. Instead it detects, at write time, whether someone else changed the row in the meantime, and if so, it retries.
The usual trick is a version column:
ALTER TABLE events ADD COLUMN version INT NOT NULL DEFAULT 0;
Read the row along with its version. When writing, only update if the version is still the one you read, and bump it:
-- Read (no lock)
SELECT seats_left, version FROM events WHERE id = 42;
-- returns seats_left = 1, version = 7
-- Write: succeeds only if nobody changed the row since we read it
UPDATE events
SET seats_left = 0, version = 8
WHERE id = 42 AND version = 7;
-- 1 row updated -> we won
-- 0 rows updated -> someone else got there first, so retry
def book_seat(event_id, user_id, max_retries=3):
for _ in range(max_retries):
seats, version = db.query_one(
"SELECT seats_left, version FROM events WHERE id = %s", event_id)
if seats <= 0:
raise SoldOut()
with db.transaction():
updated = db.execute(
"UPDATE events SET seats_left = %s, version = %s "
"WHERE id = %s AND version = %s",
seats - 1, version + 1, event_id, version)
if updated == 1:
db.execute("INSERT INTO bookings (event_id, user_id) VALUES (%s, %s)",
event_id, user_id)
return
# Lost the race: loop and re-read the fresh state
raise TooMuchContention()
In the race, Asha and Ben both read version = 7. Asha's update matches and sets version = 8. Ben's update says WHERE version = 7, matches zero rows, and Ben's code re-reads, sees seats_left = 0, and reports "sold out".
Many ORMs support this out of the box: JPA/Hibernate @Version, Rails lock_version, Django via packages, and so on. HTTP APIs use the same idea with ETag and If-Match headers.
Pessimistic or Optimistic: Which One?
Pessimistic (FOR UPDATE) | Optimistic (version column) | |
|---|---|---|
| Assumes | Conflicts are common | Conflicts are rare |
| On conflict | Second transaction waits | Second transaction fails and retries |
| Cost when no conflict | Small locking overhead | Almost free |
| Cost under heavy contention | Queueing, but steady progress | Many wasted retries, possibly livelock |
| Good for | Hot rows: the last few seats of a big concert, a shared wallet balance, inventory for a flash sale | Mostly independent edits: user profiles, documents, admin forms, carts |
| Watch out for | Deadlocks, long-held locks, locks across network calls | Forgetting the retry loop, unbounded retries |
A rough rule: if many users fight over the same few rows, lock pessimistically (or use the atomic UPDATE ... WHERE trick). If users mostly touch their own rows and clashes are an occasional surprise, go optimistic.
War Story (illustrative)
A flash-sale site used optimistic locking on its inventory row for a hot product. At launch, 5,000 requests per second raced for the same row. Almost every update hit 0 rows updated, retried, and failed again. The database was busy, yet almost nothing sold. Switching that one code path to a single atomic UPDATE inventory SET stock = stock - 1 WHERE sku = ? AND stock > 0 let the database serialize the writes itself, and orders flowed again. Optimistic locking was the right default for the rest of the site, just not for its single hottest row.
A First Look at Isolation Levels
Locking is something you ask for explicitly. Isolation levels are the database's built-in setting for how much concurrent transactions can see of each other. Stronger levels prevent more anomalies but cost more in performance or retries.
Three anomalies are enough to start:
- Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed a change in between.
- Lost update: the seat booking bug above.
- Write skew: two transactions each read overlapping data, each make a decision that is fine on its own, and they write different rows, so together they break a rule. Example: two on-call doctors each see "two doctors on call, so I can go off call", and both do, leaving nobody on call.
| Isolation level | Non-repeatable read | Lost update | Write skew | Notes |
|---|---|---|---|---|
| Read committed | Possible | Possible | Possible | Default in PostgreSQL, Oracle, SQL Server |
| Repeatable read | Prevented | Prevented in PostgreSQL (one transaction aborts); possible in MySQL InnoDB for plain read-then-write | Possible | Default in MySQL InnoDB |
| Serializable | Prevented | Prevented | Prevented | Behaves as if transactions ran one at a time; expect serialization errors you must retry |
You set it per transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT; -- may fail with a serialization error; retry the whole transaction
Know your default
Most applications run on the default level and never think about it. On PostgreSQL that is read committed, which permits the lost-update bug. Either use explicit locking or atomic updates on the code paths that matter, or raise the isolation level for those transactions and add retries.
The exact behavior differs between databases, and the details depend on how each one implements snapshots under the hood. For the full story see Isolation Levels and MVCC.
Where This Shows Up in System Design
Interviewers love these problems because concurrency bugs are expensive. Some common patterns:
- Ticket and seat booking. Hold a seat with a short-lived reservation row, protected by
FOR UPDATEor a unique constraint on(event_id, seat_id), then confirm after payment. AUNIQUEconstraint is a wonderful last line of defense: even if your logic races, the database rejects the second booking. See Ticket Booking and Hotel Booking. - Payments and wallets. Debit and credit in the same transaction so money is never created or destroyed. Lock the account rows (in a consistent order, to avoid deadlocks), add
CHECK (balance >= 0), and use idempotency keys so a retried request does not charge twice. See Payment System. - E-commerce inventory. Decrement stock with an atomic conditional
UPDATE, and create the order in the same transaction. For very hot items, some designs move the counter to Redis or a queue in front of the database. - Across services. A single database transaction cannot span two microservices or two databases. That is where sagas, the outbox pattern, and idempotency take over, which is a topic for later modules.
Summary
| Idea | One-line version |
|---|---|
| Transaction | A group of operations that commit together or not at all |
| Atomicity | All-or-nothing on failure, not "invisible to others" |
| Consistency | Your declared constraints always hold, so declare them |
| Isolation | Concurrent transactions don't interfere, to a configurable degree |
| Durability | Committed means it survives a crash (thanks to the WAL) |
| Lost update | Read, decide, write, with another writer in the gap |
| Pessimistic locking | SELECT ... FOR UPDATE, others wait; best for hot rows |
| Optimistic locking | Version column + retry; best when conflicts are rare |
| Isolation levels | Read committed < repeatable read < serializable; know your default |
Where to Go Next
- Isolation Levels: every anomaly, how each database really behaves, and Serializable Snapshot Isolation
- MVCC: how databases let readers and writers avoid blocking each other with row versions
- Write-Ahead Logging: the machinery behind durability
- Ticket Booking and Payment System: these ideas applied end to end