detecting-database-deadlocks

name	detecting-database-deadlocks
description	Process use when you need to work with deadlock detection. This skill provides deadlock detection and resolution with comprehensive guidance and automation. Trigger with phrases like "detect deadlocks", "resolve deadlocks", or "prevent deadlocks".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)
version	1.27.0
author	Jeremy Longshore <[e-mail]>
license	MIT
tags	["database","detecting-database"]
compatibility	Designed for Claude Code

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

2. Parse recent deadlock events from database logs:

   • PostgreSQL: Search logs for ERROR: deadlock detected entries, which include the two conflicting queries and the lock types
   • MySQL: Run SHOW ENGINE INNODB STATUS\G and examine the LATEST DETECTED DEADLOCK section
   • Extract: transaction IDs, queries involved, tables and rows locked, and which transaction was rolled back

3. Construct the lock wait graph from the deadlock log. Map which transaction held which lock and which lock each transaction was waiting for. The circular dependency reveals the deadlock cycle. Identify the specific rows or index ranges involved.

4. Trace the deadlocking queries back to application code. Use Grep to find the SQL statements in the codebase and identify the transaction boundaries (BEGIN/COMMIT blocks or ORM transaction decorators). Map the full sequence of operations within each transaction.

5. Identify the root cause pattern: