On this path · Distributed Primitives 5. Distributed Concurrency, Locking, and Race Conditions
Distributed Concurrency, Locking, and Race Conditions
The portfolio-value debug: optimistic locking, idempotency keys, and database transactions versus in-memory state.
Learning outcomes
One of the documented Trade Republic debugging prompts is a race condition in concurrent portfolio-value code: the system computes a customer’s net portfolio value from recent transactions, and two concurrent requests produce inconsistent results. The bug is not a typo or a bad formula; it is a concurrency flaw, the kind that appears only when two operations interleave. This concept teaches how to find that flaw, how to fix it with the right locking strategy, and how to choose between optimistic locking, pessimistic locking, and idempotency when the system spans a database and many service instances.
After studying this page, you can:
- Diagnose the portfolio-value race as a read-modify-write conflict under weak isolation.
- Explain why a process-local mutex does not fix a distributed race.
- Implement optimistic locking with a version column and a conditional update.
- Implement pessimistic locking with SELECT FOR UPDATE and reason about its deadlock risks.
- Use an idempotency key to make retries safe and to defeat duplicate application of an operation.
- Distinguish a database transaction’s guarantees from in-memory state, and know when each applies.
- Defend a concurrency strategy against an interviewer who probes isolation, contention, and correctness.
Before we dive in
Net portfolio value is the sum a customer sees when they open the app: the current worth of every position they hold, minus what they owe, derived from their recent transactions and the latest prices. Computing it sounds like arithmetic. You read the transactions, you read the prices, you multiply and add, and you return a number. The interesting engineering begins when two requests to compute the same customer’s portfolio run at the same time, and when a write (a new trade, a price update) lands while the computation is in flight. The number the customer sees can be wrong, not because the math is wrong, but because the reads and writes interleaved in a way the single-threaded mental model never accounted for.
This is the territory of concurrency control. A single-process program can use a mutex to make operations atomic, because all the state lives in one process’s memory. A distributed system cannot, because the state lives in a shared database accessed by many service instances over the network, and a mutex in one instance does not lock anything in another. The tools that work at the process level (mutexes, channels, atomic operations) do not extend across the network, which is why distributed concurrency has its own techniques. This concept builds those techniques from the portfolio-value bug that motivates them.
The portfolio-value race
The buggy code reads the customer’s transactions and prices, computes the value, and writes a cached snapshot to speed up the next read. The race appears when two requests overlap. Both read the same set of transactions before either writes the snapshot. Both compute a value based on that read. Both write their computed value back as the cached snapshot. One write overwrites the other, and the cached snapshot is whichever finished last, which may not reflect transactions that arrived between the two reads.
The bug is the same lost-update anomaly from the consistency concept, now in application code rather than a balance update. Two transactions read the same data, each computes a result, and each writes back, so one result is lost. Under read committed isolation (the default), the database allows this, because each transaction’s read and write are independent statements that do not conflict at the row level until the write, by which point the read is stale.
Any code that reads a value, computes a new value, and writes it back is vulnerable to a lost update when two instances run concurrently. The fix is to make the read and write a unit that the database protects, either by locking the row for the transaction or by making the write conditional on the value not having changed.
Why a mutex is not enough
The instinct of a candidate who learned concurrency in a single-process context is to wrap the read-modify-write in a mutex. That works if all requests are served by one process, because the mutex serializes them. It fails the moment the service runs as multiple instances behind a load balancer, which every production service does. A mutex in instance A does not block instance B, which runs its own mutex in its own memory. The two instances read and write the same database row concurrently, and the race is back.
A mutex protects in-process state. It cannot protect shared database state accessed by many processes. For that, the lock must live where the state lives, which is the database, or the protocol must make conflicting writes impossible through conditional updates. This is the core distinction between process-local concurrency control and distributed concurrency control: the lock has to be on the shared resource, not in any one process’s memory.
The picture exposes the gap directly: two independent mutexes guard two independent memory spaces, while the shared row sits between them, unguarded by either. Whatever coordinates access to that row must live at the row, in the database, not in any one instance.
Optimistic locking
Optimistic locking assumes conflicts are rare and lets them happen, then detects them at write time and retries. The pattern adds a version column to the row. A transaction reads the row along with its version, computes its update, and writes back conditionally: the update succeeds only if the version in the row still matches the version it read. If another transaction changed the row in between, the version differs, the update affects zero rows, and the transaction retries the whole read-compute-write cycle.
Optimistic locking is a compare-and-set (CAS) at the row level. It is best when contention is low, because under low contention most attempts succeed on the first try and the cost is one extra column and one conditional predicate. Under high contention, retries multiply: many transactions read the same version, one wins the write, the rest retry, and under heavy contention the system can livelock or spend most of its cycles retrying. The retry budget must be bounded, and the strategy assumes that conflicts are the exception, not the norm.
Pessimistic locking with SELECT FOR UPDATE
Pessimistic locking assumes conflicts will happen and prevents them by locking the row for the duration of the transaction. The SQL mechanism is SELECT FOR UPDATE, which acquires a row-level lock that no other transaction’s SELECT FOR UPDATE can acquire until the holding transaction commits or rolls back. A transaction that locks the row, reads it, computes, and writes, is guaranteed that no other transaction can change the row between its read and its write.
Pessimistic locking is best when contention is high or when the operation must hold a consistent view across multiple reads within one transaction. Its cost is that it reduces concurrency: transactions queue on the lock, so throughput falls when many transactions contend on the same row. Its risk is deadlock: two transactions each holding a lock the other wants will wait forever unless the database detects the cycle and aborts one. PostgreSQL detects deadlocks and aborts one transaction with a deadlock error, which the application must handle by retrying.
If transactions lock multiple rows, they must lock them in a consistent order. Transaction A locking row one then row two, while transaction B locks row two then row one, is a deadlock waiting to happen. A global lock ordering, enforced everywhere, makes the wait-for graph acyclic and prevents the deadlock entirely.
Idempotency keys
Locking serializes operations; idempotency makes operations safe to repeat. The two solve different problems. Locking prevents two operations from interfering. Idempotency ensures that an operation applied twice has the same effect as applied once, which is what makes retry safe.
A retried request is common in distributed systems. A client sends a trade, the network drops the response, the client retries, and the server receives what looks like two trades. Without protection, the second request executes again: the balance moves twice, the holdings change twice. The fix is an idempotency key, a client-supplied unique identifier for the logical operation. The server records the key before executing, and if it sees the same key again, it returns the original result instead of re-executing.
The idempotency key table is the same dedup table from the partitioning concept: a growing record of every key ever seen. Recording the key and applying the trade in one transaction is what makes the operation idempotent: either both commit (the trade ran once, the key is recorded) or neither does (a crash retries the whole thing, and the key is not yet recorded so the retry proceeds). This is the application-level analog of the outbox invariant, applied to defeat duplicate execution rather than to deliver an event.
Database transaction versus in-memory state
A persistent confusion is the boundary between what a database transaction protects and what in-memory state protects. A database transaction protects the rows it reads and writes, for its duration, against other transactions, according to the isolation level. It does not protect data the application reads outside the transaction and uses inside it. It does not protect in-memory caches. It does not protect values computed before the transaction began.
A transaction’s guarantees apply only to the reads and writes performed inside it, at the isolation level chosen. A value computed outside the transaction, or a read from a previous transaction, is not protected by the current transaction. If you need consistency across a read and a write, they must be in the same transaction, or the write must be conditional on the read.
The portfolio-value bug illustrates this precisely. The buggy code reads transactions, computes a total, and writes the cache, but the read and write are separate statements with no shared transaction and no conditional update, so nothing protects the gap between them. The fix puts the read and write in one transaction (pessimistic) or makes the write conditional on the read’s version (optimistic), closing the gap. The lesson is that concurrency correctness is about what spans the gap between operations, and a transaction or a conditional write is the tool that spans it.
The atomic update shortcut
Not every read-modify-write needs a version column or a lock. When the new value is a pure function of the old one, you can push the computation into a single atomic SQL statement that the database executes under its own row lock, with no application-level read at all. A withdrawal that subtracts an amount can be written as an update that reads and writes in one statement, and the database’s statement-level atomicity guarantees no other transaction slips between the read and the write.
This works because the database holds the row for the duration of the single statement, which is far shorter than a whole transaction, and because the predicate and the update share one atomic moment. It is the cheapest possible protection for a pure read-modify-write. It does not help when the computation needs data from other rows, or when the new value depends on an external input that cannot be expressed in SQL, in which case optimistic or pessimistic locking is the tool. The senior judgement is to recognize when the computation fits one statement and avoid the heavier machinery.
If the new value is a pure function of the old, a single UPDATE that computes and checks in one statement is faster and simpler than a version column or a SELECT FOR UPDATE, because the database’s statement atomicity is enough. Reach for optimistic or pessimistic locking only when the computation spans rows or needs external inputs a single statement cannot express.
Deadlock detection and recovery
Pessimistic locking introduces the deadlock risk, and understanding how the database handles it is part of defending the design. PostgreSQL runs a deadlock detector that periodically builds a wait-for graph of transactions blocked on locks. When it finds a cycle, it picks one transaction in the cycle, aborts it with a deadlock error, and releases its locks so the others can proceed. The aborted transaction’s application receives the error and is expected to retry.
The detector runs on a configurable interval (about one second by default), which is the maximum time a deadlock can persist before it is broken. That interval is a latency floor for how long a deadlocked transaction waits before it is freed, and it is the reason long-held locks over many rows are dangerous: the window for a deadlock to form grows with the number of locks and the time they are held. The disciplined defense is consistent lock ordering, which makes the wait-for graph acyclic by construction so the cycle the detector hunts for never forms. Lock ordering is prevention; the detector is recovery. A production system relies on both, but prefers to need the recovery rarely.
Choosing a strategy
Optimistic locking fits low contention. When conflicts are rare, the version check succeeds on the first try most of the time, and the cost is one extra column and one predicate. It scales well because nothing is held while the transaction thinks, and there is no lock contention. It degrades under high contention, where retries multiply.
Pessimistic locking fits high contention or operations that need a consistent multi-read view. When many transactions contend on one row, a lock serializes them cleanly, and retry churn is avoided. It does not scale beyond the lock’s serialization point, and it risks deadlock if lock ordering is inconsistent.
Idempotency fits wherever an operation might be retried, which is everywhere in a distributed system. It is not a substitute for locking; it complements it. An operation should be idempotent (so retries are safe) and protected against concurrent interference (so concurrent instances do not corrupt each other). The two concerns are orthogonal.
The diagram separates the two decisions: the contention level drives the locking path on the left, while idempotency runs alongside both, because a retried operation is unsafe under either strategy unless it can be recognized and skipped.
Common mistakes candidates make
The first mistake is reaching for an in-process mutex to solve a distributed race. A mutex serializes within one process, but production services run many instances, and a mutex in one does not block another. The fix is to lock where the state lives, in the database, or to use a conditional update.
The second mistake is assuming BEGIN and COMMIT prevent lost updates. Under read committed, the default, two transactions can both read a value and both write back, and one update is lost. The fix is a stronger isolation level, a conditional update (optimistic), or a row lock (pessimistic).
The third mistake is using pessimistic locking without considering deadlock. Two transactions locking rows in different orders deadlock, and while PostgreSQL detects and aborts one, the application must retry. The disciplined fix is a consistent global lock ordering, so the wait-for graph never cycles.
The fourth mistake is forgetting idempotency when retries are possible. A retried trade without an idempotency key executes twice. The fix is a client-supplied idempotency key, recorded in the same transaction as the operation, so a retry is recognized and not re-applied.
The fifth mistake is confusing the transaction’s scope with in-memory state. A value read outside a transaction is not protected by a later transaction that uses it. The read and write that must be consistent must be in the same transaction, or the write must be conditional on the read.
Defending the design
When an interviewer asks you to fix the portfolio-value race, the defense starts with the diagnosis: it is a lost-update anomaly under read committed, where concurrent read-modify-write cycles clobber each other. Then the fix, chosen by contention profile. For low contention, optimistic locking: a version column and a conditional update that retries on conflict. For high contention, pessimistic locking: SELECT FOR UPDATE within a transaction that holds the row for its duration. For safety against retries, an idempotency key on every mutating operation, recorded atomically with the operation.
The follow-up on isolation is: pick repeatable read at minimum for financial correctness, because it catches lost updates, and handle the serialization conflicts it surfaces by retrying. The follow-up on deadlock is: enforce a consistent lock ordering across all transactions that lock multiple rows, so the wait-for graph is acyclic.
The final probe is about the boundary between the transaction and the application. The defense is: a transaction protects only the reads and writes inside it. Any value computed outside, or read in a previous transaction, is unprotected. The discipline is to put the read and write that must be consistent in one transaction, or to make the write conditional on the read, closing the gap that the race exploits.
Mastery Questions
Question
In the portfolio-value race, what exactly causes two concurrent requests to produce an inconsistent cached value?
Answer
Both requests read the same transactions before either writes the cache (a read-modify-write cycle), each computes a total against its read, and each writes its total back. One write clobbers the other, a lost update. Under read committed isolation the database allows this because the read and write are independent statements that do not conflict until the write, by which point the read is stale.
Sources & evidence7 claims · 1 cited
Portfolio-value race prompt backed by interview source; optimistic/pessimistic locking, idempotency keys, and transaction-scope reasoning is internal-reasoning grounded in standard database theory.
- The documented Trade Republic debugging prompt is a race condition in concurrent portfolio-value code where concurrent reads and writes produce an inconsistent cached value via a lost update under weak isolation.verified
- A process-local mutex does not fix a distributed race because production services run as multiple instances and a mutex in one instance does not block another; the lock must live where the shared state lives, in the database, or the write must be conditional.internal reasoning
- Optimistic locking adds a version column and writes back conditionally (UPDATE WHERE version = read_version); if another transaction changed the row the update affects zero rows and the transaction retries, best under low contention and degrading under high contention where retries multiply.internal reasoning
- Pessimistic locking via SELECT FOR UPDATE acquires a row-level lock for the transaction duration so no other transaction can lock the same row until commit; it fits high contention but risks deadlock, which a consistent global lock ordering prevents by keeping the wait-for graph acyclic.internal reasoning
- An idempotency key is orthogonal to locking: locking prevents concurrent interference while idempotency makes an operation safe to repeat by recording a client-supplied key in the same transaction as the operation so a retried request returns the prior result without re-executing.internal reasoning
- A database transaction protects only the rows it reads and writes for its duration at the chosen isolation level; a value computed outside the transaction or read in a previous transaction is unprotected, so consistent read and write pairs must share one transaction or use a conditional write.internal reasoning
- Choose optimistic locking for low contention where version checks usually succeed first try, pessimistic locking for high contention or consistent multi-read views where serialization avoids retry churn, and add idempotency keys wherever an operation can be retried since retries are always possible in a distributed system.internal reasoning
Cited sources
- How to Pass the Trade Republic Software Engineer Interview (JobMentis) · JobMentis (interview outcome database)