Two Users Hit "Buy" at Once: Concurrency & Transactions for Vibe Projects

Cover of the concurrency and transactions field guide: unprotected races oversell, transaction-plus-lock protection keeps stock non-negative" alt="Guide cover: unprotected race conditions cause overselling, while transaction-plus-lock protection keeps stock from going negative">Cover: the same two concurrent orders — unprotected they oversell; with transactions and locks, stock never goes negative.

Let's draw the boundary first. This guide is about database and business-layer concurrency: how to keep money and inventory correct when two requests touch the same row at the same time. It has nothing to do with the cycle49 news piece anthropic-claude-dynamic-workflows-october-2026 — that one was about agent orchestration concurrency (running 1,000 subagents in one go: how tasks get split up and scheduled in parallel), i.e. parallel compute. This guide is about data races: two writers colliding on the same piece of data. Same word, "concurrency" — two completely different subjects.

One more cross-link: the in-site guide vibe-project-credits-ledger-billing-guide (the credits ledger) has a section on idempotent debits — that's replay protection, solving "the same request arrived twice". Section 4 of this guide is the generalized version of that idea, while Sections 1–3 solve "two different requests arrived at the same time". The two concerns are orthogonal, and a real system usually needs both.

Why does this lesson matter especially for vibe coders? Because AI-generated CRUD code almost never thinks about concurrency. The checkout endpoint AI writes for you usually looks like this: SELECT the stock, check in code whether it's enough, then UPDATE to decrement. It passes every solo test. Then on your first promo night, two users hit "Buy" in the same second, and your stock goes negative. The AI didn't write wrong logic — it just never told you that "read, compute, write back" is not an atomic operation, and two requests can slip into each other's gaps.

1. Three real-world race condition scenes

Scene 1: Inventory overselling

One item left in stock. Users A and B click "Buy" in the same millisecond. Both requests run SELECT stock and both see 1; both pass the "enough stock" check; both run UPDATE stock = 0. Final stock: 0 — but there are 2 orders. You just sold one item that doesn't exist, and now you're looking at refunds, support tickets, and bad reviews.

Scene 2: Balance double-spend

A wallet holds $100. The user fires two $80 withdrawals at once (two phones, or one click sent twice). Both requests read 100, both pass the "balance ≥ 80" check, both debit. Final balance: -$60 — the user just got $60 for free. The idempotent debit from the credits-ledger guide protects against "the same withdrawal request retried twice"; this protects against "two different withdrawal requests executing simultaneously" — and idempotency keys can't help here, because the keys are legitimately different.

Scene 3: Duplicate submission

The user triple-clicks "Submit", or a network hiccup makes the browser re-send the POST. Three requests with different (or keyless) bodies reach the backend, each INSERTing its own order. The user gets three confirmations; the warehouse ships three parcels. This is the most common race of all, and the one most often dismissed with "frontend debounce fixes it" — debounce doesn't stop network replays or direct API calls.

All three scenes share one root cause: there's a gap between the check and the action. A database only guarantees that a single statement is atomic — not that your "check first, then write" combination is. To make the combination atomic, you need this guide's protagonists: transactions and locks.

Cover of the concurrency and transactions field guide: unprotected races oversell, transaction-plus-lock protection keeps stock non-negative" alt="Race condition sequence diagram: with stock at 1, users A and B both read 1 and both decrement successfully, leaving stock at -1">Figure 1: the race timeline. A and B both read stock=1, and their "read-modify-write" sequences interleave without isolation, ending in oversell.

2. What a transaction actually promises: ACID for a team of one

A transaction is four promises from your database. The plain-language versions are all you need:

• Atomicity: everything inside the transaction happens, or nothing does. When checkout runs "decrement stock + create order + debit balance", any failure rolls back the earlier steps. You never get "money taken, order missing".
• Consistency: the data moves from one valid state to another. Put a CHECK (stock >= 0) constraint on the stock column and any transaction trying to write negative stock gets rejected wholesale.
• Isolation: concurrent transactions can't see each other's intermediate states. This is the core battleground of this guide — and the trickiest one.
• Durability: once COMMIT returns, a power cut won't lose it. The write-ahead log (WAL) handles that; you don't need the details, just the guarantee.

Isolation levels: you really only need to choose between two

The SQL standard defines four isolation levels, distinguished by which concurrency anomalies they allow:

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	possible	possible	possible
Read Committed	no	possible	possible
Repeatable Read	no	no	allowed by the standard, prevented in practice by PG/MySQL*
Serializable	no	no	no

