Concurrent Write Correctness: Choosing Between Conditional Updates, Pessimistic/Optimistic Locks, and Distributed Locks

Idempotency vs. Concurrency Safety: Two Distinct Problems

Multiple requests modifying shared data can cause overselling or state overwrites even when each request executes once. The article distinguishes two concerns:

Idempotency : Ensures the same business operation (e.g., a retry with the same operation ID) produces only one effect. Example: inventory 10 → 9 after first deduction; retry must return the original result (still 9), not deduct to 8.

Concurrency safety : Ensures independent operations interleaving on shared data still respect business rules. Example: two orders each buy 1 item when stock=1; both read 1, both compute 0, both write 0 → oversell. Idempotency cannot reject either request because both are legitimate; concurrency control is needed.

A single interface often needs both. The article presents a decision table mapping request scenarios to required guarantees.

When Is Concurrency Control Required?

First identify shared data — data accessed by multiple requests/tasks. Three sharing scopes:

Cross-user : same SKU inventory, activity quota — accessed by different users/orders.

User/account scope : account balance, daily quota — accessed by same user from multiple devices or callbacks.

Single business object : one order, one refund — accessed by request, callback, background job.

Then check how one operation affects another . If an operation modifies shared data, another operation's prior judgment may become stale, or its saved result may be overwritten. Examples:

Stock=1, A and B both judge "enough". A deducts; B's judgment expires. Rule: total allocated ≤ initial stock.

Cancel request reads "pending", payment callback updates to "paid". Cancel writes "cancelled" based on stale read → overwrites paid state. Fix: verify state at write time or lock from read through write in same transaction.

Control scope follows the impact: protect the specific shared data (inventory, order) whose modification breaks the rule. Existing mechanisms (conditional UPDATE, unique constraints, proper locking) may already suffice; merely wrapping code in a transaction does not guarantee valid judgments under concurrency.

Four Concurrency Solutions

1. Atomic & Conditional Updates

Core : Push computation and condition into a single UPDATE. stock = stock - quantity computes inside DB; stock >= quantity enforces rule in same statement. InnoDB row lock serializes competing UPDATEs — B waits for A's commit, then re-checks condition against new value.

Applicable when : condition and modification fit in one SQL (fixed-quantity deduction, balance deduction, simple state transition).

Implementation (inventory keyed by (tenant_id, sku_id)):

UPDATE inventory
SET stock = stock - :quantity,
    version = version + 1
WHERE tenant_id = :tenant_id
  AND sku_id = :sku_id
  AND stock >= :quantity;

Rows affected = 1 → proceed to save associated records in same transaction; 0 → rollback (insufficient stock or not found). Version increment supports other version-checking entry points. Conditional update does not detect duplicate requests — idempotency (unique operation record in same transaction) still required.

2. Database Pessimistic Lock

Core : Lock → Judge → Update in one transaction. SELECT ... FOR UPDATE locks parent record (order), then locks child records (order items). All reads/calculations use locked data; competing transactions wait.

Applicable when : need to read multiple related rows, compute locally, save together, all within a short transaction. Example: modify order items and recalc total. Two requests modifying different items on same order — both must lock order header, then re-read items and recompute total so second request includes first's committed change.

Implementation (order keyed by (tenant_id, order_id)):

-- Lock order header
SELECT order_state, total_amount, version
FROM orders
WHERE tenant_id = :tenant_id AND order_id = :order_id
FOR UPDATE;

-- Confirm pending, then lock items
SELECT item_id, unit_price, quantity
FROM order_items
WHERE tenant_id = :tenant_id AND order_id = :order_id
ORDER BY item_id
FOR UPDATE;

Apply quantity change, recompute total from full current item set , update items, update order total + version, commit. Critical : must recalc from locked rows, not from stale page data. All modification entry points must follow the same lock-order convention. Handle deadlock/lock-wait timeout by rolling back entire transaction and retrying with backoff. Lock acquisition does not replace idempotency for side-effects (e.g., operation logs).

3. Version Number & Optimistic Lock

