Deadlock

Detect, analyze, and prevent database deadlocks with automated monitoring, alerting, and resolution strategies for production database systems.

When to Use This Command

Use /deadlock when you need to:

• Investigate recurring deadlock issues in production
• Implement proactive deadlock detection and alerting
• Analyze transaction patterns causing deadlocks
• Optimize lock acquisition order in applications
• Monitor database lock contention in real-time
• Generate deadlock reports for performance tuning

DON'T use this when:

• Database doesn't support deadlock detection (use lock monitoring instead)
• Dealing with application-level race conditions (not database deadlocks)
• Looking for slow queries (use query analyzer instead)
• Investigating connection pool exhaustion (use connection pooler)

Design Decisions

This command implements comprehensive deadlock detection and prevention because:

• Proactive monitoring prevents production incidents
• Automated analysis identifies root causes faster
• Prevention strategies reduce deadlock frequency by 90%+
• Real-time alerting enables rapid incident response
• Historical analysis reveals patterns and trends

Alternative considered: Reactive deadlock handling

• Only responds after deadlocks occur
• Relies on application retry logic
• No visibility into deadlock patterns
• Recommended only for low-traffic systems

Alternative considered: Database-native logging only

• Limited to log file analysis
• No automated alerting or resolution
• Requires manual correlation of events
• Recommended only for development environments

Prerequisites

Before running this command:

1. Database user with monitoring permissions (e.g., pg_monitor role)
2. Access to database logs or system views
3. Understanding of your application's transaction patterns
4. Monitoring infrastructure (Prometheus/Grafana recommended)
5. Python 3.8+ or Node.js 16+ for monitoring scripts

Implementation Process

Step 1: Configure Database Deadlock Logging

Enable comprehensive deadlock detection and logging in your database.

Step 2: Implement Deadlock Monitoring

Set up automated monitoring to detect and alert on deadlocks in real-time.

Step 3: Analyze Deadlock Patterns

Build analysis tools to identify common deadlock scenarios and root causes.

Step 4: Implement Prevention Strategies

Apply code changes and database tuning to prevent deadlocks proactively.

Step 5: Set Up Continuous Monitoring

Deploy dashboards and alerting for ongoing deadlock visibility.

Output Format

The command generates:

• monitoring/deadlock-detector.py - Real-time deadlock monitoring script
• analysis/deadlock-analyzer.sql - SQL queries for pattern analysis
• config/deadlock-prevention.md - Prevention strategies documentation
• dashboards/deadlock-dashboard.json - Grafana dashboard configuration
• alerts/deadlock-rules.yml - Prometheus alerting rules

Code Examples

Example 1: PostgreSQL Deadlock Detection and Monitoring

-- Enable comprehensive deadlock logging
-- Add to postgresql.conf
log_lock_waits = on
deadlock_timeout = '1s'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

-- Create deadlock monitoring view
CREATE OR REPLACE VIEW deadlock_monitor AS
SELECT
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    l.pid AS blocked_pid,
    l.page,
    l.tuple,
    a.usename,
    a.application_name,
…

# monitoring/deadlock-detector.py
import psycopg2
import time
import logging
import json
from datetime import datetime, timedelta
from typing import List, Dict, Optional
from dataclasses import dataclass, asdict
from collections import defaultdict

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@dataclass
class DeadlockEvent:
    """Represents a detected deadlock event."""
    detected_at: datetime
    blocked_pid: int
…

Example 2: MySQL Deadlock Detection and InnoDB Monitoring

-- Enable InnoDB deadlock logging
-- Add to my.cnf
[mysqld]
innodb_print_all_deadlocks = 1
innodb_deadlock_detect = ON
innodb_lock_wait_timeout = 50

-- Create deadlock monitoring table
CREATE TABLE deadlock_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    victim_thread_id BIGINT,
    victim_query TEXT,
    waiting_query TEXT,
    lock_mode VARCHAR(50),
    table_name VARCHAR(255),
    index_name VARCHAR(255),
    deadlock_info TEXT,
…

// monitoring/mysql-deadlock-detector.js
const mysql = require('mysql2/promise');
const fs = require('fs').promises;

class MySQLDeadlockDetector {
    constructor(config) {
        this.config = config;
        this.pool = mysql.createPool({
            host: config.host,
            user: config.user,
            password: config.password,
            database: config.database,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
        this.checkInterval = config.checkInterval || 10000;
        this.deadlockStats = {
…