deadlock

name	deadlock
description	Detect and resolve database deadlocks with automated monitoring
shortcut	dead

Database Deadlock Detector

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,
    a.client_addr,
    a.query AS blocked_query,
    a.state,
    a.wait_event_type,
    a.wait_event,
    NOW() - a.query_start AS query_duration,
    NOW() - a.state_change AS state_duration
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

-- Query to identify blocking vs blocked processes
CREATE OR REPLACE FUNCTION show_deadlock_chains()
RETURNS TABLE (
    blocked_pid integer,
    blocked_query text,
    blocking_pid integer,
    blocking_query text,
    duration interval
) AS $$
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query  blocking_query,
    NOW()  blocked.query_start  duration
 pg_stat_activity blocked
 pg_locks blocked_locks  blocked.pid  blocked_locks.pid
 pg_locks blocking_locks 
    blocked_locks.locktype  blocking_locks.locktype
     blocked_locks.relation     blocking_locks.relation
     blocked_locks.page     blocking_locks.page
     blocked_locks.tuple     blocking_locks.tuple
     blocked_locks.pid  blocking_locks.pid
 pg_stat_activity blocking  blocking_locks.pid  blocking.pid
  blocked_locks.granted
   blocking_locks.granted
   blocked.pid  blocking.pid;
$$  ;


 deadlock_history (
    id SERIAL ,
    detected_at   NOW(),
    victim_pid ,
    victim_query TEXT,
    blocker_pid ,
    blocker_query TEXT,
    lock_type TEXT,
    table_name TEXT,
    resolution_time_ms ,
    metadata JSONB
);


  REPLACE  log_deadlock_event()
   $$

     deadlock_history (
        victim_pid, victim_query, blocker_pid, blocker_query,
        lock_type, table_name, metadata
    )
    
        blocked_pid,
        blocked_query,
        blocking_pid,
        blocking_query,
        ,
        ,
        jsonb_build_object(
            , ,
            , NOW()
        )
     show_deadlock_chains()
    LIMIT ;
     ;
;
$$  plpgsql;

# 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
    blocked_query: str
    blocking_pid: int
    blocking_query: str
    lock_type: str
    table_name: Optional[str]
    duration_seconds: float

    def to_dict(self) -> dict:
        return {
            **asdict(self),
            'detected_at': self.detected_at.isoformat()
        }

class PostgreSQLDeadlockDetector:
    """Real-time PostgreSQL deadlock detection and alerting."""

    def __init__(
        self,
        connection_string: str,
        check_interval: int = 5,
        alert_threshold:  = ,
        alert_webhook: [] = 
    ):
        .connection_string = connection_string
        .check_interval = check_interval
        .alert_threshold = alert_threshold
        .alert_webhook = alert_webhook
        .deadlock_count = defaultdict()
        .last_alert_time = {}

     () -> psycopg2.extensions.connection:
        
         psycopg2.connect(.connection_string)

     () -> [DeadlockEvent]:
        
        query = 

        conn = .connect()
        :
             conn.cursor()  cur:
                cur.execute(query)
                rows = cur.fetchall()

                events = []
                 row  rows:
                    event = DeadlockEvent(
                        detected_at=datetime.now(),
                        blocked_pid=row[],
                        blocked_query=row[][:],  
                        blocking_pid=row[],
                        blocking_query=row[][:],
                        lock_type=row[],
                        table_name=row[],
                        duration_seconds=(row[])
                    )
                    events.append(event)

                 events
        :
            conn.close()

     () -> [, ]:
        
          events:
             {}

        
        tables = defaultdict()
        lock_types = defaultdict()
        query_patterns = defaultdict()

         event  events:
             event.table_name:
                tables[event.table_name] += 
            lock_types[event.lock_type] += 

            
            query_type = event.blocked_query.strip().split()[].upper()
            query_patterns[query_type] += 

         {
            : (events),
            : (tables.items(), key= x: x[])[]  tables  ,
            : (lock_types.items(), key= x: x[])[]  lock_types  ,
            : (query_patterns),
            : (e.duration_seconds  e  events) / (events),
            : (e.duration_seconds  e  events)
        }

     () -> []:
        
        suggestions = []

         analysis.get():
            table = analysis[]
            suggestions.append(
                
                
            )

         analysis.get(, {}).get(, ) > :
            suggestions.append(
                
                
            )

         analysis.get(, ) > :
            suggestions.append(
                
                
                
            )

        lock_type = analysis.get()
         lock_type == :
            suggestions.append(
                
                
            )

         suggestions

     ():
        
         (events) >= .alert_threshold:
            logger.warning(
                
                
            )

            
             .alert_webhook:
                 requests
                payload = {
                    : ,
                    : [e.to_dict()  e  events],
                    : analysis,
                    : .suggest_prevention_strategy(analysis)
                }
                :
                    requests.post(.alert_webhook, json=payload, timeout=)
                 Exception  e:
                    logger.error()

     ():
        
        logger.info()

         :
            :
                events = .detect_deadlocks()

                 events:
                    logger.info()
                    analysis = .analyze_deadlock_pattern(events)

                    
                     event  events:
                        logger.warning(
                            
                            
                        )

                    
                    suggestions = .suggest_prevention_strategy(analysis)
                     suggestions:
                        logger.info()
                         suggestion  suggestions:
                            logger.info()

                    .alert_on_deadlock(events, analysis)

                time.sleep(.check_interval)

             KeyboardInterrupt:
                logger.info()
                
             Exception  e:
                logger.error()
                time.sleep(.check_interval)


 __name__ == :
    detector = PostgreSQLDeadlockDetector(
        connection_string=,
        check_interval=,
        alert_threshold=,
        alert_webhook=
    )

    detector.run_continuous_monitoring()

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,
    INDEX idx_detected_at (detected_at)
) ENGINE=InnoDB;

