Concurrency, Locking, and Idempotency

Financial and inventory data has correctness requirements that ordinary web applications don’t. This page is the set of patterns that keep it correct under concurrent load.

The three problems

Lost update — two users edit the same record; one overwrites the other
Over-commitment — two orders confirm the same stock
Double posting — a retried message posts the same invoice twice

Each has a distinct solution. Confusing them produces systems that are slow and incorrect.

Optimistic vs pessimistic locking

Optimistic (default for user editing)

UPDATE sales_order
   SET quantity = 500, version = version + 1
 WHERE id = 4711 AND version = 7;
-- 0 rows affected → someone else changed it → show a conflict message

Cheap, no held locks, works across stateless requests. Best for records edited rarely and concurrently seldom. The cost is a user occasionally losing work — which is why the conflict message must show what changed, not just “please retry”.

Pessimistic (for genuine contention)

SELECT * FROM material_valuation
 WHERE material_id = 100 AND plant_id = 1
   FOR UPDATE;

Necessary where the read-modify-write must be atomic: balances, moving-average price, number ranges, stock. Hold it for the shortest possible time, and never across a user interaction.

Application-level locks (the ERP pattern)

For long-running user edits, ERPs hold a lock in an application lock table, not in the database:

CREATE TABLE app_lock (
  lock_key    VARCHAR(200) PRIMARY KEY,   -- 'SALES_ORDER:4711'
  owner       VARCHAR(100) NOT NULL,      -- session/user
  acquired_at TIMESTAMPTZ NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL
);

User opens order 4711  → INSERT app_lock (fails if held → "locked by Karim")
User edits for 8 min   → no DB lock held
User saves             → short DB transaction: validate, write, commit
                       → DELETE app_lock

Requirements: TTL expiry (users close laptops), an admin unlock with logging, lock granularity that matches how people actually work (locking a whole customer master when two people edit different sales areas is too coarse).

Number range assignment

The gapless requirement forces serialisation. See Number Ranges and Keys.

BEGIN;
  SELECT current_number FROM number_range
   WHERE object='AR_INVOICE' AND company_id=1000 AND fiscal_year=2026
   FOR UPDATE;                                  -- serialisation point
  UPDATE number_range SET current_number = current_number + 1 WHERE ...;
  INSERT INTO ar_invoice (document_number, ...) VALUES (:new_number, ...);
COMMIT;

Assign the number as late as possible. If you take the lock at the start of building a 500-line invoice, every other invoice in the company queues behind you for the whole build.

MAX(number)+1 is not an alternative. It races, deadlocks, and degrades with table size.

Stock and ATP concurrency

Stock 100.
Order A checks: 100 available → confirms 80
Order B checks: 100 available → confirms 80    ← checked before A committed
Both save → 160 confirmed, 100 exist.

Four legitimate strategies:

Strategy	Correctness	Throughput	Where
Lock the material/plant for check+confirm	Perfect	Bottleneck on hot items	High-value, low-volume
Optimistic: re-check at save, fail if over	Perfect	Good	General purpose
Reservation: check creates a short-lived hold	Perfect	Good	E-commerce
Accept over-commitment, resolve in backorder	Eventually	Best	High-volume consumer goods

This is a business decision, not a technical one. Ask: is disappointing a customer occasionally worse than slowing order entry for everyone? Document the answer.

Balance updates

Every posting to the same account/period updates the same balance row → contention.

Pattern 1: delta rows (recommended)

-- Instead of UPDATE balance SET amount = amount + 500
INSERT INTO balance_delta (account_id, period, amount) VALUES (100, 202603, 500);

-- Read: SELECT SUM(amount) FROM balance_delta WHERE ...
-- Compaction job periodically rolls deltas into a summary row

Converts an UPDATE hotspot into an INSERT. Scales dramatically better.

Pattern 2: sharded counters

INSERT INTO balance_shard (account_id, period, shard, amount)
VALUES (100, 202603, floor(random()*16), 500)
ON CONFLICT (account_id, period, shard) DO UPDATE SET amount = balance_shard.amount + 500;

16 shards → 16× less contention. Sum across shards on read.

Pattern 3: asynchronous aggregation

Post the journal; a background process updates balances. Fast writes, eventually-consistent reads. Acceptable for reporting, not acceptable for a real-time availability or budget check.

Deadlocks

