Race conditions and select_for_update: two requests, one last ticket
Two requests read the same stock and sell the last ticket twice. Atomic UPDATE with F(), select_for_update, nowait, skip_locked, deadlocks and optimistic locking with a version column, explained from inside PostgreSQL.
Picture a ticket sale. The last seat for a concert is still on sale, and two people press "Buy" within the same few milliseconds. Two Gunicorn workers pick up the requests, both read stock = 1 from the database, both decide "there is enough", both write stock = 0 and both create an order. The database is happy, the logs are clean, and the next day you have two customers holding one seat.
This is a lost update: one write silently overwrites another because both were based on the same stale read. In this post we will take that one bug apart: why the obvious code breaks, how PostgreSQL row locks actually work, what F() expressions and select_for_update() (with nowait, skip_locked and of) really do, where deadlocks come from, and when optimistic locking with a version column is the better tool.
The setup: a product and its stock
All examples use these models. Money is stored in tiyin as an integer, and the database itself refuses negative stock.
from django.conf import settings
from django.db import models
from django.db.models import Q
class Product(models.Model):
name = models.CharField(max_length=200)
price = models.BigIntegerField() # in tiyin
stock = models.IntegerField(default=0)
version = models.PositiveIntegerField(default=0)
class Meta:
constraints = [
models.CheckConstraint(
condition=Q(stock__gte=0),
name="product_stock_non_negative",
),
]
class Order(models.Model):
product = models.ForeignKey(Product, on_delete=models.PROTECT)
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)
quantity = models.PositiveIntegerField()
created_at = models.DateTimeField(auto_now_add=True)
The CheckConstraint is your last line of defence: whatever the application gets wrong, PostgreSQL will reject stock < 0 with an IntegrityError. It will not prevent overselling to two people when stock goes from 1 to 0 twice, though, so we still need correct application logic.
Why the naive version breaks
This is the code that ends up in almost every project at first:
class OutOfStock(Exception):
pass
def buy(product_id: int, qty: int, user) -> Order:
product = Product.objects.get(pk=product_id)
if product.stock < qty:
raise OutOfStock
product.stock -= qty
product.save()
return Order.objects.create(product=product, user=user, quantity=qty)
Run it with two concurrent requests on a product with stock = 1:
| Time | Request A | Request B |
|---|---|---|
| t1 | SELECT ... WHERE id = 42 → stock = 1 |
|
| t2 | SELECT ... WHERE id = 42 → stock = 1 |
|
| t3 | UPDATE ... SET stock = 0 |
|
| t4 | UPDATE ... SET stock = 0 |
|
| t5 | creates order | creates order |
The check product.stock < qty and the write product.save() are two separate steps, and the decision is made in Python on a copy of the data that may already be outdated. Between t1 and t3 nothing stops another process from reading the same row.
Wrapping the function in transaction.atomic does not help. Under PostgreSQL's default READ COMMITTED isolation, a plain SELECT takes no row lock, so both transactions read the same value happily. A transaction gives you all-or-nothing, not "nobody else may touch this row". There is a second trap here too: save() without update_fields writes every column, so it can also overwrite a concurrent price change made by an admin.
The mechanism: how PostgreSQL row locks work
To fix this we need to understand what PostgreSQL does when two transactions want the same row.
- Every
UPDATE,DELETEandSELECT ... FOR UPDATEtakes a row-level lock on the rows it touches. The lock is recorded in the row itself (in the tuple header), not in a separate table, so locking millions of rows does not exhaust memory. - Row locks are held until the transaction ends —
COMMITorROLLBACK. There is no way to release one earlier. - If transaction B tries to lock a row already locked by A, B waits. It is not waiting on the row directly but on A's transaction ID; that is why deadlock messages say "waits for ShareLock on transaction 9811".
- When A commits, B wakes up. In
READ COMMITTED, B does not use its old snapshot for that row: it takes the latest committed version of the row and re-checks itsWHEREcondition against it. If the row no longer matches, B skips it.
Point 4 is the key to the simplest fix. Plain SELECT does not participate in any of this: readers never block writers and writers never block readers. That is great for throughput and exactly why the naive code is broken.
Row lock strengths in PostgreSQL, from strongest to weakest: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. A normal UPDATE that does not change key columns takes FOR NO KEY UPDATE. We will come back to this.
Fix 1: an atomic UPDATE with F()
If the whole "check and change" can be expressed as one SQL statement, let the database do it:
from django.db import transaction
from django.db.models import F
@transaction.atomic
def buy(product_id: int, qty: int, user) -> Order:
updated = (
Product.objects
.filter(pk=product_id, stock__gte=qty)
.update(stock=F("stock") - qty)
)
if updated == 0:
raise OutOfStock
return Order.objects.create(product_id=product_id, user=user, quantity=qty)
Django turns this into a single statement:
UPDATE "shop_product"
SET "stock" = ("shop_product"."stock" - 1)
WHERE "shop_product"."id" = 42
AND "shop_product"."stock" >= 1;
Replay the race. A runs the UPDATE, locks row 42 and sets stock to 0. B runs the same UPDATE, hits A's lock and waits. A commits. B wakes up, takes the new row version (stock = 0), re-checks stock >= 1 — false — and updates zero rows. update() returns 0, we raise OutOfStock. No Python-side read, no window.
If the decision can live in the
WHEREclause, you don't need an explicit lock: the UPDATE's own row lock plus the re-check in READ COMMITTED does the job.
This is the cheapest correct option: one round trip, the lock is held for a very short time. The transaction.atomic is still needed so that the stock decrement and the order creation commit or roll back together.
A small pitfall when using F() on an instance instead of a queryset:
product = Product.objects.get(pk=42)
product.stock = F("stock") - 1
product.save(update_fields=["stock"])
print(product.stock) # Django 5.x: CombinedExpression, not a number
product.refresh_from_db(fields=["stock"])
print(product.stock) # the real value from the database
In Django 5.x the attribute still holds the expression after save(). If you save the instance again, the expression is applied again and the stock drops twice. Whatever version you run, refresh_from_db() after such a save makes the intent explicit.
Fix 2: select_for_update when logic doesn't fit into one UPDATE
Real rules are rarely one condition. Say each user may buy at most two tickets per event. Now we must read stock, count the user's previous orders, and only then write. That is where select_for_update() comes in:
from django.db import transaction
from django.db.models import Sum
from django.db.models.functions import Coalesce
MAX_PER_USER = 2
class LimitExceeded(Exception):
pass
@transaction.atomic
def buy(product_id: int, qty: int, user) -> Order:
product = Product.objects.select_for_update().get(pk=product_id)
if product.stock < qty:
raise OutOfStock
bought = Order.objects.filter(product=product, user=user).aggregate(
total=Coalesce(Sum("quantity"), 0)
)["total"]
if bought + qty > MAX_PER_USER:
raise LimitExceeded
product.stock -= qty
product.save(update_fields=["stock"])
return Order.objects.create(product=product, user=user, quantity=qty)
The SQL Django sends for the first line:
SELECT "shop_product"."id", "shop_product"."name", ...
FROM "shop_product"
WHERE "shop_product"."id" = 42
LIMIT 21
FOR UPDATE;
(LIMIT 21 is how get() detects "more than one object returned".) Now the read itself takes a lock. Request B blocks on this SELECT until A commits, and then reads the fresh row with stock already decremented. All purchases of product 42 are serialized through this one row, which also makes the per-user limit check safe: no other order for this product can be created while we hold the lock.
Important details:
select_for_update()must be evaluated inside a transaction. Outsideatomicin autocommit mode, Django raisesTransactionManagementError. Querysets are lazy, so what matters is where the query runs, not where it is built.- The lock lives until the end of the outermost
atomicblock, not the inner one. A nestedatomiccreates a savepoint; it does not release locks. - Use
save(update_fields=[...])so you write only what you changed.
nowait: fail instead of waiting
By default, the second request waits as long as the first holds the lock. Sometimes it is better to answer immediately: "someone else is processing this, try again".
from django.db import OperationalError, transaction
from psycopg import errors as pg_errors
class Busy(Exception):
pass
def buy_or_busy(product_id: int, qty: int, user) -> Order:
try:
with transaction.atomic():
product = Product.objects.select_for_update(nowait=True).get(
pk=product_id
)
if product.stock < qty:
raise OutOfStock
product.stock -= qty
product.save(update_fields=["stock"])
return Order.objects.create(product=product, user=user, quantity=qty)
except OperationalError as exc:
if isinstance(exc.__cause__, pg_errors.LockNotAvailable):
raise Busy from exc
raise
With nowait=True PostgreSQL adds FOR UPDATE NOWAIT and, if the row is locked, raises an error with SQLSTATE 55P03 (lock_not_available). Django wraps it in OperationalError, and the original psycopg exception is in __cause__. Checking the cause is better than catching every DatabaseError and pretending they all mean "busy". This example assumes psycopg 3; with psycopg2 the class is psycopg2.errors.LockNotAvailable.
skip_locked: give me any free row
For seat booking where any free seat in the hall is fine, waiting on a locked seat makes no sense — just take the next one:
from django.conf import settings
from django.db import models, transaction
class Seat(models.Model):
event = models.ForeignKey("Event", on_delete=models.CASCADE)
row = models.PositiveSmallIntegerField()
number = models.PositiveSmallIntegerField()
holder = models.ForeignKey(
settings.AUTH_USER_MODEL,
null=True,
blank=True,
on_delete=models.SET_NULL,
)
@transaction.atomic
def take_any_seat(event_id: int, user) -> Seat | None:
seat = (
Seat.objects.select_for_update(skip_locked=True)
.filter(event_id=event_id, holder__isnull=True)
.order_by("row", "number")
.first()
)
if seat is None:
return None
seat.holder = user
seat.save(update_fields=["holder"])
return seat
SELECT ... FROM "shop_seat"
WHERE "shop_seat"."event_id" = 7
AND "shop_seat"."holder_id" IS NULL
ORDER BY "shop_seat"."row" ASC, "shop_seat"."number" ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;
Twenty concurrent requests get twenty different seats without waiting for each other. The same pattern powers simple job queues on top of PostgreSQL.
The catch: None here does not mean "sold out". It means "no free seat that isn't locked right now". If you show "Sold out" based on it, you may lie to the user while other transactions are about to roll back. For an honest "sold out", run a separate plain count.
nowait and skip_locked are mutually exclusive: passing both raises ValueError.
Deadlocks: when two locks wait for each other
Locks bring a new failure mode. A wallet transfer:
from django.conf import settings
from django.db import models, transaction
class Wallet(models.Model):
owner = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)
balance = models.BigIntegerField() # in tiyin
class InsufficientFunds(Exception):
pass
@transaction.atomic
def transfer(from_id: int, to_id: int, amount: int) -> None:
src = Wallet.objects.select_for_update().get(pk=from_id)
dst = Wallet.objects.select_for_update().get(pk=to_id)
if src.balance < amount:
raise InsufficientFunds
src.balance -= amount
dst.balance += amount
src.save(update_fields=["balance"])
dst.save(update_fields=["balance"])
Now user 1 sends money to user 2, and at the same moment user 2 sends money to user 1:
| Time | transfer(1, 2) | transfer(2, 1) |
|---|---|---|
| t1 | locks wallet 1 | |
| t2 | locks wallet 2 | |
| t3 | wants wallet 2 → waits for B | |
| t4 | wants wallet 1 → waits for A |
Neither can proceed. PostgreSQL does not check for deadlocks on every wait; it waits deadlock_timeout (1 second by default), then runs the detector, finds the cycle and aborts one of the transactions:
ERROR: deadlock detected
DETAIL: Process 4121 waits for ShareLock on transaction 9811; blocked by process 4122.
Process 4122 waits for ShareLock on transaction 9810; blocked by process 4121.
HINT: See server log for query details.
The other transaction proceeds. Nothing is corrupted, but one user gets a 500 and the request took at least a second.
The cure is a consistent lock order. If every transaction locks wallets in ascending pk, a cycle cannot form: whoever got the smaller id first, the other one waits for it and never holds the larger one in the meantime.
@transaction.atomic
def transfer(from_id: int, to_id: int, amount: int) -> None:
if from_id == to_id:
raise ValueError("cannot transfer to the same wallet")
locked = (
Wallet.objects.select_for_update()
.filter(pk__in=[from_id, to_id])
.order_by("pk")
)
wallets = {w.pk: w for w in locked}
src, dst = wallets[from_id], wallets[to_id]
if src.balance < amount:
raise InsufficientFunds
src.balance -= amount
dst.balance += amount
src.save(update_fields=["balance"])
dst.save(update_fields=["balance"])
One query with ORDER BY id ... FOR UPDATE locks the rows in the sorted order. The same rule applies everywhere: a batch of update() calls in a loop over unsorted ids, Celery tasks that touch an order and then a product while views do it the other way round. Pick an order and stick to it.
Even with discipline, deadlocks and serialization failures can still appear (foreign keys, triggers, SERIALIZABLE transactions). They are safe to retry — but only the whole transaction:
import time
from django.db import OperationalError, connection
from psycopg import errors as pg_errors
RETRYABLE = (pg_errors.DeadlockDetected, pg_errors.SerializationFailure)
def with_retry(fn, *args, attempts: int = 3, **kwargs):
if connection.in_atomic_block:
raise RuntimeError("with_retry must wrap the outermost transaction")
for attempt in range(1, attempts + 1):
try:
return fn(*args, **kwargs)
except OperationalError as exc:
if attempt == attempts or not isinstance(exc.__cause__, RETRYABLE):
raise
time.sleep(0.05 * attempt)
with_retry(transfer, 1, 2, 5_000_000) # 50 000 so'm in tiyin
Retrying inside an outer atomic is pointless: after the error the outer transaction is already aborted. Also make sure the function has no side effects outside the database (emails, calls to a payment provider) before commit, or the retry will repeat them.
Optimistic locking: a version column
Everything above is pessimistic: we lock first, then work. Sometimes that is impossible. An admin opens the product edit form, thinks for five minutes and presses "Save". You cannot hold a database lock across two HTTP requests and five minutes of thinking. Meanwhile a second admin changed the price, and the first one silently overwrites it.
Optimistic locking assumes conflicts are rare and only detects them at write time. The form carries the version it was loaded with:
from django.db.models import F
class ConcurrentUpdate(Exception):
pass
def update_price(product_id: int, new_price: int, expected_version: int) -> None:
updated = (
Product.objects
.filter(pk=product_id, version=expected_version)
.update(price=new_price, version=F("version") + 1)
)
if updated == 0:
raise ConcurrentUpdate
If someone saved in between, version has already moved on, the WHERE matches nothing, update() returns 0, and you show "This product was changed by someone else, reload the page". No locks are held while the user thinks.
| Approach | Holds lock | Good for | Cost under contention |
|---|---|---|---|
update() + F() |
for one statement | counters, stock, balances where the rule fits in WHERE |
minimal |
select_for_update() |
until commit | multi-step logic inside one request | requests queue up on hot rows |
nowait |
until commit | "busy, try later" UX | failures instead of waits |
skip_locked |
until commit | any-free-item booking, job queues | none, but no global view |
version column |
none | long user edits, APIs with If-Match |
conflicts surface as errors, user must retry |
Pitfalls in production
- Network calls inside the lock. Calling a payment provider or sending an SMS between
select_for_update()and commit means the row is locked for as long as the external API is slow. Every other buyer queues behind it. Do the external call outside, or usetransaction.on_commit(). select_relatedlocks more than you think. With joins,FOR UPDATElocks rows in all joined tables. Useof:
order = (
Order.objects.select_related("product")
.select_for_update(of=("self",))
.get(pk=order_id)
)
On a nullable relation, select_related produces an outer join, and PostgreSQL refuses FOR UPDATE on the nullable side; of=("self",) fixes that too.
- Foreign keys and lock strength. Inserting an Order takes FOR KEY SHARE on the referenced product row. That conflicts with FOR UPDATE, so other transactions inserting orders for the same product wait. If you are not changing the primary key, select_for_update(no_key=True) takes FOR NO KEY UPDATE, which does not block those inserts.
- Endless waiting. A lock wait has no limit by default. Set a ceiling per transaction:
from django.db import connection, transaction
with transaction.atomic():
with connection.cursor() as cursor:
cursor.execute("SET LOCAL lock_timeout = '2s'")
product = Product.objects.select_for_update().get(pk=product_id)
...
On timeout PostgreSQL raises the same 55P03 error as nowait.
- Tests that never see the race. Django's TestCase wraps every test in a transaction and runs everything on one connection, so concurrency bugs are invisible. Use TransactionTestCase with real threads; each thread gets its own connection:
import threading
from django.contrib.auth import get_user_model
from django.db import connection
from django.test import TransactionTestCase
from shop.models import Product
from shop.services import OutOfStock, buy
User = get_user_model()
class BuyRaceTest(TransactionTestCase):
def test_last_item_is_sold_once(self):
product = Product.objects.create(name="Ticket", price=100_000, stock=1)
users = [User.objects.create(username=f"u{i}") for i in range(2)]
barrier = threading.Barrier(2)
results = []
def worker(user):
try:
barrier.wait()
buy(product.pk, 1, user)
results.append("ok")
except OutOfStock:
results.append("out")
finally:
connection.close()
threads = [threading.Thread(target=worker, args=(u,)) for u in users]
for t in threads:
t.start()
for t in threads:
t.join()
self.assertCountEqual(results, ["ok", "out"])
product.refresh_from_db()
self.assertEqual(product.stock, 0)
The race is non-deterministic: the broken version will sometimes pass this test. Run it in a loop, and also reproduce it by hand in two psql windows with BEGIN — it is the fastest way to see who waits for whom.
- Measuring instead of guessing. If you suspect lock contention, look at pg_stat_activity (wait_event_type = 'Lock') and pg_locks, and enable log_lock_waits so PostgreSQL logs any wait longer than deadlock_timeout.
Checklist
- Is there a read in Python followed by a write based on it? That is a race candidate.
- If the rule fits in a
WHERE, usefilter(...).update(F(...))and check the returned row count. - Otherwise use
select_for_update()insidetransaction.atomic, and write withupdate_fields. - Lock several rows in a fixed order (
order_by("pk")) in one query. - Retry deadlocks and serialization failures around the whole transaction, never inside it.
- No external HTTP calls while holding row locks; set
lock_timeout. - Use a
versioncolumn for edits that span several HTTP requests. - Keep a
CheckConstraintas the final safety net. - Test concurrency with
TransactionTestCaseand threads, or twopsqlsessions.
Takeaway
A race condition is any place where a decision is made on data that can change before you write. The fix is always to move the decision to where the database can enforce it: into the WHERE of an atomic UPDATE, behind a row lock taken with select_for_update(), or into a version check. Lock in a consistent order, keep lock time short, and retry whole transactions when PostgreSQL breaks a deadlock.
// comments
Loading comments...