Handling Concurrency in Python Services — Part 3: Locking Patterns That Ship

This is Part 3 of a three-part series. Part 1 showed why Python locks fail across app servers; Part 2 walked through the four isolation levels and their anomalies.

Part 2 ended with a pragmatic posture: stay on the database's default isolation level and protect the few genuinely contended rows explicitly. This part is about how — the three patterns that cover nearly every inventory-shaped problem, in the order you should reach for them.

Pattern 1: the atomic conditional update

The lost update from Part 1 happened because the read and the write were two separate steps with a gap between them. The cheapest fix is to remove the gap entirely — push both the check and the change into one statement:

async def buy_book(conn, book_id, quantity):
    result = await conn.execute(
        """
        update books
        set stock = stock - $2
        where id = $1 and stock >= $2
        """,
        book_id, quantity,
    )
    return result.rowcount > 0   # did we win?

stock = stock - $2 is evaluated by the database under the row lock the UPDATE itself takes, and stock >= $2 guarantees it never goes negative. Two concurrent buyers of the last copy? The database serializes the two UPDATEs; one gets rowcount = 1, the other gets rowcount = 0 and a clean "sold out". No isolation-level changes, no explicit locks, no retries.

The Django ORM spelling of the same idea uses F() expressions:

from django.db.models import F

updated = Book.objects.filter(id=book_id, stock__gte=qty).update(
    stock=F("stock") - qty
)
if updated == 0:
    raise SoldOut()

The anti-pattern it replaces — book.stock -= qty; book.save() — is the read-modify-write race, dressed up in ORM syntax.

Use this whenever the decision fits in one statement. For inventory it almost always does. The next two patterns exist for when it doesn't.

Pattern 2: pessimistic locking with SELECT … FOR UPDATE

Sometimes the logic between read and write is genuinely multi-step: check stock, apply a customer-specific discount rule, verify a purchase limit, then decrement. You need the row to hold still while you think. That's what SELECT ... FOR UPDATE does — it takes the row lock at read time:

async with pool.acquire() as conn:
    async with conn.transaction():
        row = await conn.fetchrow(
            "select stock, price from books where id = $1 for update",
            book_id,
        )
        if row is None or row["stock"] < qty:
            return {"success": False, "reason": "sold_out"}

        total = apply_discount(row["price"], user_id) * qty
        await conn.execute(
            "update books set stock = stock - $2 where id = $1",
            book_id, qty,
        )
        await conn.execute(
            "insert into orders (book_id, user_id, total) values ($1, $2, $3)",
            book_id, user_id, total,
        )

SQLAlchemy spells it select(Book).with_for_update(); Django spells it Book.objects.select_for_update().get(id=book_id) (inside transaction.atomic()).

Two rules keep this pattern safe:

• The lock is held until commit, so keep the transaction short. Everything between the FOR UPDATE and the commit is a critical section that every other buyer of that book waits behind. Pure computation is fine; a slow query against another table is borderline; an external API call (payment gateway, email service) inside the lock is never acceptable — you'd be pinning a database row on a third party's latency. Record intent in the transaction and let a worker perform the side effect after commit.
• Lock rows in a consistent order. If one code path locks book A then book B, and another locks B then A, two concurrent requests can each grab one row and wait forever for the other — a deadlock. Postgres will detect and kill one, but the fix is ordering (e.g., always lock by ascending id).

If you'd rather fail fast than queue, FOR UPDATE NOWAIT raises immediately when the row is taken, and FOR UPDATE SKIP LOCKED skips contended rows entirely — the backbone of Postgres-based job queues.

Pattern 3: optimistic locking with a version column

Pessimistic locking makes everyone queue, which is wasteful when conflicts are rare — say, two staff members occasionally editing the same book's metadata. Optimistic locking takes no lock at all; it detects conflicts at write time instead:

Add a version integer to the table. Read it along with the data, and make every update conditional on it:

row = await conn.fetchrow(
    "select stock, version from books where id = $1", book_id
)

result = await conn.execute(
    """
    update books
    set stock = $2, version = version + 1
    where id = $1 and version = $3
    """,
    book_id, new_stock, row["version"],
)
if result.rowcount == 0:
    # someone else won — re-read and retry, or surface a conflict
    raise ConflictRetry()

If another transaction committed in between, the version = $3 predicate matches nothing, rowcount is 0, and you know your read was stale. Nobody ever waited on a lock; the loser retries or shows the user a "record was modified" message. SQLAlchemy automates this via version_id_col; in HTTP APIs the same idea surfaces as ETag / If-Match headers.

The trade-off mirrors pessimistic locking exactly: great when conflicts are rare, miserable on a hot row — a flash sale on one book would have every request retrying in a stampede. That's why inventory wants Pattern 1 or 2, and metadata edits want Pattern 3.

What about distributed locks?

Part 1 dismissed distributed locks (Redis SET NX, ZooKeeper, Postgres advisory locks) for inventory, and the reason should now be concrete: the database already serializes writers to a row, with correctness guarantees a cache-based lock can't match (a Redis lock with a TTL can expire while its holder is still working). Reach for a distributed lock only when the resource you're serializing isn't a database row — a nightly job that must run on exactly one instance, a rate-limited third-party API, a file on shared storage. For rows, the row lock is the distributed lock.

Choosing, in one pass

1. Can the check and the change fit in one UPDATE ... WHERE? Do that. It's the fastest and the least code. (Inventory, counters, balances.)
2. Multi-step decision on a contended row? SELECT ... FOR UPDATE, short transaction, no external calls inside, consistent lock order.
3. Rare conflicts, no queuing desired? Version column, detect stale writes, retry or surface the conflict.
4. Set-level invariants no row lock can express? That's Serializable isolation from Part 2 — budget for retries.
5. Not a row at all? Now, and only now, a distributed lock.

The through-line of this series: your Python process is the wrong place to solve concurrency. threading.Lock guards one process; the patterns above guard the data itself, no matter how many app servers, workers, or cron jobs are hitting it. Put the coordination where the state lives — in the database — and keep the critical sections short.