Transactions, select_for_update and the lost-update race

Your application runs many requests at once, and sometimes two of them touch the same row at the same time. When they do, the naive "read a value, change it, save it back" pattern can silently lose one of the changes — a bug that never shows up in development (one request at a time) and corrupts data in production (many). This is the lost update, the most important concurrency bug in web applications, and this lesson breaks it on purpose, shows the real numbers, and fixes it three ways.

The lost update, demonstrated

Consider two requests adding to an appointment's fee at the same time — one adds ₹50, one adds ₹30. The fee starts at ₹100; it should end at ₹180. Here is what happens with read-modify-write when the two overlap:

# both requests read BEFORE either writes — the classic interleaving
r1 = Appointment.objects.get(id=aid)      # request 1 reads fee = 100
r2 = Appointment.objects.get(id=aid)      # request 2 reads fee = 100 (still 100!)
r1.fee = r1.fee + 50; r1.save()           # request 1 writes 150
r2.fee = r2.fee + 30; r2.save()           # request 2 writes 130 — overwrites 150

Verified result: the final fee is 130.00, not 180.00 — the ₹50 increment was lost. Both requests read 100, each computed from that stale value, and the second write clobbered the first. No error was raised; the data is simply wrong. On a fee this is money silently vanishing; on a stock count it is overselling; on any counter it is an undercount. This is the lost update, and it is invisible until you have concurrency.

Fix 1: F() — make the update atomic

The best fix for "adjust a value based on its current value" is F() (from the ORM-in-depth module), which does the read and write in a single database operation:

Appointment.objects.filter(id=aid).update(fee=F("fee") + 50)
Appointment.objects.filter(id=aid).update(fee=F("fee") + 30)

Verified: the final fee is 180.00 — both increments applied. Because each update becomes SQL SET fee = fee + 50, the database reads the current value and adds to it atomically; there is no window between read and write for another request to slip in. For any increment/decrement or adjust-by-current-value, use F() — it is the simplest and most efficient fix, and it eliminates the race entirely for that operation.

Fix 2: select_for_update — lock the row

F() works for a single-column arithmetic update. When you need to read a value, make a decision in Python based on it, and then write — logic that cannot be expressed as one SQL update — you must stop other requests from touching the row while you work. select_for_update locks the selected rows until the transaction commits:

from django.db import transaction

with transaction.atomic():                                   # required — locks live in a transaction
    appt = Appointment.objects.select_for_update().get(id=aid)   # row locked here
    if appt.status == "booked":                              # a decision the DB can't express as SQL
        appt.fee = appt.fee + 50
        appt.save()
    # lock released at commit

Verified: this runs correctly inside atomic, producing the expected result. Any other request that tries to select_for_update the same row waits until this transaction commits — so the two cannot interleave, and the lost update cannot happen. select_for_update must be inside transaction.atomic() (a lock needs a transaction to live in), and it is the right tool when your update involves conditional logic, not just arithmetic. The cost is that other requests block briefly, so keep the locked section short.

Fix 3: constraints and get_or_create for the "created twice" race

A related race is inserting a duplicate — two requests both check "does this exist?", both see no, both create it. The fix is not application logic but a database UniqueConstraint (from the transactions lesson): the database rejects the second insert with an IntegrityError, because uniqueness is enforced at the data level regardless of timing. For the common "get it or make it" case, get_or_create combines the check and create, and paired with a unique constraint it is safe against the race:

patient, created = Patient.objects.get_or_create(
    phone="09812345678",                       # unique field (backed by a UniqueConstraint)
    defaults={"name": "Asha", "city": "Pune"},
)

The uniqueness lives in the database, so even if two requests race, only one insert succeeds and the other either gets the existing row or a handleable error. Race conditions on existence are solved by database constraints, not by checking-then-creating in Python — the check-then-act in Python is itself the race.

The rule: never read-modify-write across a request boundary

Step back to the principle. The lost update happens because a value is read in one moment and written in another, with a gap where another request can change it. The three fixes all close that gap in the database: F() collapses read and write into one atomic statement; select_for_update holds a lock across the gap; a UniqueConstraint makes the database enforce the invariant regardless of interleaving. The anti-pattern to recognise is any code that reads a value into Python, computes a new value from it, and writes it back — on concurrent requests, that is a lost update waiting to happen. When you see it on data that concurrent requests touch, reach for F(), a lock, or a constraint. This is the concurrency instinct that separates code that works in development from code that survives production.

Check your work

The lost update. Two overlapping read-modify-write cycles both read the same stale value; the second write clobbers the first. Verified: 100 + 50 + 30 gave 130, not 180 — the +50 was lost, with no error.

Fix 1, F(). Collapses read and write into one atomic SQL statement (SET fee = fee + 50); no gap for a race. Verified: gives 180. Use for increment/adjust-by-current-value.

Fix 2, select_for_update. Locks the row until the transaction commits, so other requests wait — for read-decide-write logic that is not pure arithmetic. Must be inside transaction.atomic(); keep the locked section short.

Fix 3, constraints / get_or_create. The "created twice" race is solved by a database UniqueConstraint (not Python checks); get_or_create plus a unique field is safe against it.

The principle. Never read-modify-write across a gap on data concurrent requests touch — close the gap in the database with F(), a lock, or a constraint.

Practice

1. Reproduce the lost update: read the same appointment into two variables, increment each, save both; confirm the result is 130, not 180.
2. Fix it with two F() updates and confirm the result is 180.
3. Rewrite a read-decide-write operation with select_for_update inside transaction.atomic(); confirm it runs, and reason about what a second concurrent request would do.
4. Try select_for_update without transaction.atomic() and read the error.
5. Add a UniqueConstraint on a field and use get_or_create; reason about why two racing requests cannot both insert.
6. Find a read-modify-write in code you can access and identify which of the three fixes applies.

Official documentation

• Django — F() expressions and race conditions — Atomic updates.
• Django — select_for_update — Row locking within a transaction.
• Django — get_or_create — And why a unique constraint makes it race-safe.

Next: background tasks and scheduled jobs with Celery.