Transaction A: locks customer 100, then order 4711
Transaction B: locks order 4711, then customer 100
→ deadlock

Prevention:

Always acquire locks in a consistent order — sort by table then by primary key. This one rule eliminates most deadlocks.
Keep transactions short.
Take the most contended lock last.
Retry on deadlock with jittered backoff — deadlocks are expected, not exceptional.
Log every deadlock with both statements; the pattern reveals the design flaw.

Idempotency

The most important pattern for ERP integration. Networks time out. The caller doesn’t know if the posting succeeded. It retries. Without idempotency you post twice — and a duplicated invoice or payment is a real financial event.

CREATE TABLE idempotency_key (
  key            VARCHAR(200) PRIMARY KEY,
  request_hash   VARCHAR(64) NOT NULL,
  response       JSONB,
  status,        -- IN_PROGRESS | COMPLETED | FAILED
  created_at, expires_at
);

POST /gl/documents
Idempotency-Key: goods-receipt-4500001-line-10-v1

1. INSERT idempotency_key (key, hash, 'IN_PROGRESS')
     ON CONFLICT DO NOTHING
2. If 0 rows inserted:
     - existing status COMPLETED → return the stored response (200, not a new posting)
     - existing status IN_PROGRESS → return 409, tell the caller to retry later
     - request_hash differs → return 422: same key, different payload
3. Otherwise: perform the work, store the response, mark COMPLETED

Choosing the key

Good:  source system + source document + line + operation
       "WMS|SHIP-88213|10|GOODS_ISSUE"
       "BANK|STMT-2026-03-14|LINE-42|POST"
Bad:   a random UUID generated per attempt  ← a retry generates a new key
Bad:   a timestamp

The key must be derived from the business event, so a retry produces the same key. This is the part teams get wrong: a client that generates a fresh UUID on each retry has idempotency machinery that does nothing.

Natural idempotency

Better still, use a unique constraint on the business key:

ALTER TABLE ap_invoice
  ADD CONSTRAINT uq_vendor_invoice UNIQUE (company_id, vendor_id, invoice_number);

The database refuses the duplicate. Cheap, permanent, and it also catches duplicates entered by humans.

Exactly-once processing

True exactly-once delivery doesn’t exist across a network. What you can build is at-least-once delivery plus idempotent processing, which is operationally equivalent.

Producer:  transactional outbox      (never lose an event, never publish a phantom)
Broker:    at-least-once delivery
Consumer:  idempotent handler        (safe to process the same message twice)
Result:    effectively exactly-once

Read consistency

Level	Meaning	ERP use
Read committed	See committed data	Default; fine for most
Repeatable read	Same data throughout the transaction	Reports needing internal consistency
Serializable	As if transactions ran one at a time	Critical financial calculations

A trial balance that reads accounts one at a time under read-committed can see account A before a posting and account B after it — and won’t balance. Use a consistent snapshot for anything that must foot.

BEGIN ISOLATION LEVEL REPEATABLE READ;
  -- all reads see the same snapshot
COMMIT;

Long-running transactions

❌ BEGIN; process 5,000,000 records; COMMIT;

Blocks cleanup, holds locks for hours, makes rollback catastrophic, and one failure loses everything.

✅ for each chunk of 5,000:
     BEGIN; process chunk; update checkpoint; COMMIT;

See Jobs, Queues and Batch.

Race conditions to watch for

Race	Fix
Two users create the same customer	Unique constraint on tax ID / normalised name
Two orders confirm the same stock	Choose an ATP strategy (above)
Two payments against one invoice	Lock the open item during clearing
Two approvals of the same request	Unique constraint on (instance, step, approver)
Two batch jobs running concurrently	Advisory lock or a unique run-registry constraint
Budget check passes for two POs that together exceed	Serialise the check+write on the budget key
Two goods receipts against the same PO line beyond tolerance	Lock the PO line

Testing concurrency

Concurrency bugs don’t appear in single-user testing. Ever.

- Load test with realistic concurrency, not one user
- Deliberately run two clients against the same record
- Introduce artificial delays between read and write to widen the race window
- Chaos test: kill a process mid-transaction
- Replay the same message 3× and assert the effect is identical
- Run the batch job twice and diff the results

“It worked in testing” means nothing for concurrency. The bugs surface at month-end under load, in production, at 2am.