write_heavy_transactional vulnerable to lock

write heavy transactionalVulnerable Tolock contention

Source: Workload·Target: Failure Mode

Summary

Write-heavy transactional workloads amplify lock contention: many concurrent writers contend for row-level locks on the same records (e.g., shared account balances, inventory counts), causing transactions to queue, latency to spike, and throughput to plateau well below hardware limits.

Evidence

·High-write OLTP on shared rows (e-commerce inventory, account balance) saturates row-lock queues at >1000 concurrent writers
·PostgreSQL pg_locks view shows active lock waits: typical high-write systems show 10-100 active lock waits under peak load
·Lock wait timeout (lock_timeout=1s in PostgreSQL) causes transaction failures under contention, further degrading throughput
·Uber's MySQL migration was driven in part by InnoDB lock contention at high write volume
·SELECT FOR UPDATE with SKIP LOCKED is the standard pattern for queue-like workloads to avoid lock starvation

Operational Context

·Monitor pg_stat_activity for transactions in "lock wait" state: sustained high count indicates contention bottleneck
·{'Reduce lock scope': 'use SELECT FOR UPDATE SKIP LOCKED for queue processing; avoid full table locks in migrations'}
·Partition hot tables (accounts, inventory) to distribute lock contention across shards

Tradeoffs

·Optimistic locking (check-and-compare) reduces lock duration but increases retry rate under high contention
·Saga pattern eliminates distributed locks but introduces compensating transactions
·Short transactions reduce lock hold time but increase commit overhead at high throughput

Evidence grounding

Grounded, 5 supporting items

Lock contention under write-heavy OLTP is one of the most documented database scaling failure modes. Extensively covered in PostgreSQL, MySQL, and Oracle production guides and post-mortems.