The asterisk deserves a sentence, because it's a classic interview and production pitfall: the standard allows phantom reads under Repeatable Read, but PostgreSQL's Repeatable Read uses snapshot isolation — a transaction sees a consistent snapshot from its start, so phantoms basically never show up; MySQL InnoDB's Repeatable Read uses next-key (gap) locks to prevent them. Both are stricter than the standard, in different ways. Postgres's Serializable uses SSI (serializable snapshot isolation) and resolves conflicts by throwing 40001 serialization_failure — your code must catch it and retry. That's the price of Serializable.

Selection advice (Postgres dialect, solo-team edition):

• Leave the default alone: Postgres defaults to Read Committed, which is enough for 95% of vibe projects. Dirty reads are already prevented; the remaining concurrency problems are better solved precisely with locks and idempotency keys than by tuning isolation.
• Reporting/reconciliation reads: use Repeatable Read. Multiple SELECTs inside one transaction see fully consistent data, undisturbed by writes committed mid-transaction. The reconciliation queries in the credits-ledger guide are the textbook case.
• Don't reach for Serializable lightly: it pushes the "is this serializable?" judgment to runtime and answers conflicts by throwing 40001 for you to retry. Writing correct retry logic usually costs more than just taking a row lock — unless your business genuinely requires "the outcome equals some serial execution" (e.g. complex funds settlement), row locks plus idempotency keys are more controllable.
• Read Uncommitted: in Postgres it behaves exactly like Read Committed (PG never implemented true read-uncommitted); in MySQL it allows dirty reads. Conclusion: don't use it.

One-sentence summary: isolation levels govern what you read; locks govern who writes first. Overselling and double-spends are write-write conflicts — no isolation level prevents them (under Read Committed, two transactions can still both read stock=1). You need locks. That's next.

3. Optimistic vs. pessimistic locking: two answers to "who writes first"

Two transactions want to change the same row; the database must decide the order. Two philosophies:

• Pessimistic locking: "conflict will happen, so I'll lock the row first." SELECT ... FOR UPDATE takes an exclusive row lock; anyone else wanting that row queues up. Most stable under heavy contention, but while the lock is held everyone else waits (throughput drops), and sloppy locking deadlocks.
• Optimistic locking: "conflicts are rare; everyone edits, we check at commit." Add a version integer column (+1 on every update). Updates carry WHERE id = ? AND version = ?; "0 rows matched" means someone got there first — re-read and retry. Lock-free and high-throughput, but under heavy contention the retry storm burns CPU.

Cover of the concurrency and transactions field guide: unprotected races oversell, transaction-plus-lock protection keeps stock non-negative" alt="Decision flowchart for concurrent-write protection: check whether concurrency is possible, then pick optimistic or pessimistic locking by conflict frequency, serialize hot rows through a queue, and layer idempotency keys on cross-system writes">Figure 2: the selection flowchart. First ask "can this be concurrent?", then pick by conflict frequency; hot rows go through a queue; idempotency keys are orthogonal to locks and can be stacked.

Optimistic locking in practice: the version column (SQL dialect)

First, what the raw SQL looks like — once you get this, every ORM spelling is just syntax sugar:

-- Schema: one extra version column, default 0
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- App code: read (stock, version) = (1, 7), computed new stock 0, then:
UPDATE products
SET stock = 0, version = version + 1
WHERE id = 'sku-123' AND version = 7;
-- "1 row matched" → success; "0 rows matched" → someone beat you, re-read and retry

The key insight: the check and the update live in the same statement, which is itself atomic — the gap is welded shut. Note that version = version + 1 must be computed by the database in the SET clause; don't compute version = 8 in code and pass it in — that's still vulnerable to ABA interleavings in extreme cases (rare, but "let the database do the math" is correct at zero cost).

Pessimistic locking in practice: SELECT FOR UPDATE (SQL dialect)

BEGIN;
-- Lock this row: other transactions wanting to change it (or FOR UPDATE it) queue up
SELECT stock FROM products WHERE id = 'sku-123' FOR UPDATE;

-- Check the stock in code, then decrement
UPDATE products SET stock = stock - 1 WHERE id = 'sku-123';
COMMIT;  -- the lock is released here

Three iron rules — break any one and things go wrong:

1. The lock must be taken inside a transaction: a FOR UPDATE lock is released at commit/rollback. If your framework runs in autocommit mode (one transaction per statement), the lock is released the moment it's taken — i.e. you locked nothing.
2. Keep the transaction short: no external API calls, no waiting on user input between BEGIN and COMMIT. A locked row blocks everyone who wants it; one slow transaction serializes the whole table.
3. The WHERE must hit an index: WHERE id = ? on the primary key locks one row; without an index Postgres degrades to locking the whole table (and risks table-level locks) — concurrency drops to zero and deadlocks get likely.

