---

🟢 Junior Level

A Lost Update is a severe database concurrency anomaly where two concurrent transactions read the same record state, compute modifications independently in application memory, and write their results back sequentially. The second transaction’s write silently overwrites and obliterates the changes committed by the first transaction, with no database error or warning raised.

Execution Timeline: The Overwritten Balance

Initial State: account_1.balance = $1,000

Transaction 1 (Deposit Salary +$500)             Transaction 2 (Subscription Charge -$200)
------------------------------------             ----------------------------------------
1. SELECT balance FROM accounts WHERE id = 1;
   -- Reads: $1,000
                                                 2. SELECT balance FROM accounts WHERE id = 1;
                                                    -- Reads: $1,000!
3. Computes in RAM: 1,000 + 500 = $1,500
4. UPDATE accounts SET balance = 1500
   WHERE id = 1;
5. COMMIT; -- Database balance is now $1,500
                                                 6. Computes in RAM: 1,000 - 200 = $800
                                                 7. UPDATE accounts SET balance = 800
                                                    WHERE id = 1;
                                                 8. COMMIT; -- Database balance is now $800!

FINAL DATABASE STATE: $800! (Transaction 1's $500 deposit is permanently lost!)

Vulnerable Spring Service Code

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;

@Service
public class AccountService {

    private final AccountRepository accountRepository;

    public AccountService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    // 💥 HAZARDOUS CODE: @Transactional DOES NOT protect against Lost Updates under Read Committed!
    @Transactional
    public void depositUnsafe(Long accountId, BigDecimal amount) {
        Account account = accountRepository.findById(accountId).orElseThrow();
        account.setBalance(account.getBalance().add(amount)); // Read-Modify-Write in JVM heap
        accountRepository.save(account); // Overwrites concurrent updates!
    }
}

Analogy: Two accountants download the same spreadsheet containing warehouse inventory (100 laptops). Accountant A adds 50 laptops to the sheet and saves it to the network share at 10:00 AM (150 laptops). Accountant B subtracts 10 laptops from his locally opened sheet and saves it at 10:01 AM (90 laptops). Accountant B’s file completely overwrites Accountant A’s work—the incoming shipment of 50 laptops has vanished from records.

---

🟡 Middle Level

The Root Cause: The Read-Modify-Write Anti-Pattern

A Lost Update does not stem from database engine corruption; it arises from a temporal gap between steps in application code:

1. Read: The application queries a database record into local JVM heap memory (findById()).
2. Modify: Business logic updates entity fields in RAM (where threads can be paused by GC pauses, CPU scheduling, or I/O calls).
3. Write: The application sends an UPDATE statement back to the database.

Under standard Read Committed isolation, the database guarantees statement-level isolation, but does not link the initial SELECT with a subsequent UPDATE separated by milliseconds of application processing.

Four Production Defenses Against Lost Updates

Strategy	Mechanism	Concurrency Impact	Best Used For
1. Atomic In-Place SQL	UPDATE ... SET balance = balance + :amt	Lock-free in app; instant $X$-lock in DB	Counters, simple balance adjustments
2. Optimistic Locking	JPA @Version column (WHERE version = ?)	Non-blocking; fails on commit conflict	Low-to-moderate contention, web forms
3. Pessimistic Locking	SELECT ... FOR UPDATE (PESSIMISTIC_WRITE)	Queues threads; acquires row $X$-lock	High-contention hot-spots, invariant checks
4. Event Sourcing	Append-only ledger (INSERT instead of UPDATE)	Non-blocking writes; zero row updates	Banking ledgers, crypto ledgers, audit logs

1. Atomic In-Place SQL (Highest Performance)

Eliminates intermediate JVM memory buffering entirely:

@Modifying
@Query("UPDATE Account a SET a.balance = a.balance + :amount WHERE a.id = :id")
int atomicDeposit(@Param("id") Long id, @Param("amount") BigDecimal amount);

The database acquires an exclusive row lock (X-Lock) and computes the new value directly within the storage engine core.

2. Optimistic Locking (@Version)

Maintains an integer or timestamp version column in the database table:

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

    @Version // Managed automatically by Hibernate
    private Long version;
}

When persisting changes, Hibernate executes:

UPDATE accounts SET balance = 1500, version = 2 WHERE id = 1 AND version = 1;

If a concurrent transaction committed first, the row count updated is 0, causing Hibernate to throw an OptimisticLockException.

3. Pessimistic Locking (SELECT FOR UPDATE)

Acquires an exclusive lock at the read stage until transaction termination:

public interface AccountRepository extends JpaRepository<Account, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT a FROM Account a WHERE a.id = :id")
    Optional<Account> findByIdForUpdate(@Param("id") Long id);
}

---

🔴 Senior Level

PostgreSQL Behavior: Read Committed vs. Repeatable Read

In database concurrency theory, Berenson et al. (1995) formalized the Lost Update phenomenon as $P_4$:

\[r_1[x] \dots w_2[x] \dots c_2 \dots w_1[x] \dots c_1\]

PostgreSQL exhibits fundamentally different behavior depending on the isolation level:

• On Read Committed: Permits Lost Updates during application Read-Modify-Write cycles. While PostgreSQL uses EvalPlanQual (EPQ) re-evaluations for concurrent updates within the database, application-calculated updates overwrite previously committed values without error.
• On Repeatable Read: Automatically prevents Lost Updates! If Transaction $T_2$ attempts to UPDATE a row that was updated and committed by Transaction $T_1$ after $T_2$’s snapshot started, PostgreSQL immediately aborts $T_2$: ERROR: could not serialize access due to concurrent update (SQLSTATE 40001).

