Start by clarifying scope and goals: detect root cause of deadlocks that appear only at production scale, gather reproducible diagnostics, add monitoring to catch long lock waits, and deploy application mitigations safely with validation.Diagnostics (runtime SQL to capture locks / waits)- Periodically sample lock state and active transactions:sql
-- PostgreSQL: show blocking/waiting
SELECT pid, now() - pg_stat_activity.query_start AS age, query, state,
wait_event_type, wait_event
FROM pg_stat_activity
WHERE state <> 'idle' AND (wait_event IS NOT NULL OR now() - query_start > interval '1s');
-- current locks
SELECT t.relkind, l.locktype, l.mode, l.GRANTED, a.query, a.pid
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
LEFT JOIN pg_class t ON l.relation = t.oid;
For MySQL/InnoDB inspect INFORMATION_SCHEMA.INNODB_LOCKS and INNODB_LOCK_WAITS and SHOW ENGINE INNODB STATUS for deadlock traces.Instrumentation- Emit metrics: lock_wait_duration (histogram), lock_acquired_count, deadlock_count, blocked_transactions.- Inject tracing: annotate spans where transactions acquire locks and include table/key identifiers.- Create alert: rate(deadlock_count[5m]) > threshold or p95(lock_wait_duration) > X ms.Application-level mitigations- Enforce consistent lock ordering across code paths (document ordering by resource id or table priority).- Reduce transaction scope: move non-essential work outside transactions; use SELECT ... FOR UPDATE only when necessary.- Use optimistic concurrency where possible (version/timestamp).- Implement retry with exponential backoff and jitter for transactions that fail with deadlock:python
def txn_with_retry(func, retries=5):
backoff = 0.05
for i in range(retries):
try:
return func()
except DeadlockError:
time.sleep(backoff + random.uniform(0, backoff))
backoff *= 2
raise
Validation strategy (safe production rollout)1. Canary: enable instrumentation and mitigation for a small % of traffic (feature flag).2. Observe metrics (deadlock_count, latency, error rate) and tracing to confirm reduction of deadlocks and no regression.3. Run synthetic load tests against a production-like dataset in a staging cluster using captured query patterns to reproduce scale.4. Gradual ramp: increase canary percentage while monitoring.5. Post-mortem: retain full deadlock traces/logs for 48–72 hours; if mitigation introduces other issues (increased latency or contention), roll back.Why this works- Combining diagnostics (DB lock tables + engine deadlock traces) with telemetry gives visibility at scale.- App-level changes (ordering, smaller txns, retries) reduce the chance of circular waits and make failures transient and recoverable.- Canary + metrics ensures safe validation without global risk.