A Guide to Database Concurrency

Database concurrency describes how a database handles multiple transactions reading and modifying data at the same time. Without appropriate concurrency control, individually valid operations can interact in ways that produce incorrect or inconsistent results.

Four important concurrency anomalies are lost updates, dirty reads, non-repeatable reads, and phantom reads. Databases prevent or detect these problems through transaction isolation, MVCC, locking, and conflict-detection strategies such as pessimistic and optimistic locking.

Table of Contents

• Lost Update
  • Preventing Lost Updates
• Dirty Read
  • Why Dirty Reads Are Dangerous
• Non-Repeatable Read
  • When Non-Repeatable Reads Matter
• Phantom Read
  • Phantom vs Non-Repeatable Read
• Pessimistic Locking
  • SELECT FOR UPDATE
  • Pessimistic Locking Trade-Offs
• Optimistic Locking
  • Version-Based Conflict Detection
  • Retries and Conflict Handling
• Isolation Levels and Concurrency Anomalies
• How MVCC Changes Concurrency
• Prefer Atomic Database Operations
• Deadlocks
• Production Design Example
• Choosing a Concurrency Strategy
• Common Database Concurrency Mistakes
• Production Checklist
• Frequently Asked Questions
  • Does Using a Transaction Prevent Race Conditions?
  • What Is the Difference Between a Lost Update and a Dirty Read?
  • Is Optimistic or Pessimistic Locking Better?
  • Does MVCC Mean the Database Does Not Use Locks?
  • Should Every Transaction Use the Highest Isolation Level?
• Conclusion
  • More Articles to Read

Lost Update

A lost update occurs when two transactions read the same value, independently calculate new values, and one write overwrites the effect of the other.

Consider an account with a $1,000 balance.

Initial balance = $1,000

Transaction A: deposit $200
Transaction B: withdraw $100

Both transactions read the original value:

Transaction A                 Transaction B

READ $1,000                   READ $1,000
     ↓                             ↓
calculate                       calculate
1000 + 200                      1000 - 100
     ↓                             ↓
$1,200                           $900

Transaction A writes $1,200. Transaction B later writes $900.

Initial       $1,000
A writes      $1,200
B writes        $900

Final value     $900

Correct value $1,100

Transaction B did not intentionally remove Transaction A's deposit. It simply calculated its result from stale state and then replaced the newer value.

This pattern commonly appears when applications perform a read-modify-write sequence:

account = get_account(account_id)

new_balance = account.balance - amount

update_account(
    account_id,
    balance=new_balance,
)

There is a concurrency window between the read and the write.

Preventing Lost Updates

Several approaches can prevent or detect lost updates.

One is an atomic database update:

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

The calculation happens against the row inside the database rather than reading the value into the application and later replacing it.

Another approach is pessimistic locking:

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

A third is optimistic concurrency control:

UPDATE accounts
SET balance = 900,
    version = version + 1
WHERE id = 1
  AND version = 7;

The appropriate solution depends on whether the operation can be expressed atomically, how often conflicts occur, and how expensive retries are.

Dirty Read

A dirty read occurs when one transaction reads data written by another transaction that has not committed yet.

Consider product inventory:

Committed stock = 10

Transaction A temporarily changes it:

BEGIN;

UPDATE products
SET stock = 3
WHERE id = 42;

-- not committed yet

If Transaction B can read that uncommitted value:

Transaction A                Transaction B

stock = 10

WRITE stock = 3
not committed
                             READ stock = 3
                             ↓
                             "Only 3 left!"

Transaction A then fails:

Transaction A
ROLLBACK

stock returns to 10

Transaction B acted on a value that never became part of the committed database state.

Why Dirty Reads Are Dangerous

A dirty read can escape beyond the database.

Transaction B might:

• send a low-stock notification;
• reject a customer order;
• calculate a price;
• publish an event;
• update another system;
• make another business decision.

Rolling back Transaction A does not automatically reverse those external effects.