-- View current locks and blocking sessions
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    l.lock_mode,
    l.lock_type,
    l.lock_table,
    l.lock_index,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_time_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
JOIN information_schema.innodb_locks l ON w.requesting_lock_id  l.lock_id
  wait_time_seconds ;



    table_name,
    ()  deadlock_count,
    (detected_at)  last_deadlock,
    (TIMESTAMPDIFF(, detected_at, NOW()))  avg_age_seconds
 deadlock_log
 detected_at  DATE_SUB(NOW(),   )
  table_name
  deadlock_count ;

// 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 = {
            total: 0,
            byTable: {},
            byHour: {}
        };
    }

    async detectCurrentLockWaits() {
        const query = `
            SELECT
                r.trx_id AS waiting_trx_id,
                r.trx_mysql_thread_id AS waiting_thread,
                r.trx_query AS waiting_query,
                b.trx_id AS blocking_trx_id,
                b.trx_mysql_thread_id AS blocking_thread,
                b.trx_query AS blocking_query,
                l.lock_mode,
                l.lock_type,
                l.lock_table,
                l.lock_index,
                TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_time_seconds
            FROM information_schema.innodb_lock_waits w
            JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
            JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
            JOIN information_schema.innodb_locks l ON w.requesting_lock_id = l.lock_id
            WHERE TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) > 5
            ORDER BY wait_time_seconds DESC
        `;

         [rows] =  ..(query);
         rows;
    }

     () {
         [rows] =  ..();
         status = rows[].;

        
         deadlockRegex = ;
         match = status.(deadlockRegex);

         (match) {
             deadlockInfo = match[];
             timestamp =  ();

            
             transactions = .(deadlockInfo);

             {
                timestamp,
                deadlockInfo,
                transactions
            };
        }

         ;
    }

    () {
        
         tableRegex = ;
         tables = [];
         match;

         ((match = tableRegex.(deadlockInfo)) !== ) {
            tables.();
        }

        
         lockRegex = ;
         lockModes = [];

         ((match = lockRegex.(deadlockInfo)) !== ) {
            lockModes.(match[]);
        }

         {
            : [... (tables)],
            : [... (lockModes)]
        };
    }

     () {
         query = ;

         tables = deadlockEvent...();
         lockModes = deadlockEvent...();

         ..(query, [
            ,
            ,
            ,
            lockModes,
            tables,
            deadlockEvent.
        ]);

        ..++;

        
        deadlockEvent...( {
            ..[table] =
                (..[table] || ) + ;
        });
    }

    () {
         advice = [];

        
         tableFrequency = {};
        lockWaits.( {
             table = wait.;
            tableFrequency[table] = (tableFrequency[table] || ) + ;
        });

        
         sortedTables = .(tableFrequency)
            .( b[] - a[]);

         (sortedTables. > ) {
             [mostProblematicTable, count] = sortedTables[];

            advice.({
                : ,
                : mostProblematicTable,
                :  +
                     +
                    
            });
        }

        
         longRunning = lockWaits.( w. > );
         (longRunning. > ) {
            advice.({
                : ,
                :  +
                     +
                    
            });
        }

         advice;
    }

     () {
        .();

        ( () => {
             {
                
                 lockWaits =  .();

                 (lockWaits. > ) {
                    .();
                    lockWaits.( {
                        .(
                             +
                            
                        );
                    });

                     advice = .(lockWaits);
                     (advice. > ) {
                        .();
                        advice.( {
                            .();
                        });
                    }
                }

                
                 deadlock =  .();
                 (deadlock) {
                    .();
                    .();
                    .();

                     .(deadlock);
                }

            }  (error) {
                .(, error);
            }
        }, .);
    }

     () {
         query = ;

         [rows] =  ..(query);
         {
            : rows,
            : .
        };
    }
}


 detector =  ({
    : ,
    : ,
    : ,
    : ,
    : 
});

