All notes
Programming

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.

  1. Every UPDATE, DELETE and SELECT ... FOR UPDATE takes 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.
  2. Row locks are held until the transaction ends — COMMIT or ROLLBACK. There is no way to release one earlier.
  3. 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".
  4. 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 its WHERE condition 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 WHERE clause, 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. Outside atomic in autocommit mode, Django raises TransactionManagementError. 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 atomic block, not the inner one. A nested atomic creates 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 use transaction.on_commit().
  • select_related locks more than you think. With joins, FOR UPDATE locks rows in all joined tables. Use of:
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, use filter(...).update(F(...)) and check the returned row count.
  • Otherwise use select_for_update() inside transaction.atomic, and write with update_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 version column for edits that span several HTTP requests.
  • Keep a CheckConstraint as the final safety net.
  • Test concurrency with TransactionTestCase and threads, or two psql sessions.

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...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