skip to content

In Django, how does select_for_update() stop two flash-sale buyers from both taking the last unit, and why must it run inside transaction.atomic()?

level: juniorimportance: must knowfreq 58%

answer

  1. read, check, write — twice at once
  2. SELECT ... FOR UPDATE
  3. the lock lives as long as the transaction
  4. autocommit would release it instantly

basics

~20 s

select_for_update() makes the query lock the rows it returns until the transaction ends, so a second buyer's read waits until the first commits. Outside atomic, autocommit would release the lock at once, so Django raises TransactionManagementError.

solid answer

~50 s

The race is read-check-write: two requests both read `stock == 1`, both pass the check, both save `0`, and two units are sold. `Product.objects.select_for_update().get(pk=pk)` emits `SELECT ... FOR UPDATE`, so the first transaction locks the row and the second one's identical query blocks until the first commits — then it reads the new stock and fails the check. The lock lasts until the end of the transaction, and in Django's default autocommit mode each query is its own transaction, so the lock would vanish as soon as the SELECT finished. That is why evaluating a `select_for_update()` QuerySet in autocommit raises `TransactionManagementError: select_for_update cannot be used outside of a transaction.` Wrap the read, the check and the save in one `transaction.atomic()` block. On SQLite the call is a silent no-op; a `TestCase` hides the error because every test already runs in a transaction.

code

python · 20 lines
python
from django.db import models, transaction


class Product(models.Model):
    sku = models.CharField(max_length=32, unique=True)
    stock = models.PositiveIntegerField()


class SoldOut(Exception):
    pass


def buy(product_id: int, qty: int) -> None:
    with transaction.atomic():
        product = Product.objects.select_for_update().get(pk=product_id)
        if product.stock < qty:
            raise SoldOut(product_id)  # rollback releases the lock
        product.stock -= qty
        product.save(update_fields=["stock"])
    # lock released here, at commit

go deeper

for a junior

Recall the read-check-write race, that select_for_update() locks the selected rows, and that it must run inside transaction.atomic().

for a middle

Explain lazy evaluation of the lock, why autocommit would make the lock useless, and the SQLite and TestCase caveats.

for a senior

Keep locked sections short, avoid external calls under the lock, and make sure tests exercise real transaction behaviour.

for a principal

Weigh pessimistic locking against lock-free designs for hot rows, given connection pressure during traffic spikes.

## The flash-sale race A flash sale decrements `Product.stock` under heavy concurrency. The natural code reads, checks and writes: ```python product = Product.objects.get(pk=product_id) if product.stock >= qty: product.stock -= qty product.save(update_fields=["stock"]) ``` Two requests running this at the same moment can both read `stock = 1`, both pass the check and both write `0`. The second write overwrites the first — a **lost update** — and the shop has sold two units it only had once. ## What `select_for_update()` does `QuerySet.select_for_update()` returns a QuerySet that, when **evaluated**, runs `SELECT ... FOR UPDATE` on backends that support it. The database locks every row the query returns: - another transaction running `select_for_update()` on the same row **waits** until the lock is released; - another transaction trying to update or delete the row also waits; - ordinary `SELECT`s without `FOR UPDATE` are not blocked on PostgreSQL — they read the last committed version. Like any QuerySet it is lazy: building it takes no lock; iterating it, calling `get()` or `first()` does. ```python from django.db import transaction def buy(product_id, qty): with transaction.atomic(): product = Product.objects.select_for_update().get(pk=product_id) if product.stock < qty: raise SoldOut(product_id) product.stock -= qty product.save(update_fields=["stock"]) ``` Now the second buyer's `get()` blocks until the first buyer's block commits, then reads `stock = 0` and raises `SoldOut`. ## Why it needs `transaction.atomic()` A row lock lasts **until the end of the transaction** that took it. Django runs in **autocommit** by default, where each query is a transaction of its own; a `SELECT ... FOR UPDATE` in autocommit would lock the row and release it the instant the statement finished, protecting nothing. Django therefore refuses: evaluating the QuerySet while the connection is in autocommit raises `TransactionManagementError: select_for_update cannot be used outside of a transaction.` The check happens when the SQL is compiled, so it fires at evaluation time, not when you call `select_for_update()`. The lock is released only when the **outermost** atomic block commits or rolls back — leaving an inner block does not release it. ## Backend and test caveats | Situation | What happens | |---|---| | PostgreSQL, Oracle, MySQL, MariaDB inside `atomic` | Row lock taken | | Same backends in autocommit | `TransactionManagementError` | | SQLite | `FOR UPDATE` is not added; no error, no lock | | Code run inside `django.test.TestCase` | No error even without `atomic`, since each test runs in a transaction | | `select_related()` over a nullable relation | `NotSupportedError`, because `FOR UPDATE` cannot lock the nullable side of an outer join | The `TestCase` row matters: a test suite can pass while production raises. Django's docs recommend `TransactionTestCase` for testing `select_for_update()` properly. ## Keeping the lock cheap 1. Lock as late as possible in the block and commit as soon as possible — every waiting buyer holds a connection. 2. Never make an external call (payment provider, email) while holding the lock; schedule it after commit. 3. Lock only the rows you will change; a broad filter locks everything it returns. 4. For a single counter, a conditional `update()` with no prior read may avoid the lock entirely — a separate design choice with its own trade-offs. ## What interviewers want to hear - The race named precisely: read-check-write, lost update, oversold stock. - The mechanism: `SELECT ... FOR UPDATE`, taken at evaluation, held until the transaction ends. - The Django-specific rule: it only makes sense inside `transaction.atomic()`, and Django enforces that with `TransactionManagementError` on backends that support locking. - The traps: SQLite silently ignores it, and `TestCase` hides a missing `atomic()`. A candidate who adds "and I would keep external calls out of the locked section" has usually operated this code in production.

  • The flash-sale test passes in a Django TestCase but production raises TransactionManagementError. Why?
    `TestCase` wraps every test in a transaction, so the connection is never in autocommit and Django's check never fires, even if your code forgot `atomic()`. In production the same call runs in autocommit and raises. Test locking code with `TransactionTestCase`, which leaves transactions to your code.
  • When exactly is the row locked: at select_for_update() or later?
    Later. `select_for_update()` only marks the QuerySet; the `SELECT ... FOR UPDATE` runs, and the lock is taken, when the QuerySet is evaluated — by `get()`, iteration, `list()` and so on. The autocommit check also happens at that point.

It is a fitting-room key: the first shopper takes the key for that room, the next one queues at the door, and the key only goes back to the hook when the first shopper checks out — which is why handing it back the moment you open the door (autocommit) would be pointless.

saying these in an interview costs you the question

  • select_for_update() locks the row as soon as it is called
  • The lock is released when the save() call returns
  • select_for_update() works the same on SQLite
  • Passing tests in TestCase prove the atomic block is in place
  • select_for_update() blocks every plain SELECT on the row