What Happens When Two Users Update the Same Record at the Exact Same Time?

Both Requests Succeeded. One User’s Change Disappeared.#

Two support agents open the same ticket:

Current status: open
Current owner:  null
Version:        7

At almost the same time:

Agent A assigns the ticket to Sara
Agent B changes the status to urgent

Both APIs return 200 OK.

The final record contains only Agent B’s copy of the object. Sara’s assignment vanished.

This is not a network problem. It is a lost update caused by a read-modify-write race.

The Race Condition#

Both requests read version 7 before either writes:

Time ----->
 
Request A:  READ version 7 -------- UPDATE owner = 'Sara'
Request B:       READ version 7 -------- UPDATE status = 'urgent'

If each request sends a complete object, the later write may overwrite fields it never intended to change.

// Dangerous when `ticket` was read earlier.
await db.ticket.update({
  where: { id: ticket.id },
  data: {
    status: ticket.status,
    ownerId: ticket.ownerId,
    priority: ticket.priority,
  },
});

Partial updates reduce accidental overwrites, but they do not solve every race. Two requests incrementing the same counter can still lose one increment.

Why MVCC Does Not Magically Fix It#

PostgreSQL uses Multi-Version Concurrency Control. Readers can see a consistent snapshot while writers create new row versions.

MVCC is excellent for concurrency, but it does not understand your business intent.

At the default READ COMMITTED isolation level, this sequence is possible:

Transaction A reads balance = 100
Transaction B reads balance = 100
Transaction A writes balance = 80
Transaction B writes balance = 70
Final balance = 70

If A subtracted 20 and B subtracted 30, the expected result was 50. One change disappeared.

Solution 1: Make the Update Atomic#

The simplest solution is often to avoid reading before writing.

Bad counter update:

const post = await db.post.findUniqueOrThrow({ where: { id } });
 
await db.post.update({
  where: { id },
  data: { likes: post.likes + 1 },
});

Atomic SQL:

UPDATE posts
SET likes = likes + 1
WHERE id = $1
RETURNING likes;

The database applies each increment to the current stored value. Concurrent requests do not need to transport the old counter through application memory.

Atomic conditional writes can also protect invariants:

UPDATE inventory
SET available = available - $1
WHERE product_id = $2
  AND available >= $1
RETURNING available;

If zero rows are returned, there was not enough inventory. The check and update happen as one statement.

Solution 2: Optimistic Locking#

Optimistic locking assumes conflicts are possible but uncommon. Add a version column:

ALTER TABLE tickets
ADD COLUMN version integer NOT NULL DEFAULT 1;

Read the record and send the version with the update:

UPDATE tickets
SET
  owner_id = $1,
  version = version + 1
WHERE id = $2
  AND version = $3
RETURNING *;

If Agent A updates version 7 first, it becomes version 8. Agent B’s WHERE version = 7 matches no row.

The API can return 409 Conflict:

const result = await updateTicket({ id, expectedVersion, ownerId });
 
if (result.rowCount === 0) {
  throw new ConflictError("Ticket changed since you loaded it");
}

The client then reloads, shows the newer state, and asks the user to retry or merge intentionally.

Optimistic locking is a strong fit for:

• Admin screens
• Profile editing
• Content management
• Workflows where concurrent edits are uncommon

It is less attractive when the same record conflicts constantly, because callers repeatedly retry.

Solution 3: Pessimistic Row Locking#

When a transaction must read a row, make several decisions, and then write safely, lock it:

BEGIN;
 
SELECT available
FROM inventory
WHERE product_id = $1
FOR UPDATE;
 
UPDATE inventory
SET available = available - $2
WHERE product_id = $1;
 
COMMIT;

Another transaction requesting the same row lock waits until the first finishes.

Transaction A: lock row -> validate -> update -> commit
Transaction B:      wait -----------------> lock -> update

Pessimistic locking is useful when:

• Conflicts are common
• The invariant spans several statements
• A failed optimistic attempt is expensive
• Work must occur in a strict sequence

It also introduces risks:

• Longer wait times
• Deadlocks
• Reduced throughput for hot rows
• Locks held accidentally during slow network calls

Never call an external API while holding a database lock unless the design explicitly accepts that latency and failure risk.

What About Serializable Transactions?#

PostgreSQL’s SERIALIZABLE isolation can detect execution patterns that would not be valid in a serial order.

for (let attempt = 1; attempt <= 3; attempt++) {
  try {
    return await runSerializableTransaction();
  } catch (error) {
    if (!isSerializationFailure(error) || attempt === 3) throw error;
  }
}

Serializable transactions can abort under contention. Your application must retry the whole transaction, not just the last statement.

They are powerful, but they are not a reason to ignore clear atomic operations or explicit version checks.

Diagnose Concurrency Bugs#

Lost updates often appear as user reports rather than infrastructure alerts.

Look for:

• Audit records with two updates milliseconds apart
• Multiple requests carrying the same version or updated_at
• APIs that accept and overwrite complete objects
• Read-modify-write code outside a transaction
• Rows with unexpectedly high update frequency
• Retries that replay writes without idempotency

Create a deterministic concurrency test:

const start = Promise.withResolvers<void>();
 
const a = updateOwnerAfter(start.promise, "sara");
const b = updateStatusAfter(start.promise, "urgent");
 
start.resolve();
await Promise.all([a, b]);

Run the test repeatedly and assert the invariant, not only that both requests returned successfully.

Choose the Smallest Correct Tool#

The more locking you introduce, the more carefully you must control transaction duration and ordering.

Production Best Practices#

1. Define the invariant before choosing a lock.
2. Prefer atomic SQL over application read-modify-write loops.
3. Use a version column for user-facing edit conflicts.
4. Return a clear 409 Conflict instead of silently overwriting.
5. Keep locked transactions short.
6. Acquire multiple locks in a consistent order.
7. Retry serialization failures with a strict limit.
8. Record actor, version, and change in an audit log where needed.
9. Test concurrent requests deliberately.
10. Keep external calls outside the database transaction when possible.

Conclusion#

When two users update the same record, the database does not know whether the second writer should win, merge, wait, or fail.

That is an application decision expressed through an atomic statement, version check, lock, or isolation level.

The safest design makes conflicts visible:

Detect conflict
    |
    v
Preserve the invariant
    |
    v
Retry or ask the caller to merge

Concurrency correctness is not “both requests returned 200.” It is “the final state still means what the business says it means.”

These database guarantees are one part of a wider distributed contract. The consistency-models guide explains how replicas, caches, and projections can change what users observe after the write commits.

References#

• Multiversion concurrency control — PostgreSQL
• Transaction isolation — PostgreSQL
• Explicit locking — PostgreSQL