Backend Architecture Series — Jocoso.cl eCommerce · #03

Ecommerce Stock: SELECT FOR UPDATE and Serializable Transactions with Prisma

How to prevent race conditions and maintain complete traceability with StockMovement

The Problem: Race Conditions in Stock

Imagine two users buying the last size simultaneously. With a naive SELECT + UPDATE implementation, both transactions read stock = 1, validate OK, and both decrement — stock ends up at -1. This scenario is not theoretical: on any Latin American ecommerce's Black Friday, real concurrency makes it inevitable.

■ Technical Decision

Use $transaction with isolationLevel: 'Serializable' + SELECT FOR UPDATE via $queryRaw. Prisma ORM does not expose SELECT FOR UPDATE natively — raw queries inside the managed transaction are required.

The Golden Rule: Stock Only Through Movements

The ProductVariant.stock field is never modified directly from the application. Every stock change — sale, restock, manual adjustment, return — goes through a StockMovement. This guarantees complete audit: the historical stock can always be reconstructed by summing the movements.

StockMovement Model

prisma 
model StockMovement {
  id            String        @id @default(uuid())
  variantId     String
  quantity      Int           // positive = entry, negative = exit
  source        StockSource   // ORDER | MANUAL | RETURN | ML_SALE
  referenceType ReferenceType // ORDER | PAYMENT | MANUAL_ADJUSTMENT
  referenceId   String
  externalId    String        @unique // idempotency (ML webhooks)
  userId        String?       // who executed the movement
  createdAt     DateTime      @default(now())
}

The Real Code: SELECT FOR UPDATE

typescript 
// infrastructure/stock/stock.prisma-repo.ts
async decreaseWithLock(
  variantId: string,
  amount: number,
  movement: StockMovement,
): Promise<void> {
  await this.prisma.$transaction(
    async (tx) => {
      // 1. Acquire exclusive row lock
      const rows = await tx.$queryRaw<VariantStockRow[]>`
        SELECT stock FROM product_variants
        WHERE id = ${variantId} FOR UPDATE
      `;
      if (!rows.length) throw new NotFoundException('Variant not found');
      const current = rows[0].stock;

      // 2. Validate in domain
      if (current < amount) {
        throw new BadRequestException('Insufficient stock');
      }

      // 3. Decrement and record movement atomically
      await tx.$executeRaw`
        UPDATE product_variants
        SET stock = stock - ${amount}, updated_at = NOW()
        WHERE id = ${variantId}
      `;
      await tx.stockMovement.create({ data: movement.toPersistence() });
    },
    { isolationLevel: 'Serializable' },
  );
}

Why Prisma ORM Is Not Enough

Prisma exposes $transaction() for atomic operations, but has no high-level API for SELECT FOR UPDATE. The solution is $queryRaw inside the transaction: the Prisma client manages the connection and isolation level, while the SQL query guarantees row-level locking. This decision was explicitly documented so future developers understand why raw queries exist in a project using ORM.

Idempotency with externalId

MercadoLibre (and any external webhook) can send the same notification multiple times. The externalId @unique field in StockMovement guarantees that a double webhook does not decrement stock twice:

typescript 
// If the movement already exists, Prisma throws Unique Constraint Violation
// The use case catches it and returns 200 OK (idempotent)
try {
  await this.stockRepo.decreaseWithLock(variantId, qty, movement);
} catch (e) {
  if (isUniqueConstraintViolation(e)) return; // already processed
  throw e;
}

Pure Domain: Stock Entity and StockDomainService

Validation logic lives in the domain, not infrastructure. The Stock entity and its domain service encapsulate business rules:

  • stock never negative — enforced in domain AND in DB constraint
  • canDecrease(amount): throws DomainException if stock < amount
  • StockDomainService.validateDecrease() coordinates entity + business rules

■ Trade-offs

Serializable isolation vs Read Committed. Serializable prevents all concurrency phenomena (dirty reads, non-repeatable reads, phantom reads) but has higher overhead from conflict management. For stock operations correctness trumps throughput: an ecommerce prefers rejecting a sale over selling nonexistent stock.

Traceability with source + referenceType

Each StockMovement records who, why, and from where. When integrating with MercadoLibre, ML sales are created with source: ML_SALE and referenceType: ORDER, distinguishing them from direct web channel sales. This enables audits, accounting reconciliation, and channel-by-channel analysis.