TL;DR: When multiple requests read and update the same database record simultaneously, performing calculations in your application code causes race conditions and lost updates. To prevent this, delegate the calculation directly to your database engine using atomic updates (e.g., SET quantity = quantity + 1).
I’ve seen this silent data killer play out on plenty of boring Tuesday afternoons. You are sitting at your desk, sipping a lukewarm coffee, when a bug report lands in your queue. The warehouse system physical inventory count is seven, but the database insists there are only six. You check the system logs, and everything looks pristine: every API request returned a 200 OK, and every database transaction committed successfully.
So, where did that missing item go?
The culprit isn't a failing database or a network drop. It is a silent, data-corrupting concurrency bug known as a "lost update" race condition. Let's look at why this happens and how I write code to prevent it.
Why did my database stock count get out of sync?
Your database count is out of sync because two concurrent requests read the exact same initial state, performed addition in application memory, and then wrote back the same final value. This classic concurrency bug is known as a "lost update" race condition.
To understand why this happens, I like to use a simple whiteboard analogy. Imagine a physical whiteboard with the number "5" written on it. Two people walk up to the board, planning to add 1 to the total.
- Person A looks at the board and reads "5".
- Person B looks at the board at the exact same time and reads "5".
- Person A does the math in their head (5 + 1 = 6) and writes "6" on the board.
- Person B does the math in their head (5 + 1 = 6) and writes "6" on the board.
Even though two separate increments occurred, the final value is 6 instead of 7. Person B's write completely erased Person A's work. This is exactly what is happening inside your database when concurrent threads process writes using stale read data.
How does a lost update race condition happen in application code?
A lost update happens when application code reads a row, modifies the value in local memory, and saves that absolute value back to the database. Because concurrent database reads do not block each other by default, multiple threads will fetch the same initial state and overwrite each other's changes.
When I audit backend codebases, I often find a simple three-step sequence: fetch, modify, save. This sequence is inherently unsafe when executed concurrently.
I've broken down the differences between handling this arithmetic in your application versus delegating it to your database:
| Feature | App-Level Calculations (SET val = @new_val) |
Database-Level Atomic Updates (SET val = val + 1) |
|---|---|---|
| Execution Location | Application Memory (Node, Go, JVM) | Database Engine |
| Race Condition Risk | High (Concurrent writes overwrite each other) | None (Writes are serialized on the row lock) |
| Network Roundtrips | Requires Read-then-Write (2 steps) | Single Write (1 step) |
| Efficiency | Slower due to network latency | Fast, executed directly on disk/memory |
When two API endpoints execute this sequence at the same millisecond, they both fetch the stock count of 5. Both calculation steps yield 6, and both save statements write 6 back to the row. The database did exactly what we told it to do; our application logic was the weak link.
How do you fix a lost update with atomic database updates?
You fix a lost update by shifting the mathematical calculation from your application code directly to the database engine. By using an atomic update statement, the database serializes the operations and evaluates the arithmetic using the absolute latest state of the row.
My rule of thumb is simple: never compute the absolute new value in your code if you can help it. Instead, write a query that instructs the database engine to perform the arithmetic directly on the disk.
Here is the difference in SQL:
-- Avoid: App-level calculation (vulnerable to race conditions)
UPDATE inventory SET stock = 6 WHERE id = 42;
-- Use: Atomic update (race condition safe)
UPDATE inventory SET stock = stock + 1 WHERE id = 42;
When you use SET stock = stock + 1, the database engine acquires a write lock on that specific row. If two transactions attempt to execute this statement simultaneously, the database forces the second transaction to wait until the first one completes. The second transaction then executes its calculation using the newly updated value of 6, successfully raising the final total to 7.
FAQ
Whenever I talk to developers about concurrency, a few common questions always pop up.
Can standard database transactions prevent lost updates?
No, standard transactions running at default isolation levels (such as Read Committed) do not prevent lost updates. While transactions ensure that your writes are atomic and won't be partially saved, they do not prevent concurrent threads from reading the same stale data unless you explicitly use a serializable isolation level or write locks.
How do I implement atomic updates using an ORM?
Most modern ORMs support atomic updates natively without requiring raw SQL. For example, in Prisma I recommend using the increment helper inside your update query, and in Hibernate/JPA I write a JPQL update statement (UPDATE Inventory i SET i.stock = i.stock + 1 WHERE i.id = :id) to bypass loading the entity into application memory.
What are the downsides of relying on database-level updates?
Database-level updates bypass your application's domain logic, meaning any in-memory validation rules (such as checking if stock drops below zero) cannot easily run before the write occurs. To handle this, I recommend relying on database constraints (like a CHECK constraint to prevent negative values) or using pessimistic locking (SELECT FOR UPDATE) to safely run complex validation rules in your application code.
Top comments (0)