Core : Read version, include version = :expected_version in UPDATE. Compare-and-increment happens atomically in one statement. If version mismatched (0 rows affected), reject — caller must re-read and re-confirm, not just bump version.

Applicable when : modification depends on previously read data and business requires detecting any intervening change. Ideal for user-facing edits (config, address) where read-save interval is long (minutes) and holding DB transaction lock is impractical.

Example : Two agents edit same pending order address. Both read version 7. A saves "Address A" → version 8. B saves "Address B" with version 7 → 0 rows. B re-reads, sees Address A, decides whether to overwrite.

Implementation (order address update):

UPDATE orders
SET shipping_address = :shipping_address,
    version = version + 1
WHERE tenant_id = :tenant_id
  AND order_id = :order_id
  AND order_state = 'PENDING_PAYMENT'
  AND version = :expected_version;

State condition (pending) and version condition address separate business rules. Version conflict ≠ previous request success (could be other operation); check operation identity + result record. All writers must maintain version.

4. Distributed Lock

Core : Multiple instances coordinate via shared lock service (e.g., Redis). Acquire lock with unique ownerToken, TTL (lease). Only holder proceeds; others wait or exit. Lock covers check → remote call → save result ; DB transactions are short and separate.

Applicable when : cross-instance coordination beyond DB scope, especially with remote calls unsuitable for long DB transactions. Example: payment consumer and compensation job both push same order to third-party WMS. In-memory locks don't work across instances.

Implementation (Redis SET lock NX PX 30000 with key order-push:t-1:wms-1:9001:<ownerToken>). Steps:

Acquire lock (new ownerToken each attempt).

Query local push record; if success, return saved third-party ID.

First push: save idempotency key + request payload (unique constraint).

Retry: query third-party by idempotency key.

Call third-party with same idempotency key + payload.

On success: conditional UPDATE local record to SUCCESS (protects against late failure overwriting success).

Timeout/uncertain: leave pending, do not generate new identity.

Finally: atomic compare-and-delete ownerToken to release.

Lease expiry does not stop old process — third-party must support idempotency; local conditional update protects confirmed state. DB conditional updates/locks still suffice for intra-DB coordination; don't add distributed lock just because service is multi-instance.

Combining Idempotency & Concurrency Control

Third-party push example uses four mechanisms together:

Distributed lock : coordinates which instance processes this push (valid lease + lock service guarantee).

Local unique push record : fixes (tenant_id, target_system, order_id, action=CREATE) as operation identity; saves request payload; unique constraint prevents duplicate local records.

Third-party idempotency key : e.g., push-t1-wms1-9001-create — same key + same params across retries/overlaps → third-party creates only one outbound order.

Local conditional update : only transition from non-SUCCESS to SUCCESS; never downgrade SUCCESS.

Recovery scenarios covered in table: simultaneous triggers (lock contention), already recorded success (return saved), third-party success but local save failed (query by idempotency key), timeout (keep pending, don't change key), lease expiry + overlap (third-party dedup + local conditional update), explicit rejection (record, decide retry per contract).

Key distinction : lock's ownerToken changes per attempt; idempotency key stays constant for the same business operation. Local transaction cannot roll back third-party action; if third-party lacks idempotency/unique constraint, distributed lock alone cannot guarantee exactly-once.

Selection Guide for Real Projects

First pick protection for shared data based on decision table:

Only check current value/state at write time → Conditional UPDATE .

Read-compute-save in short transaction → Pessimistic lock .

Save-time detection of stale read, reject outdated modification → Optimistic lock (version) .

Cross-instance coordination with remote calls → Distributed lock + handle lease expiry.

Patterns compose: state + version in same UPDATE; distributed lock protected write may still need version check. Multi-instance deployment does not invalidate DB locks/conditional updates — if same DB already enforces rules, no distributed lock needed. Multi-row total limits or ordered-application requirements need custom design.

Then assess idempotency: if same operation arriving again could re-deduct/re-create, define operation identity and return original result. Locks/version conflicts don't prove prior success — idempotency must be designed separately. Third-party push combines distributed lock (coordination), fixed idempotency key (retry identity), local conditional update (state protection), and third-party result query (confirmation).