Database Deadlock Detector

Overview

Detect, analyze, and prevent database deadlocks in PostgreSQL, MySQL, and MongoDB by examining lock wait graphs, parsing deadlock log entries, identifying the application code paths that cause lock ordering conflicts, and implementing preventive patterns.

Prerequisites

• Database credentials with access to lock monitoring views (pg_locks, INNODB_LOCK_WAITS)
• psql or mysql CLI for executing diagnostic queries
• PostgreSQL: log_lock_waits = on and deadlock_timeout = 1s configured
• MySQL: innodb_print_all_deadlocks = ON for deadlock logging to error log
• Access to database error logs for deadlock event parsing
• Application source code access for identifying lock-inducing code paths

Instructions

1. Check for currently blocked transactions and their blockers:
   • PostgreSQL: SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid JOIN pg_locks bl2 ON bl2.locktype = bl.locktype AND bl2.relation = bl.relation AND bl2.pid != bl.pid JOIN pg_stat_activity blocking ON blocking.pid = bl2.pid WHERE NOT bl.granted
   • MySQL: SELECT * FROM information_schema.INNODB_LOCK_WAITS