Prisma in practice (dialect: Prisma + PostgreSQL)

Prisma gives you two weapons for two scenarios. First, the simplest: pure counter decrements need no transaction at all — use the atomic operation:

// Dialect: Prisma Client. decrement is atomic — the database does the math, no race
await prisma.product.update({
  where: { id: "sku-123" },
  data: { stock: { decrement: 1 } },
});

"Check-then-decrement" needs read-modify-write, so it needs a transaction. Prisma's interactive transaction:

// Dialect: Prisma. Note: interactive transactions need session semantics on the connection.
// Under PgBouncer's transaction-pooling mode each query may land on a different connection
// and the transaction breaks — use a direct connection or PgBouncer session mode
// (Supabase offers a session-mode pooler).
const order = await prisma.$transaction(async (tx) => {
  // Pessimistic lock: Prisma has no native FOR UPDATE API, so pass SQL through $queryRaw
  const rows = await tx.$queryRaw<{ stock: number }[]>`
    SELECT stock FROM "Product" WHERE id = ${productId} FOR UPDATE`;
  if (rows[0].stock < qty) throw new Error("OUT_OF_STOCK");

  await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: qty } },
  });
  return tx.order.create({ data: { productId, qty } });
}, {
  maxWait: 5000,   // longest wait for a pooled connection
  timeout: 10000,  // whole-transaction timeout: stops slow transactions hogging locks
});

Optimistic locking in Prisma — note you need updateMany to get the matched-row count:

// Dialect: Prisma. update returns the entity but won't tell you whether the version
// in WHERE matched — updateMany returns { count }, and count === 0 means version conflict
async function decrementStockOptimistic(productId: string, qty: number, retries = 3) {
  for (let i = 0; i < retries; i++) {
    const p = await prisma.product.findUniqueOrThrow({ where: { id: productId } });
    if (p.stock < qty) throw new Error("OUT_OF_STOCK");
    const r = await prisma.product.updateMany({
      where: { id: productId, version: p.version },  // only updates on version match
      data: { stock: p.stock - qty, version: { increment: 1 } },
    });
    if (r.count === 1) return;           // success
    // count === 0: someone got there first — re-read and retry
  }
  throw new Error("STOCK_CONFLICT_RETRY_EXHAUSTED");
}

Drizzle in practice (dialect: Drizzle ORM + PostgreSQL)

Drizzle hugs SQL more closely — for("update") is a literal translation of FOR UPDATE:

// Dialect: Drizzle ORM. Throwing inside db.transaction rolls back
import { eq, sql } from "drizzle-orm";

await db.transaction(async (tx) => {
  // SELECT ... FOR UPDATE: exclusive row lock
  const [row] = await tx
    .select({ stock: products.stock })
    .from(products)
    .where(eq(products.id, productId))
    .for("update");
  if (!row || row.stock < qty) throw new Error("OUT_OF_STOCK");

  // Let the database subtract: atomic, no race
  await tx
    .update(products)
    .set({ stock: sql`${products.stock} - ${qty}` })
    .where(eq(products.id, productId));

  await tx.insert(orders).values({ productId, qty });
});

The optimistic version is equally direct:

// Dialect: Drizzle ORM
const [p] = await db.select().from(products).where(eq(products.id, productId));
if (p.stock < qty) throw new Error("OUT_OF_STOCK");
const updated = await db
  .update(products)
  .set({ stock: p.stock - qty, version: sql`${products.version} + 1` })
  .where(eq(products.id, productId).and(eq(products.version, p.version)));
// Drizzle's update doesn't return the matched-row count directly:
// use .returning({ id: products.id }) — an empty array means version conflict, re-read and retry

How to choose: three rules of thumb

• Read-heavy, rare conflicts (editing a profile, renaming a product in admin): optimistic locking. No lock overhead; a few lines of retry logic.
• Frequent write conflicts, but no hot row (normal checkout, coupon claiming): pessimistic FOR UPDATE + short transactions. Retry storms hurt more than queuing.
• One row written insanely often (flash-sale stock, global counters, leaderboards): neither lock is enough — lock contention itself becomes the bottleneck. See Section 5: queue serialization.

A question beginners always ask: "if I just crank isolation to Serializable, can I skip locks?" It works, but the price is runtime 40001 errors you must retry — you saved the lock code but now you write retry code, and you must handle "the retried operation already called an external API" (SMS sent, payment gateway hit — neither is replayable). Locks mean "queue up"; Serializable means "collide, roll back, try again". The latter is usually more expensive.

