In Django's select_for_update(), what do nowait, skip_locked, of and no_key change, and when would a flash-sale service use each?
answer
- wait, fail fast or skip
- which tables get locked
- a weaker lock for foreign keys
- not every backend supports every option
basics
~20 snowait=True raises DatabaseError instead of waiting; skip_locked=True silently skips locked rows; of=(...) limits locking to named models in a select_related join; no_key=True takes PostgreSQL's weaker FOR NO KEY UPDATE lock that still lets other rows reference the locked one.
solid answer
~50 sBy default a `select_for_update()` query waits for conflicting locks. `nowait=True` makes it fail immediately with `DatabaseError` — good for returning "try again" to a buyer instead of piling up blocked requests. `skip_locked=True` leaves locked rows out of the result, which lets many workers each claim a *different* free row, such as one reserved stock unit per buyer; `nowait` and `skip_locked` together raise `ValueError`. `of=("self",)` restricts the lock to the queryset's own model when `select_related()` joins others, so a checkout does not also lock the shared `Product` row. `no_key=True`, PostgreSQL only, takes `FOR NO KEY UPDATE`, which still allows other transactions to insert rows whose foreign key points at the locked row. On a backend with `FOR UPDATE` but without a given option, Django raises `NotSupportedError` instead of silently locking differently; on SQLite the whole call is a no-op.
go deeper
Remember the four options: nowait fails fast, skip_locked skips busy rows, of chooses tables, no_key weakens the lock.
Explain the error each option produces, the nowait and skip_locked conflict, and the default locking of select_related joins.
Pick the option that fits the contention pattern, redesign hot counters into claimable rows, and handle NotSupportedError across backends.
Decide whether hot-row contention is solved with lock options or with a data model that removes the shared row.
## Default behaviour `QuerySet.select_for_update(nowait=False, skip_locked=False, of=(), no_key=False)` locks every row the query returns until the transaction ends. If another transaction already holds a conflicting lock, the query **waits**. The four options change who waits, what is locked and how strongly. ## `nowait=True`: fail fast The query does not wait; if any selected row is locked, evaluating the QuerySet raises `django.db.DatabaseError`. In a flash sale, a buyer whose request would otherwise queue behind hundreds of others gets an immediate "busy, try again" and the server keeps its connections free. ```python from django.db import DatabaseError, transaction try: with transaction.atomic(): product = Product.objects.select_for_update(nowait=True).get(pk=pk) ... except DatabaseError: return busy_response() ``` The `try` sits **around** the atomic block: after the error the transaction must be rolled back before anything else runs. ## `skip_locked=True`: take whatever is free Locked rows are simply left out of the result — no waiting, no error. This suits "claim any one of N" problems. If each sellable unit is its own `StockUnit` row, a buyer can grab a free one without contending on a single counter: ```python unit = (StockUnit.objects.select_for_update(skip_locked=True) .filter(product_id=pk, order__isnull=True) .order_by("pk") .first()) ``` `None` means every unit is sold or currently being claimed. The trade-off: results are deliberately inconsistent — a skipped row is not "gone", just busy. `nowait` and `skip_locked` are **mutually exclusive**; passing both raises `ValueError`. ## `of=(...)`: choose which tables to lock When the query uses `select_related()`, Django locks rows of **every joined model** by default. `of` takes the same field-path syntax as `select_related()`, with `"self"` for the queryset's own model: ```python Order.objects.select_related("product").select_for_update(of=("self",)) ``` This locks the order but not the heavily shared product row. For multi-table inheritance, parent rows are locked only if you name the parent link, such as `"place_ptr"`. ## `no_key=True`: a weaker lock (PostgreSQL) A plain `FOR UPDATE` lock also blocks other transactions from inserting rows that **reference** the locked row through a foreign key. `no_key=True` emits `FOR NO KEY UPDATE`, which still serialises updates to the row but lets, for example, new `OrderLine` rows pointing at the locked `Product` be inserted concurrently. Use it when you will update non-key columns such as `stock` but not the primary key. ## Backend support | Option | PostgreSQL | Oracle | MySQL | MariaDB | SQLite | |---|---|---|---|---|---| | plain lock | yes | yes | yes | yes | no-op | | `nowait` | yes | yes | yes | yes | — | | `skip_locked` | yes | yes | yes | yes | — | | `of` | yes | yes | yes | no | — | | `no_key` | yes | no | no | no | — | On a backend that supports `FOR UPDATE` but lacks a particular option, Django raises `NotSupportedError` rather than emitting different SQL, so code never blocks unexpectedly. SQLite is the exception: it has no `FOR UPDATE` at all, so the whole call, options included, is a no-op there. Oracle additionally rejects `LIMIT`/`OFFSET` (slicing) combined with `select_for_update()`. ## Choosing quickly - Buyers should not queue → `nowait`. - Many workers or buyers, many interchangeable rows → `skip_locked`. - A join drags in a hot shared row → `of`. - Child rows keep being inserted against the locked parent → `no_key`. ## Testing option behaviour Lock options only show their effect with two real transactions, so they cannot be exercised inside a single `TestCase` test, which runs everything in one transaction on one connection. Use `TransactionTestCase` with a second thread or connection that holds the lock, then assert that `nowait` raises and `skip_locked` returns the next free row. Run those tests against the same database engine as production: SQLite would pass them vacuously.
- Why does skip_locked suit a pool of individual stock-unit rows better than a single stock counter?With one counter row there is nothing to skip to: a skipped row just looks like no stock. With one row per unit, each buyer's `select_for_update(skip_locked=True)...first()` claims a different free unit, so buyers proceed in parallel instead of queueing on one lock.
- What happens if you call select_for_update(nowait=True, skip_locked=True)?Django raises `ValueError` immediately, when the QuerySet method is called: the options contradict each other — one says fail if anything is locked, the other says ignore locked rows.
saying these in an interview costs you the question
- nowait=True returns an empty result when rows are locked
- skip_locked=True waits and then returns every row
- select_related() locks only the main model's rows by default
- no_key=True is supported on every backend
- On MySQL, no_key=True silently falls back to a normal FOR UPDATE lock