Uncommitted database value
          ↓
Another transaction reads it
          ↓
External side effect
          ↓
Original transaction rolls back
          ↓
External side effect remains

This is why dirty reads are prohibited by commonly used isolation levels such as Read Committed.

Non-Repeatable Read

A non-repeatable read occurs when one transaction reads the same row twice and receives different committed values because another transaction modified the row between those reads.

Suppose a reporting transaction reads a product price:

Transaction A

SELECT price
FROM products
WHERE id = 42;

→ $500

Transaction B updates the same product:

UPDATE products
SET price = 650
WHERE id = 42;

COMMIT;

Transaction A reads the row again:

Transaction A

First read  → $500

Transaction B commits $650

Second read → $650

Both values were committed and individually valid. The problem is that one transaction observed two different versions of what it may have expected to be a stable value.

When Non-Repeatable Reads Matter

Many transactions do not require repeated reads to return identical values.

Problems appear when several calculations inside one logical operation assume a stable database view.

For example:

Build monthly report

1. Read product price → $500
2. Calculate aggregate
3. Another transaction changes price → $650
4. Read product details → $650

One report now contains values
derived from different states.

A stronger isolation level or an explicit locking strategy may be appropriate when a stable transaction-level view is required.

Phantom Read

A phantom read occurs when a transaction repeats a predicate query and the set of matching rows changes because another transaction inserted, deleted, or modified rows that affect the predicate.

Suppose Transaction A runs:

SELECT id, total_cents
FROM orders
WHERE total_cents > 100000;

The result contains two orders:

Order 811 → $1,250
Order 814 → $1,800

2 matching rows

Transaction B inserts another qualifying order:

INSERT INTO orders (
    id,
    total_cents
)
VALUES (
    819,
    140000
);

COMMIT;

Transaction A repeats the exact query:

Order 811 → $1,250
Order 814 → $1,800
Order 819 → $1,400

3 matching rows

The phantom is not necessarily a changed version of an existing row. It is a change in the result set defined by a predicate.

Phantom vs Non-Repeatable Read

The distinction is easiest to see by comparing what changes.

For example:

Non-repeatable read:

Query 1 → Product 42 costs $500
Query 2 → Product 42 costs $650


Phantom read:

Query 1 → 2 orders match
Query 2 → 3 orders match

Different database engines and isolation implementations prevent these anomalies using different combinations of snapshots, locks, predicate protection, and serialization conflict detection.

Pessimistic Locking

Pessimistic locking coordinates access before a conflicting modification occurs.

The strategy assumes that a conflict is likely or important enough that competing operations should wait rather than independently proceed from the same state.

Consider the original account example:

Balance = $1,000

Transaction A wants +$200
Transaction B wants -$100

Transaction A first acquires a row lock.

Transaction A
     ↓
Acquire lock
     ↓
Read $1,000
     ↓
Transaction B requests lock
     ↓
B WAITS
     ↓
A writes $1,200
     ↓
A commits
     ↓
Lock released
     ↓
B reads fresh $1,200
     ↓
B writes $1,100

The conflicting read-modify-write operations no longer overlap.

SELECT FOR UPDATE

In PostgreSQL and many relational databases, SELECT ... FOR UPDATE can lock selected rows for modification.

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance = balance + 200
WHERE id = 1;

COMMIT;

A conflicting transaction attempting to lock the same row normally waits until the first transaction releases its lock.

The critical property is that the second transaction reads the current value after gaining the required access rather than calculating from stale state.

Pessimistic Locking Trade-Offs

Pessimistic locking provides straightforward coordination but reduces concurrency around locked resources.

Long transactions can create queues:

Lock owner
    ↓
Transaction takes 3 seconds
    ↓
Waiter 1
Waiter 2
Waiter 3
Waiter 4
Waiter 5

It can also create deadlocks when transactions acquire multiple resources in conflicting orders.

Pessimistic locking works best when:

• conflicts are common;
• retrying work is expensive;
• a resource must be exclusively coordinated;
• transactions can remain short;
• lock contention is bounded.

For a broader discussion of lock behavior and transactions, see Database Locks and Transactions.

Optimistic Locking

Optimistic locking allows concurrent operations to proceed without holding a database lock across the complete read-modify-write workflow.

Instead, the write verifies that the state originally read is still current.

Consider:

Account balance = $1,000
Version         = 7

Both transactions read Version 7:

Transaction A                 Transaction B

READ $1,000 v7                READ $1,000 v7

calculate $1,200              calculate $900

Transaction A writes first:

$1,000 v7
    ↓
$1,200 v8

Transaction B then tries to update Version 7, but that version is no longer current.

Expected version = 7
Current version  = 8

→ conflict

Instead of silently replacing $1,200 with $900, the database operation changes zero rows and the application detects the conflict.

Version-Based Conflict Detection

A typical implementation stores a version column:

SELECT balance, version
FROM accounts
WHERE id = 1;

The application receives:

balance = 1000
version = 7

The update includes the original version:

UPDATE accounts
SET balance = 1200,
    version = version + 1
WHERE id = 1
  AND version = 7;

If the row is still Version 7:

1 row updated
→ success

If another transaction already changed it:

0 rows updated
→ concurrency conflict

The affected-row count is therefore part of the correctness logic and must not be ignored.

Retries and Conflict Handling

After detecting a conflict, an application can reread the current state and decide what to do.

Conflict
   ↓
Read Version 8
   ↓
Recalculate operation
   ↓
Attempt UPDATE WHERE version = 8

Possible responses include:

• retry automatically;
• return a conflict to the caller;
• merge compatible changes;
• cancel the operation.

Retries must be bounded. Under heavy contention, immediate unlimited retries can create additional load:

High contention
      ↓
More conflicts
      ↓
More retries
      ↓
More database traffic
      ↓
Even more conflicts

Retrying a database transaction also requires care when the surrounding workflow performs external side effects such as payments, emails, or message publication.

Isolation Levels and Concurrency Anomalies

SQL transaction isolation levels describe which concurrency effects can become visible to transactions, but exact guarantees vary between database engines.

A useful conceptual comparison is:

This table should not be treated as a substitute for database-specific documentation.

For example, PostgreSQL implements Repeatable Read using snapshot isolation and prevents several anomalies beyond the minimum behavior commonly associated with the SQL isolation-level names.

Serializable does not necessarily mean the database simply places locks around everything. A database can detect unsafe concurrent execution and abort one transaction instead.

Transaction A ─┐
               ├→ serialization conflict
Transaction B ─┘
                       ↓
                one transaction fails
                       ↓
                     retry

The application must therefore understand not only what an isolation level prevents but also what failures it can produce.

How MVCC Changes Concurrency

Many databases use Multi-Version Concurrency Control (MVCC) so readers can access an appropriate version of data while another transaction creates a newer version.

Suppose:

Version 1
price = $500

Version 2
price = $650

An older transaction may continue reading Version 1 while a newer transaction can see Version 2 according to its snapshot.

Transaction A snapshot
        ↓
     Version 1

Transaction B
        ↓
creates Version 2

This significantly reduces reader-writer blocking.

MVCC does not mean concurrency problems disappear. Writer-writer conflicts, stale application state, serialization anomalies, long-running transactions, and explicit locks still need to be handled.

Row versions, snapshots, visibility rules, PostgreSQL tuple versions, VACUUM, and long-running transactions are covered in What Is MVCC?.

Prefer Atomic Database Operations

Before adding explicit locking, check whether the database can express the required state transition atomically.

Consider inventory:

Read stock
    ↓
Application checks stock > 0
    ↓
Application calculates stock - 1
    ↓
UPDATE stock

This creates a concurrency window.

The operation can often be expressed as one conditional statement:

UPDATE products
SET stock = stock - 1
WHERE id = $1
  AND stock > 0;

