ADR-0002. Pessimistic SELECT FOR UPDATE on order during item mutations

Field	Value
Status	Accepted
Date	2026-02-08
Deciders	sale-team
Supersedes	—

Context

• Two devices (POS-A and POS-B) frequently add the same item to the same DRAFT order simultaneously.
• The natural merge logic — "if the same (orderId, itemType, itemId) exists, increment quantity" — has a race window: both transactions see no existing row, both insert, and we end up with duplicate items.
• This was observed in production with a customer ordering "burger × 2" on tablet A and "burger × 1" on tablet B at the same time — result was two separate burger lines instead of one with qty=3.

Decision

In SaleOrderService.addSaleOrderItem, the service opens a transaction and issues SELECT * FROM "SaleOrder" WHERE id = $1 FOR UPDATE before reading items and inserting/merging. The order row acts as a coordination lock for ALL item-level mutations on that order.

The same pattern applies to:

• Bulk item updates (SaleOrderItemService.update)
• Clear items (SaleOrderService.clearOrderItems)
• Checkout (CheckoutService.checkout)
• Merge / split

Consequences

Pros	Cons
Race-free merge: duplicate items deterministically combine	Lock holds until transaction commit — slow operations block others
Single mechanism for all order-row contention	Read-only operations don't share the lock (only writes serialize)
Compatible with PostgreSQL concurrency model	Lock waits visible in DB metrics — false-positive alerts possible
No application-level mutex needed	Long-running transactions cause cascade waits

Alternatives Considered

Option	Pros	Cons	Why rejected
Optimistic locking (version column + retry)	DB-portable, no waits	Retry storms when many devices add the same item; complex client logic	Real-world hot SKUs make retries costly
Application-level Redis lock	Cross-DB	Stale lock risk; SPOF; sync overhead	Worse than native DB primitive
Unique index on (orderId, itemType, itemId) + ON CONFLICT INCREMENT	DB-only	Doesn't work with CUSTOM items (unique line per add); can't capture line-level discounts	Doesn't fit mode=CUSTOM semantics

References

• sale/src/services/sale.service.ts (lock pattern in addSaleOrderItem)
• sale/src/services/sale-order-item.service.ts (bulk update lock)
• core/src/models/schemas/sale/sale-item/schema.ts (mode field)