What is Lost Update?

🟢 Junior Level

Lost Update is an anomaly where changes made by one transaction are overwritten by another transaction, and the first update is irretrievably lost.

Key point: both transactions not only read but also WRITE. The last write erases the result of the first.

Simple analogy: Two editors simultaneously edit the same document. Editor A fixes 5 errors and clicks “Save.” Editor B fixes 3 other errors and clicks “Save.” The file only saved B’s changes — A’s changes are lost.

SQL example:

-- Initial value: balance = 1000

-- Transaction 1: credit +500
BEGIN;
SELECT balance FROM accounts WHERE id = 1;    -- reads 1000
-- (calculates: 1000 + 500 = 1500)

-- Transaction 2: debit -200
BEGIN;
SELECT balance FROM accounts WHERE id = 1;    -- also reads 1000
-- (calculates: 1000 - 200 = 800)

-- Transaction 1:
UPDATE accounts SET balance = 1500 WHERE id = 1;  -- writes 1500
COMMIT;

-- Transaction 2:
UPDATE accounts SET balance = 800 WHERE id = 1;   -- overwrites to 800!
COMMIT;

-- Result: balance = 800. The +500 credit is lost!

Key problem: Both transactions read the same value and wrote the result of their own calculations, overwriting each other.

When it matters: Account balances, counters, inventory, any “read-modify-write” operations.

🟡 Middle Level

How It Works Internally

Lost Update is not directly an isolation level problem, but a problem of the read-modify-write pattern. It occurs when:

Transaction A reads value X
Transaction B reads value X (same snapshot)
Transaction A computes the new value and writes it
Transaction B computes its value and overwrites A’s result

At the Read Committed level, this happens naturally because each SELECT sees the latest committed data. On Repeatable Read in PostgreSQL, it’s less likely (MVCC snapshot), but still possible in certain scenarios.

Real Scenarios

Scenario 1: Account Update via API

// Without protection — Lost Update guaranteed on concurrent requests
@Transactional
public void deposit(Long accountId, BigDecimal amount) {
    Account account = accountRepo.findById(accountId);   // reads balance = 1000
    account.setBalance(account.getBalance().add(amount)); // 1000 + 500 = 1500
    accountRepo.save(account);                            // writes 1500
}
// Two parallel deposit(500) calls may result in 1500 instead of 2000

Scenario 2: View Counter

@Transactional
public void incrementViews(Long articleId) {
    Article article = articleRepo.findById(articleId);     // views = 99
    article.setViews(article.getViews() + 1);              // 99 + 1 = 100
    articleRepo.save(article);                              // writes 100
}
// 100 parallel requests may give 101 instead of 199

Common Mistakes

Mistake	Consequence	Solution
Read-modify-write without locks	Lost updates on concurrent access	`SELECT ... FOR UPDATE` or optimistic locking
Assuming @Transactional protects from lost update	Transaction != protection from concurrent writes	Use `@Version` or pessimistic lock
Optimistic lock retry without limit	Infinite retry loop under high contention	Add `maxRetries` and fallback
UPDATE without WHERE version = ?	Bypassing optimistic locking	Always use version in the condition

Comparison of Protection Approaches

Approach	Mechanism	Pros	Cons
`SELECT ... FOR UPDATE`	Pessimistic lock	Simplicity, guaranteed protection	Locks, deadlocks, reduced throughput
`@Version` (optimistic)	Version check on write	No locks, high speed	Retries on conflict, unsuitable for hot-spots
Atomic UPDATE (`SET x = x + 1`)	Single query without read	Maximum performance	Only for simple operations, no validation
Serializable isolation	Full serialization	Protection from all anomalies	Serialization failures, low throughput

When NOT to Use Pessimistic Locking

High contention on a single record (counters, leaders) — deadlock rate will grow exponentially
Read-heavy workload — 95%+ queries are reads, locks are excessive
Microservices with eventual consistency — locks don’t scale horizontally

When NOT to Use Optimistic Locking (@Version)

Don’t use optimistic locking (@Version):

For hot-spot records (counters, queues) — contention >20%, infinite retries
For financial transactions with strict invariants — retry logic complicates the code

🔴 Senior Level

Internal Implementation: MVCC, Lock Types, and Write Skew

Terms:

Write Skew — two transactions read overlapping data, make decisions based on what they read, and write results that violate an invariant.

HOT update (Heap Only Tuple) — an update without changing indexed columns, so PostgreSQL updates the row without updating the index.

EvalPlanQual — PG mechanism: on concurrent UPDATE, re-reads the row and checks if it still satisfies the WHERE condition in the new version.

How PostgreSQL MVCC Prevents (and Doesn’t Prevent) Lost Update

PostgreSQL Read Committed (default):
  Each SELECT sees the latest committed snapshot
  T1: SELECT balance → 1000 (snapshot at time T1)
  T2: SELECT balance → 1000 (snapshot at time T2, already sees committed T1 if T1 committed)

  If T1 and T2 work in parallel (neither has committed yet):
  T1: SELECT balance → 1000
  T2: SELECT balance → 1000 (MVCC: xmax = 0, row not deleted)
  T1: UPDATE balance = 1500 → creates new row version (new tuple, xmin = T1)
  T2: UPDATE balance = 800 → WAITS (T1 hasn't committed yet, row locked)
  T1: COMMIT → T2 continues, sees the new row version
  T2: UPDATE balance = 800 → overwrites! Lost Update!

Key insight: On Read Committed, PostgreSQL does NOT prevent Lost Update for the read-modify-write pattern, because after waiting for the lock, T2 re-reads the row (but doesn’t necessarily see T1’s change if the logic is at the application level).

Pessimistic Locking: Row-Level Lock Internals

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

What happens inside:

PostgreSQL sets an ExclusiveLock on the tuple (row)
Other transactions attempting SELECT ... FOR UPDATE or UPDATE are blocked
Lock is stored in LockManager shared memory (~200 bytes per lock)
On deadlock: PostgreSQL chooses a victim by transaction cost (less work → abort)

Deadlock detection:

PostgreSQL runs the deadlock detector every deadlock_timeout (default 1 sec)
Algorithm: build wait-for graph, search for cycles
Complexity: O(V + E) where V = number of transactions, E = number of waits

Optimistic Locking: @Version Implementation

@Entity
public class Account {
    @Id
    private Long id;

    private BigDecimal balance;

    @Version
    private Long version;  // Hibernate adds WHERE version = ?
}

Generated SQL:

-- Hibernate on save():
UPDATE accounts
SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;

-- If rows_affected == 0 → OptimisticLockException

Internal mechanics in Hibernate:

On entity load: reads current version
On flush: generates UPDATE with WHERE version = old_version
If executeUpdate() == 0: another transaction already modified the row
Hibernate throws OptimisticLockException (or StaleObjectStateException)

Architectural Trade-offs

Approach A: Atomic SQL (SET balance = balance + ?)

✅ Pros: Zero lost update, maximum throughput (50K+ TPS), no locks
❌ Cons: Only for simple operations, no validation possible, no audit trail
Suitable for: counters, simple increments/decrements

Approach B: Optimistic Locking (@Version)

✅ Pros: No locks, deterministic behavior, easy to implement
❌ Cons: Retries on contention (5-30% abort rate under high load), doesn’t work for hot-spots
Suitable for: CRUD with moderate contention (profile updates, config changes)

Approach C: Pessimistic Locking (SELECT … FOR UPDATE)

✅ Pros: Guaranteed protection, simplicity, works for complex business logic
❌ Cons: Deadlocks, 3-10x throughput reduction, connection pool exhaustion risk
Suitable for: financial transactions with low contention

Approach D: Event Sourcing / CQRS

✅ Pros: No lost updates (append-only), full audit trail, horizontal scaling
❌ Cons: Complexity, eventual consistency, needs event store
Suitable for: high-load systems with audit requirements

Edge Cases and Corner Cases

1. Lost Update with Composite Key:

// UPDATE may be "lost" if WHERE condition is not unique
UPDATE accounts SET balance = ? WHERE user_id = ? AND currency = 'USD';
// If two requests come simultaneously, both find the same row, but UPDATE is atomic
// However, read-modify-write BEFORE UPDATE is already broken

2. Hibernate First-Level Cache and Lost Update:

@Transactional
public void updateAccount(Long id, BigDecimal amount) {
    Account a1 = repo.findById(id);      // loaded into L1 cache
    Account a2 = repo.findById(id);      // returns the same object from L1 cache!

    // If another transaction changed the DB between the two findById calls,
    // both a1 and a2 will show the old value
    a1.setBalance(a1.getBalance().add(amount));
    repo.save(a1);  // May overwrite someone else's changes
}

3. Write Skew (Sophisticated Lost Update):

-- doctors on call constraint: at least 1 doctor must be on call
-- T1: SELECT COUNT(*) FROM doctors WHERE on_call = true;  → 2
-- T2: SELECT COUNT(*) FROM doctors WHERE on_call = true;  → 2
-- T1: UPDATE doctors SET on_call = false WHERE name = 'Alice';  → now 1
-- T2: UPDATE doctors SET on_call = false WHERE name = 'Bob';    → now 0! Constraint violated

On Repeatable Read, both SELECTs return 2. Both UPDATEs succeed. Constraint is violated. This is Write Skew — a variant of Lost Update.

4. Lost Update with Batching:

// Batch update: updating 1000 records
@Transactional
public void batchUpdate() {
    List<Account> accounts = repo.findAll();  // snapshot 1
    for (Account a : accounts) {
        a.setBalance(calculate(a));           // calculations
    }
    repo.saveAll(accounts);                   // flush — may overwrite others' changes
}
// While the batch runs, another transaction may change 500 out of 1000 records

5. Lost Update via Secondary Index:

-- UPDATE by non-unique index may cause row lock escalation
UPDATE accounts SET balance = ? WHERE status = 'active';
-- If 10,000 rows, PostgreSQL may escalate to page-level or table-level lock

Performance Implications

Approach	Latency (p99)	Throughput	Contention Impact
Atomic SQL	1-3ms	50,000+ TPS	Minimal
Optimistic Locking	2-5ms (happy path), 50-200ms (retry)	30,000 TPS (low contention), 5,000 TPS (high)	Exponential retry growth
Pessimistic Locking	5-20ms	5,000-10,000 TPS	Linear decrease
Serializable	20-100ms	2,000-8,000 TPS	10-30% abort rate

Specific numbers (PostgreSQL 15, 8 cores, NVMe, 1M rows):

Atomic SET balance = balance + 1: ~55,000 TPS
Optimistic Lock with 10% contention: ~25,000 TPS, avg 3 retries
Pessimistic Lock: ~8,000 TPS, deadlock rate 0.1% at 100 concurrent
Serializable: ~5,000 TPS, serialization failure rate 15%

Memory Implications

Row locks (FOR UPDATE): ~200 bytes per lock in shared memory. At 10,000 concurrent locks = ~2MB.
Hibernate L1 cache: One entity ~500-2000 bytes. Loading 10,000 entities = 5-20MB in heap.
MVCC dead tuples: Each UPDATE creates a new row version. Old version = dead tuple. AutoVacuum removes them, but under high update frequency → table bloat (2-5x growth).
Retry buffers: With optimistic locking, each retry allocates a new transaction context. At 1000 retries/sec = ~50MB/s allocation rate.

Concurrency Aspects

Write Skew detection:

PostgreSQL Serializable: tracks RW-dependencies
  T1: reads {doctors WHERE on_call = true}
  T2: reads {doctors WHERE on_call = true}
  T1: writes (update Alice)
  T2: writes (update Bob)

  Dependency graph:
    T1 → reads → predicate P → T2 writes to P
    T2 → reads → predicate P → T1 writes to P

  Cycle detected → serialization failure → one aborts

Real Production Scenario

Situation: Cryptocurrency exchange (2023), order processing.

Problem: Under high volatility (5000 orders/sec), user balances went out of sync. The sum of balances didn’t match the sum of deposits — $2M “disappeared” in 30 minutes.

Root cause:

@Transactional  // Read Committed by default
public void executeOrder(Long userId, BigDecimal amount) {
    User user = userRepo.findById(userId);       // balance = 10,000
    // Order processing...
    user.setBalance(user.getBalance().subtract(amount));  // 10,000 - 500 = 9,500
    userRepo.save(user);
}

At 5000 orders/sec, multiple transactions read the same balance, subtracted their amount, and wrote the result. The last transaction overwrote all previous ones.

Solution (quick): Atomic SQL:

@Modifying
@Query("UPDATE User u SET u.balance = u.balance - :amount WHERE u.id = :userId AND u.balance >= :amount")
int subtractBalance(@Param("userId") Long userId, @Param("amount") BigDecimal amount);

// Returns rows affected — if 0, means insufficient funds

Why does atomic SQL work? SET balance = balance - amount executes in one step: the DBMS reads the current value and immediately writes the new one. There’s no window between read and write where another transaction can interfere.

Solution (long-term): Event Sourcing with append-only ledger:

// Instead of updating balance — INSERT a transaction
@Transactional
public void executeOrder(Long userId, BigDecimal amount) {
    // Check by summing all transactions (slower but more accurate)
    BigDecimal currentBalance = ledgerRepo.sumByUser(userId);
    if (currentBalance.compareTo(amount) < 0) {
        throw new InsufficientFundsException();
    }
    ledgerRepo.save(new LedgerEntry(userId, amount.negated(), "ORDER"));
    // Balance = sum of all records, no lost update
}

Impact:

Before: $2M lost updates in 30 minutes
After (atomic SQL): 0 lost updates, throughput 8,000 TPS
After (event sourcing): 0 lost updates, throughput 5,000 TPS, full audit trail

Monitoring and Diagnostics

PostgreSQL — detecting lost update patterns:

-- Check for update conflicts
SELECT
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,  -- HOT updates = good (no index update)
    n_dead_tup,     -- Dead tuples from lost updates
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'accounts';

-- Long-running transactions (risk window for lost updates)
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Hibernate — Optimistic Lock failures:

@EntityListener
public class OptimisticLockMonitor {
    @PostUpdate
    public void onUpdate(Object entity) {
        // Log version conflicts
    }
}

// Micrometer counter
Counter.builder("hibernate.optimistic_lock.failures")
    .tag("entity", "Account")
    .register(meterRegistry);

Deadlock monitoring:

-- PostgreSQL deadlock stats (PG 14+)
SELECT
    datname,
    deadlocks
FROM pg_stat_database;

-- Application: track deadlock rate
// Alert when deadlocks > 10/min

Best Practices for Highload

Use atomic SQL for counters and balances: UPDATE table SET counter = counter + 1 instead of read-modify-write.
Optimistic locking for moderate contention: Add exponential backoff with jitter for retries.
Pessimistic locking only for critical paths: Minimize the time FOR UPDATE is held.
Partitioning for hot-spot reduction: Split tables with frequent updates (e.g., by user_id hash).
Event Sourcing for financial systems: Append-only ledger eliminates lost updates at the architectural level.
Avoid long-running transactions: The longer the transaction, the larger the window for lost update.
Monitor dead tuples: Regular autoVacuum, configure autovacuum_vacuum_threshold for high-update tables.

🎯 Interview Cheat Sheet

Must know:

Lost Update — two transactions read the same value, both modify and write, the first result is overwritten by the second
This is a read-modify-write pattern problem, not a specific isolation level issue
Solutions: atomic SQL (SET balance = balance + 1), SELECT FOR UPDATE, optimistic locking (@Version), Serializable
PostgreSQL on Read Committed does NOT prevent lost update for application-level read-modify-write
Event Sourcing / append-only ledger completely eliminates lost updates at the architectural level
Write Skew — a variant of lost update: two transactions read overlapping data and violate an invariant

Common follow-up questions:

How does atomic SQL prevent lost update? — One step: DBMS reads and writes atomically, no window for interference
How does pessimistic locking differ from optimistic? — Pessimistic locks in advance, optimistic checks version on write
When to use Event Sourcing? — Financial systems with audit requirements, high-load systems
What is Write Skew? — Two transactions read the same data, both modify — invariant violated (example: doctors on call)

Red flags (DO NOT say):

“@Transactional protects from lost update” — transaction != protection from concurrent writes
“Optimistic locking is always better than pessimistic” — for hot-spots, optimistic = infinite retries
“Lost Update = an isolation level problem” — it’s a read-modify-write pattern problem

Related topics:

[[2. What transaction isolation levels exist]]
[[5. What is Repeatable Read]]
[[13. What is Propagation in Spring]]
[[16. What is the @Transactional annotation]]