The application then checks the affected-row count:

1 row changed
→ item reserved

0 rows changed
→ no stock available

Another example is incrementing a counter.

Avoid:

SELECT counter → 10
application: 10 + 1
UPDATE counter = 11

Prefer:

UPDATE counters
SET value = value + 1
WHERE id = $1;

Atomic statements reduce the amount of application-side concurrency coordination required and are often both simpler and faster.

Deadlocks

Pessimistic locking can produce a deadlock when transactions wait for resources held by each other.

Transaction A
locks Account 1
      ↓
waits for Account 2
      ↑
      │
waits for Account 1
      ↑
Transaction B
locks Account 2

Neither transaction can continue naturally.

Databases detect such cycles and abort one transaction so the other can proceed.

One of the most useful prevention techniques is consistent lock ordering:

Risky:

A: lock Account 1 → Account 2
B: lock Account 2 → Account 1


Better:

A: lock Account 1 → Account 2
B: lock Account 1 → Account 2

Applications should expect deadlock failures in sufficiently concurrent systems and have a bounded retry strategy when repeating the transaction is safe.

Production Design Example

Consider a wallet service where users can transfer funds between accounts.

The initial implementation performs:

source = get_account(source_id)
target = get_account(target_id)

source.balance -= amount
target.balance += amount

save(source)
save(target)

Load testing exposes lost updates when several transfers affect the same account concurrently.

The transfer is moved into a database transaction:

BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;

UPDATE accounts
SET balance = balance - $3
WHERE id = $1
  AND balance >= $3;

UPDATE accounts
SET balance = balance + $3
WHERE id = $2;

COMMIT;

Accounts are locked in deterministic ID order to reduce deadlock risk.

Transfer A:
Account 10 → Account 20

lock order:
10 → 20


Transfer B:
Account 20 → Account 10

lock order:
10 → 20

The transfer transaction is intentionally short. It does not call notification, analytics, or payment services while locks are held.

Profile editing in the same application has very different contention characteristics. Multiple users rarely edit the exact same profile simultaneously, and an editing screen can remain open for minutes.

Holding a database lock for the entire editing session would be inappropriate, so profile updates use optimistic locking:

UPDATE profiles
SET display_name = $1,
    bio = $2,
    version = version + 1
WHERE user_id = $3
  AND version = $4;

Two concurrency strategies therefore coexist:

Money transfer
High correctness requirement
Potential contention
Short transaction
        ↓
Pessimistic row locking


Profile editing
Low conflict frequency
Long time between read and write
        ↓
Optimistic version check

Production monitoring includes:

• transaction duration;
• lock wait duration;
• deadlock count;
• lock timeout count;
• serialization failures;
• optimistic conflict rate;
• retry count;
• aborted transactions;
• hot rows;
• connection-pool utilization.

Suppose metrics show:

Database CPU:              42%
Storage utilization:       normal
Connection utilization:    58%
Transfer p99:             1.8 sec
Lock wait p99:            1.7 sec

The database is not CPU-bound or storage-bound. Almost all of the transaction latency comes from lock contention.

Increasing the connection pool or adding more application instances would not remove the hot-resource constraint. It could create more transactions waiting for the same locks.

The optimization target is the concurrency model and the contested data, not general database capacity.

Choosing a Concurrency Strategy

A practical decision process starts with the state transition rather than with a preferred locking technique.

Conflict frequency is particularly important.

Optimistic concurrency under a 0.1% conflict rate can be efficient because almost every operation succeeds immediately.

At a 50% conflict rate, repeated retries can become expensive.

Pessimistic locking reverses the trade-off: it avoids wasted conflicting writes but makes transactions wait before they can proceed.

The best strategy is therefore the one that protects the required invariant with acceptable contention, retry cost, latency, and operational complexity.

Common Database Concurrency Mistakes