4. Idempotency keys: stopping "the same request executed twice"

Locks solve "two different requests writing at once"; idempotency keys solve "the same request executed twice": users double-clicking submit, browsers re-sending POSTs, payment-gateway webhook retries, your own failure retries. The "idempotent debit" section of the credits-ledger guide is this idea applied to billing; here is the general version.

The whole mechanism in one sentence: give every write a client-generated unique key, and let a database unique constraint guarantee it executes exactly once.

-- Dialect: PostgreSQL. key is the primary key = a natural unique constraint; a conflict means "already executed"
CREATE TABLE idempotency_keys (
  key        TEXT PRIMARY KEY,
  status     TEXT NOT NULL,            -- processing | succeeded | failed
  response   JSONB,                    -- snapshot of the success response: replayed directly on conflict
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

// Dialect: TypeScript + Postgres. INSERT first to claim the key; only the claimer executes,
// everyone else reads the archived response
async function idempotent<T>(key: string, fn: () => Promise<T>): Promise<T> {
  // 1. Try to claim: INSERT ... ON CONFLICT DO NOTHING is atomic
  const inserted = await db`
    INSERT INTO idempotency_keys (key, status) VALUES (${key}, 'processing')
    ON CONFLICT (key) DO NOTHING RETURNING key`;
  if (inserted.length === 0) {
    // 2. Key exists: wait for it to finish (poll or LISTEN/NOTIFY), then return the archived response
    const rec = await waitForDone(key);
    if (rec.status === "succeeded") return rec.response as T;
    throw new Error("IDEMPOTENT_RETRY_OF_FAILED");
  }
  try {
    // 3. Only the process that claimed the key runs the real business logic
    const result = await fn();
    await db`UPDATE idempotency_keys
             SET status = 'succeeded', response = ${JSON.stringify(result)}::jsonb
             WHERE key = ${key}`;
    return result;
  } catch (e) {
    await db`UPDATE idempotency_keys SET status = 'failed' WHERE key = ${key}`;
    throw e;
  }
}

// Caller: the key is generated client-side (uuid at click time) and sent with the request
await idempotent(`order:${clientRequestId}`, () => createOrder(...));

Practical details:

• The key must be generated client-side: a server-generated key can't protect against "the request never arrived, so the client retried". The frontend mints a uuid at click time and sends it in a header (e.g. Idempotency-Key — that's Stripe's design) or in the body.
• "Check-then-insert" has a race: don't write "SELECT to see if the key exists, INSERT if not" — two concurrent requests will both see "not there" and both execute. Use INSERT ... ON CONFLICT DO NOTHING and let the unique constraint arbitrate; the constraint conflict is itself atomic.
• Status must distinguish processing/succeeded/failed: when the second request arrives, the first may still be running. Seeing processing means wait (short polling or Postgres LISTEN/NOTIFY) — don't error out, and don't execute again.
• Keys need a TTL: the idempotency table grows forever otherwise. Set a retention window per business case (24h for orders, 7 days for payments); expired keys may be reused or archived away.
• Idempotency keys and locks are orthogonal: keys stop replays, locks stop concurrent writes. In a checkout endpoint, the idempotency key wraps the outside (stops duplicate submits) and the transaction + row lock wraps the inside (stops oversell). That's what the "stackable" note at the bottom of Figure 2 means.

5. Hot rows: when the lock itself becomes the bottleneck

Ten seconds into a flash sale, 100,000 people rush 500 units of stock. Even with FOR UPDATE, every request queues on the same row: lock waits exhaust the connection pool, then timeouts, retries, more timeouts — the database gets trampled by the queue itself. This is the hot row problem, and the fix isn't "a better lock", it's "don't write concurrently" — serialize concurrent writes through a queue.

The architecture is simple: write requests enter a queue first (Redis Stream / BullMQ / a cloud queue service), and a single consumer deducts them one by one in order. With only one writer touching the row at a time, the race disappears at the root.

// Dialect: TypeScript + BullMQ (Redis queue). Producer: just enqueue, return "queued" immediately
import { Queue } from "bullmq";
const orderQueue = new Queue("seckill", { connection: redis });

// API layer: validate params → enqueue → return the queued status (don't touch the stock row here)
await orderQueue.add("deduct", { productId, userId, qty, idempotencyKey });

// Consumer: concurrency 1, sequential. Reuses the Section-3 transaction code inside
// (no contention now; the lock is just a safety net)
import { Worker } from "bullmq";
new Worker("seckill", async (job) => {
  await idempotent(job.data.idempotencyKey, () =>
    deductStockInTransaction(job.data)   // the Section 3 transaction code, reused as-is
  );
}, { connection: redis, concurrency: 1 });  // key point: concurrency 1 for the hot-key consumer

But a queue is no silver bullet — it moves the problem to three new places:

• The queue's own ordering guarantees: Redis Stream consumer groups shard across consumers; a hot key must be routed to one consumer (hash-by-key partitioning, or a single consumer as above). BullMQ defaults to competing consumers — control concurrency explicitly for hot keys.
• UX turns asynchronous: "checkout" no longer returns success synchronously; it's "queued, check back for the result". The frontend needs polling or WebSocket push — a product-level tradeoff.
• Simple counters don't need a queue: for "likes +1" with no check-then-act, UPDATE ... SET likes = likes + 1 is already atomic as a single statement, as is Redis INCRBY (single-threaded, atomic). Queues are for hot check-then-deduct flows.

6. Deadlocks, lock timeouts, and retries: the dark side of locks

Once you take locks, you inherit their three dark sides. AI-generated code almost never handles them — yet they cause half of all production incidents in this area.

Deadlocks: two transactions waiting on each other

Transaction A locks row 1 and wants row 2; transaction B locks row 2 and wants row 1. They wait forever. Postgres detects this and kills one of them (40P01 deadlock_detected); MySQL does the same. There is exactly one prevention: all transactions must acquire locks in the same order. When deducting multiple products, sort the product ids first, then FOR UPDATE row by row — with identical lock ordering, deadlocks can't form. This is a code-convention issue; put it on the team checklist.

Lock timeouts: don't let one slow query stall everyone

By default, a blocked transaction waits forever (while holding a pooled connection). Cap the wait:

-- Dialect: PostgreSQL. Session-level: error after 3s of lock waiting instead of waiting forever
SET lock_timeout = '3s';
-- Statement-level: roll back any transaction not committed within 10s (stops slow transactions hogging locks)
SET statement_timeout = '10s';

Prisma's $transaction({ timeout }) and Drizzle's transaction options express the same statement-level idea. The principle: lock waits must have an upper bound — a fast failure the user can retry beats a wedged service by orders of magnitude.

Retries: which errors deserve one

Not every error is retryable. The allowlist is short — memorize it:

• Retryable: 40001 (serialization failure, PG Serializable), 40P01 (deadlock — the killed one), 55P03 (lock timeout), optimistic-lock version conflicts (count=0). Back off with jitter before retrying (100ms, 200ms, 400ms… plus randomness, so retries don't collide again).
• Not retryable: unique-constraint violations (23505 — a data problem, identical on retry 100), business validation failures like OUT_OF_STOCK, and anything that already performed an external side effect (SMS sent, payment gateway called) — retrying those repeats the side effect; such operations belong inside an idempotency key (Section 4).

7. The solo-team rollout checklist

Theory's done; here's what you can change in your code tonight, in priority order:

1. First, reproduce every "check-then-act" endpoint under load: fire 50 concurrent requests at your checkout/debit endpoint and see whether stock goes negative or balances double-spend. Reproduce first, then fix. A race you never reproduced is a fix you can't verify.
2. Pure counters get atomic operations: Prisma's decrement/increment, SQL's SET x = x - 1, Redis's DECRBY. One line, zero races.
3. "Check-then-deduct" gets transaction + row lock: FOR UPDATE + short transaction + indexed WHERE. Prisma: $transaction + $queryRaw; Drizzle: .for("update").
4. Idempotency keys on every write endpoint: client-generated keys, INSERT ... ON CONFLICT DO NOTHING arbitration, processing/succeeded/failed statuses, TTL on keys.
5. Hot rows go through a queue: flash sales and global counters get serialized enqueueing, consumer concurrency pinned to 1 per key, frontend switched to async result polling.
6. Lock discipline: sort multi-row lock acquisition to prevent deadlocks; always set lock_timeout and a transaction timeout; retry only allowlisted errors, with jittered backoff.
7. Leave isolation levels at their defaults: Postgres Read Committed is enough; Repeatable Read for reconciliation-style reads; Serializable as a last resort.

Back to the opening: AI writes code fast, but it defaults to living in a vacuum where only one user exists at a time. Concurrency control is the line between "the demo runs" and "I'd dare sell on it" — stock, balances, orders: every mistake is real money. Next time AI hands you "SELECT then UPDATE" code, you'll know exactly what the missing lines should look like.