Building Concurrency-Safe Inventory with Optimistic Locking

Inventory systems look simple until two users update the same SKU at the same time. Without a concurrency strategy, you get lost updates, negative stock counts, and angry stakeholders.

The Problem

A naive read-modify-write flow fails under parallel requests. User A reads quantity 10, User B reads quantity 10, both subtract 3, both write 7 — but the correct answer is 4.

Optimistic Locking Approach

• Added a version column to every inventory row.
• Every update includes WHERE version = :expectedVersion in the transaction.
• On conflict (0 rows updated), the API returns 409 and the client refetches fresh state.
• Auto-saving tables debounce writes and surface conflict toasts instead of silently overwriting.

This pattern trades a bit of retry logic for zero lock contention at the database level — a good fit for web apps with bursty, user-driven edits rather than high-frequency trading.

What I'd Do Differently

I'd add integration tests that fire parallel PATCH requests from day one. Concurrency bugs are invisible in manual QA until production traffic proves you wrong.