• Assuming a transaction automatically prevents every race condition. Isolation level and access pattern still matter.
• Performing read-modify-write in application code when one atomic SQL statement would work.
• Ignoring affected-row counts. Zero rows can represent an optimistic concurrency conflict or failed conditional update.
• Using stale values in unconditional UPDATE statements. This can cause lost updates.
• Using pessimistic locks for long user interactions. Database locks should not remain held while a person edits a form.
• Holding locks while calling external services. Network latency extends contention.
• Retrying conflicts without a limit. Heavy contention can create retry storms.
• Retrying external side effects blindly. Database retries can duplicate payments, messages, or other actions.
• Acquiring multiple locks in inconsistent order. This increases deadlock risk.
• Assuming MVCC eliminates writer conflicts. Multiple versions mainly reduce reader-writer interference.
• Using stronger isolation without understanding failure behavior. Serializable execution can intentionally abort transactions.
• Increasing database connections to solve lock contention. More concurrent waiters can worsen the problem.

Production Checklist

• Identify the business invariant protected by each important transaction.
• Prefer atomic SQL operations for simple state changes.
• Choose transaction isolation deliberately.
• Use optimistic concurrency where conflicts are relatively rare.
• Use pessimistic locking only where coordinated access is required.
• Keep transactions short.
• Avoid external network calls while database locks are held.
• Check affected-row counts for conditional updates.
• Acquire multiple locks in a consistent order where practical.
• Define bounded retry policies for deadlocks and serialization failures.
• Add backoff or jitter where retry contention can synchronize callers.
• Make external side effects idempotent when retries are possible.
• Monitor lock waits rather than only query execution time.
• Track optimistic conflict rates.
• Track deadlocks and serialization failures.
• Identify hot rows and frequently contested resources.
• Load-test operations with realistic contention, not only independent records.

Frequently Asked Questions

Database concurrency problems are often subtle because every individual query can look correct when tested alone. The failure appears only when multiple valid operations overlap in a particular order.

Does Using a Transaction Prevent Race Conditions?

No. A transaction provides an atomic unit of work, but concurrent transactions can still interact in problematic ways depending on the isolation level and statements used.

A read followed by an unconditional write can still be vulnerable to a lost update unless the database or application adds appropriate concurrency control.

What Is the Difference Between a Lost Update and a Dirty Read?

A lost update occurs when one concurrent write overwrites the effect of another.

A dirty read occurs when a transaction reads another transaction's uncommitted data and may act on a value that is later rolled back.

Is Optimistic or Pessimistic Locking Better?

Neither is universally better.

Optimistic locking is attractive when conflicts are uncommon because operations do not wait for a long-lived lock. Pessimistic locking can be more appropriate when conflicts are frequent and work should be serialized before modification.

Does MVCC Mean the Database Does Not Use Locks?

No. MVCC allows transactions to read different row versions and reduces many reader-writer conflicts, but concurrent writes still require coordination.

Databases also use locks for explicit locking statements, schema operations, constraints, and other internal operations.

Should Every Transaction Use the Highest Isolation Level?

Not automatically. Stronger isolation can require more coordination, conflict detection, transaction retries, or blocking depending on the database.

The isolation level should provide the guarantees required by the operation without introducing unnecessary contention or complexity.

Conclusion

Database concurrency is fundamentally about preserving correctness while allowing useful work to execute in parallel. Lost updates, dirty reads, non-repeatable reads, and phantom reads show different ways concurrent transactions can interact unexpectedly.

Atomic database operations are often the simplest defense. Pessimistic locking coordinates conflicting operations before they modify shared state, while optimistic locking allows concurrency and detects stale writes afterward. Isolation levels and MVCC provide additional control over what transactions can observe.

The core principle is: define what must remain correct under simultaneous execution, then use the simplest atomic operation, isolation guarantee, or locking strategy that protects that invariant without creating unnecessary contention.

More Articles to Read

• Database Transactions
• What Is Database Indexing?
• Database Best Practices for Scalable Applications