-- Add version column for optimistic locking
ALTER TABLE orders ADD COLUMN version INT DEFAULT 1;

-- Read current state
SELECT order_id, status, total, version FROM orders WHERE order_id = 42;

-- Update with version check (optimistic lock)
UPDATE orders
SET
  status = 'shipped',
  shipped_at = NOW(),
  version = version + 1  -- increment version
WHERE order_id = 42
  AND version = 3  -- must match what we read!
RETURNING *;

-- If 0 rows updated: concurrent modification detected!
-- Application should retry or show conflict error