detector.();


( () => {
     stats =  detector.();
     fs.(
        ,
        .(stats, , )
    );
}, );

Error Handling

Error	Cause	Solution
"Permission denied"	Insufficient database privileges	Grant pg_monitor role (PostgreSQL) or PROCESS privilege (MySQL)
"Connection timeout"	Network or authentication issues	Verify connection string and firewall rules
"No deadlocks detected"	Deadlocks resolved before detection	Reduce deadlock_timeout to 500ms for faster detection
"Table not found"	Missing monitoring tables	Run setup scripts to create required tables
"Log file not accessible"	Filesystem permissions	Ensure logging user has write access to log directory

Configuration Options

Deadlock Detection

• deadlock_timeout: Time to wait before logging lock waits (PostgreSQL: 1s default)
• innodb_deadlock_detect: Enable/disable InnoDB deadlock detection (MySQL)
• innodb_print_all_deadlocks: Log all deadlocks to error log (MySQL)
• log_lock_waits: Log queries waiting for locks (PostgreSQL)

Monitoring Parameters

• check_interval: Frequency of deadlock checks (5-10 seconds recommended)
• alert_threshold: Number of deadlocks before alerting (3-5 recommended)
• retention_period: How long to keep deadlock history (7-30 days)

Best Practices

DO:

• Always acquire locks in consistent order across transactions
• Keep transactions as short as possible
• Use row-level locking instead of table-level when possible
• Implement retry logic with exponential backoff
• Monitor deadlock trends over time
• Set appropriate lock timeouts (innodb_lock_wait_timeout = 50s)

DON'T:

• Hold locks during expensive operations (network calls, file I/O)
• Mix DDL and DML in the same transaction
• Use SELECT ... FOR UPDATE without ORDER BY
• Ignore deadlock patterns (they indicate design issues)
• Set deadlock_timeout too high (delays detection)

Performance Considerations

• Monitoring queries add minimal overhead (<0.1% CPU typically)
• Use connection pooling to reduce monitoring overhead
• Index deadlock_history table on detected_at for fast queries
• Archive old deadlock logs to separate table monthly
• Consider read replicas for monitoring queries in high-traffic systems

Related Commands

• /sql-query-optimizer - Optimize queries to reduce lock duration
• /database-index-advisor - Add indexes to minimize table scans
• /database-transaction-monitor - Monitor transaction patterns
• /database-connection-pooler - Optimize connection management
• /database-health-monitor - Overall database health monitoring

Version History

• v1.0.0 (2024-10): Initial implementation with PostgreSQL and MySQL support
• Planned v1.1.0: Add Microsoft SQL Server and Oracle deadlock detection