Architecture Pattern: Event Sourcing & Append-Only Ledgers

In high-throughput fintech platforms and exchange matching engines, core account balances are never modified using UPDATE:

• The account balance is not an editable column; it is an aggregation projection (Read Model).
• Every debit or credit is written as an immutable transaction log entry via INSERT:

  INSERT INTO account_ledger (account_id, amount, type, created_at)
  VALUES (1, +500.00, 'DEPOSIT', clock_timestamp());
  

• The current balance equals $\sum(\text{ledger amounts})$.
• Result: Lost Updates become physically impossible because no UPDATE statements exist in the system write path.

Optimistic vs. Pessimistic Locking Trade-Offs

Characteristic	Optimistic (@Version)	Pessimistic (PESSIMISTIC_WRITE)
Locking Mechanism	Lock-free; verified via SQL WHERE clause	Physical row-level exclusive lock ($X$-lock)
High Contention Behavior	Severe Retry Storms (Thundering Herd)	Cleanly queues incoming threads
Database Deadlock Risk	None	Possible if resource acquisition order varies
Overhead on Idle Data	Negligible (single version column check)	Connection holding time and lock manager RAM
Ideal Scenario	Web dashboards, shopping carts, CMS	Flash sales, seat booking, banking balances

---

4 Tricky Questions

1. Why does Spring’s @Transactional annotation by default fail to prevent Lost Updates?

Answer: Spring’s @Transactional merely establishes transaction boundaries (connection.setAutoCommit(false) and connection.commit()). By default, the transaction executes under the database engine’s default isolation level (Read Committed in PostgreSQL and Oracle). Under Read Committed:

1. findById() loads an entity snapshot at instant $T_1$.
2. The JVM modifies attributes in heap memory.
3. save() sends an UPDATE accounts SET balance = :val WHERE id = :id.

Because the UPDATE contains no condition checking that the record has remained unchanged since $T_1$, the database happily commits the new value, overwriting any concurrent commits that occurred during step 2. @Transactional ensures atomicity, not entity-level concurrency control.

---

2. What is the difference between LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE? Which one reliably prevents Lost Updates?

Answer:

• PESSIMISTIC_READ maps to SELECT ... FOR SHARE (or LOCK IN SHARE MODE). It acquires a shared read lock (S-Lock). Multiple transactions can read simultaneously, but other transactions are blocked from updating the row.
  • Deadlock Danger: If Transaction A and Transaction B both acquire PESSIMISTIC_READ on the same account and then both attempt an UPDATE, both transactions attempt to upgrade their shared S-Lock to an exclusive X-Lock. Because each waits for the other to release its S-Lock, an instant deadlock occurs.
• PESSIMISTIC_WRITE maps to SELECT ... FOR UPDATE. It immediately acquires an exclusive lock (X-Lock).
  • Only PESSIMISTIC_WRITE reliably prevents Lost Updates without deadlocks, as it forces concurrent transactions to serialize at the initial read step.

---

3. What happens if an entity mapped with @Version is updated via a native Spring Data JPA @Modifying @Query?

Answer: The optimistic locking mechanism is completely bypassed and disabled! JPA optimistic locking (@Version) is enforced strictly by Hibernate’s persistence engine during entity state flushes (merge(), persist(), or automated dirty checking). When an update is executed through @Modifying @Query("UPDATE Account a SET a.balance = :b WHERE a.id = :id"):

1. Hibernate bypasses the persistence context and dispatches the raw SQL directly to JDBC.
2. The database receives a raw statement without the AND version = :version predicate.
3. The version column is not incremented.
4. No OptimisticLockException can ever be triggered, exposing the system to silent Lost Updates.

---

4. Under what conditions does Optimistic Locking perform significantly worse than Pessimistic Locking?

Answer: Under High Write Contention on Hot-Spots (e.g., flash sales with 100 concurrent requests attempting to purchase the last available seat, or updating a viral post counter):

• With Optimistic Locking, exactly 1 request succeeds in incrementing the version, while the remaining 99 requests fail with OptimisticLockException. If a retry mechanism (e.g., @Retryable) is in place, those 99 threads continuously re-read, re-calculate, and re-attempt writes. This triggers a Retry Storm (Thundering Herd), saturating database connections and CPU.
• With Pessimistic Locking, the 100 threads form a lightweight FIFO queue on the row lock inside the database engine. Each thread acquires the lock, performs the update in a fraction of a millisecond, and commits. All requests succeed sequentially without repetitive rollback/retry cycles.

---

🎯 Interview Cheat Sheet

• Core Definition: Two transactions read the same data, modify it in application memory, and save back sequentially; the last writer overwrites the earlier changes without error.
• The Culprit: The Read-Modify-Write pattern executed under Read Committed without row locking or entity versioning.
• Why @Transactional Doesn’t Save You: Standard @Transactional enforces transaction boundaries, but does not prevent concurrent threads from overwriting entity state.
• 4 Defense Strategies:
  1. Atomic In-Place SQL: UPDATE ... SET col = col + ? (fastest, $O(1)$ database execution).
  2. Optimistic Locking: JPA @Version (best for low/moderate contention; zero lock overhead).
  3. Pessimistic Locking: SELECT ... FOR UPDATE via PESSIMISTIC_WRITE (best for high-contention hot-spots).
  4. Event Sourcing: Append-only transaction ledger (INSERT instead of UPDATE).
• PostgreSQL Detail: PostgreSQL Repeatable Read automatically aborts concurrent conflicting updates with SQLSTATE 40001.

Related Topics

• What Transaction Isolation Levels Exist
• What is Read Committed
• What is Repeatable Read
• What is Non-Repeatable Read
• What is the Transactional Annotation