A simple, practical guide using PostgreSQL
Concurrency is one of those topics that looks small on the surface but sits at the core of real-world systems.
It’s also why interviewers love asking:
“How do you handle concurrency in a database?”
This question tests not just database knowledge, but how you think about correctness, scale, and failure.
In this article, we’ll break concurrency down step by step, using PostgreSQL examples, simple explanations, and real-world tradeoffs.
What Does Concurrency Mean in Databases?
Concurrency means multiple transactions accessing or modifying the same data at the same time.
This happens constantly in real systems:
- Two users booking the last available seat
- Two payments deducted from the same wallet
- Two API requests updating the same order
If concurrency is not handled properly, the system can:
- Lose updates
- Produce inconsistent data
- Charge users incorrectly
At scale, these are not edge cases — they are guaranteed to happen.
Why Concurrency Is Hard
Databases are shared systems.
Many transactions run in parallel, and their operations can interleave in unpredictable ways.
A Simple Race Condition Example
Initial balance = 100
Two transactions execute at the same time:
T1 reads balance → 100
T2 reads balance → 100
T1 withdraws 70 → writes 30
T2 withdraws 50 → writes 50
❌ Final balance = 50
✅ Correct balance = 30
This happens because both transactions acted on stale data.
The Four Classic Concurrency Problems
Almost every concurrency issue falls into one of these categories:
1. Lost Update
One transaction overwrites another transaction’s update without realizing it.
2. Dirty Read
A transaction reads data that another transaction has modified but not yet committed.
3. Non-Repeatable Read
The same row returns different values within a single transaction because another transaction committed changes in between.
4. Phantom Read
Re-running the same query returns new rows that were inserted by another transaction.
Understanding these problems helps you choose the right isolation and locking strategy.
Transactions: The Foundation of Concurrency Control
Databases use transactions to group multiple operations into a single logical unit.
Transactions follow the ACID properties:
| Property | Meaning |
|---|---|
| Atomicity | All operations succeed or none do |
| Consistency | Data always stays valid |
| Isolation | Transactions don’t interfere incorrectly |
| Durability | Committed data is permanent |
👉 Concurrency control is primarily about Isolation.
Isolation Levels in PostgreSQL
Isolation levels define how much interaction is allowed between concurrent transactions.
| Isolation Level | What It Guarantees |
|---|---|
| Read Committed (default) | No dirty reads |
| Repeatable Read | Stable snapshot during transaction |
| Serializable | Transactions behave as if run one by one |
Important Insight
Higher isolation levels provide stronger correctness guarantees, but they reduce concurrency and throughput.
Lower isolation levels improve performance, but allow more anomalies.
PostgreSQL Example: Read Committed Behavior
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- another transaction commits here
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
In PostgreSQL’s default isolation level, the two SELECTs may return different values.
This is expected and acceptable for many applications.
Locking: Controlling Access to Shared Data
Locking is one of the oldest and most reliable ways to handle concurrency.
The idea is simple:
When one transaction is modifying data, other transactions must wait.
Row-Level Locking in PostgreSQL
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;
This locks only the selected row, not the entire table.
Other rows remain accessible, which allows the database to stay performant under load.
Pessimistic Locking
Pessimistic locking assumes that conflicts are likely, so it locks data before modifying it.
This approach prioritizes correctness over throughput.
Example (PostgreSQL)
BEGIN;
SELECT * FROM inventory
WHERE product_id = 10
FOR UPDATE;
UPDATE inventory SET stock = stock - 1;
COMMIT;
✅ Pros of Pessimistic Locking
Pessimistic locking provides strong safety guarantees.
Once a row is locked, no other transaction can modify it, which makes reasoning about correctness much simpler.
This approach is ideal for systems where even a single incorrect update is unacceptable, such as banking or payments.
❌ Cons of Pessimistic Locking
Because locks block other transactions, pessimistic locking can reduce system throughput under high traffic.
If many transactions compete for the same data, latency increases.
It also increases the risk of deadlocks, where two transactions wait on each other and one must be aborted.
📌 Common Use Cases for Pessimistic Locking
Pessimistic locking is commonly used in financial transactions, seat reservations, and inventory systems where contention is high and correctness is critical.
Optimistic Locking
Optimistic locking assumes that conflicts are rare and avoids locking data upfront.
Instead, it checks whether the data has changed right before updating it.
This is usually implemented using a version number.
Example (PostgreSQL)
UPDATE inventory
SET stock = stock - 1,
version = version + 1
WHERE id = 10 AND version = 3;
If no rows are updated, a conflict occurred and the operation must be retried.
✅ Pros of Optimistic Locking
Optimistic locking allows much higher concurrency because transactions do not block each other.
Reads remain fast, and the system scales well with increasing traffic.
This makes it a great fit for modern web applications, where reads heavily outnumber writes.
❌ Cons of Optimistic Locking
Optimistic locking requires retry logic in the application layer, which adds complexity.
When contention increases, retries can become frequent and hurt latency.
In highly contested systems, optimistic locking may perform worse than locking.
📌 Common Use Cases for Optimistic Locking
Optimistic locking is widely used in REST APIs, e-commerce catalogs, social platforms, and general CRUD-based systems.
MVCC: PostgreSQL’s Core Concurrency Model
PostgreSQL uses Multi-Version Concurrency Control (MVCC).
Instead of overwriting rows:
- Each update creates a new version
- Readers see a consistent snapshot
How MVCC Works
Time →
Row V1 → Row V2 → Row V3
Readers see snapshot
Writers do not block readers
✅ Pros of MVCC
MVCC allows readers and writers to operate simultaneously, dramatically improving performance under concurrent workloads.
It reduces lock contention and simplifies application logic, especially for read-heavy systems.
❌ Cons of MVCC
Because old row versions are retained, MVCC can increase disk usage.
If cleanup processes like VACUUM are not tuned correctly, performance may degrade over time.
📌 Common Use Cases for MVCC
MVCC is ideal for general-purpose databases, dashboards, analytics systems, and high-traffic APIs.
Deadlocks and Retry Logic
Deadlocks occur when two transactions wait on each other indefinitely.
PostgreSQL automatically detects deadlocks and aborts one transaction.
However, the application must retry safely.
Why Retry Logic Matters
Retries turn concurrency failures into temporary, invisible events rather than user-facing errors.
Well-designed systems treat deadlocks and serialization failures as expected behavior.
Common Patterns for Handling Deadlocks
- Automatic retries with exponential backoff
- Idempotent APIs to ensure retries are safe
- Short-lived transactions to reduce lock duration
Interview Perspective: Junior vs Senior vs Staff Engineer Answers
❓ Question
How do you handle concurrency in databases?
👶 Junior Engineer Answer
“We use transactions and locks so that multiple users don’t update the same data at the same time.”
This answer shows basic understanding but lacks depth, tradeoffs, and real-world awareness.
👨💻 Senior Engineer Answer
“Concurrency is handled using transactions and isolation levels. In PostgreSQL, I use row-level locks with
SELECT FOR UPDATEfor critical updates, and optimistic locking for high-throughput paths. PostgreSQL’s MVCC helps reduce reader-writer contention.”
This answer shows practical experience and database-specific knowledge.
🧠 Staff Engineer Answer
“Concurrency is a balance between correctness, throughput, and failure handling. PostgreSQL’s MVCC lets readers scale without blocking writers. For high-risk paths like payments, I use pessimistic locking with retries. For most web workloads, optimistic locking scales better. I also design APIs to be idempotent and assume retries as a normal part of the system.”
This answer demonstrates system-level thinking, not just database knowledge.
Choosing the Right Strategy
| Scenario | Recommended Approach |
|---|---|
| Payments | Pessimistic locking + retries |
| Inventory | Optimistic locking |
| Analytics | Read committed |
| High traffic APIs | MVCC + optimistic locking |
Final Takeaway
Concurrency is not just a database concern — it is a system design responsibility.
Strong systems:
- Expect conflicts
- Handle retries gracefully
- Choose tradeoffs intentionally
- Protect correctness first
Concurrency separates code that works from systems that scale.