Transactions

Monitor database transaction performance, detect long-running transactions, identify lock contention, track rollback rates, and automatically alert on transaction anomalies for production database health.

When to Use This Command

Use /txn-monitor when you need to:

• Detect and kill long-running transactions blocking other queries
• Monitor lock wait times and identify deadlock patterns
• Track transaction rollback rates for error analysis
• Alert on isolation level anomalies (phantom reads, dirty reads)
• Analyze transaction throughput and latency trends
• Investigate application connection leak issues

DON'T use this when:

• Database has minimal transaction load (<100 TPS)
• All transactions complete within milliseconds
• Looking for query optimization (use query optimizer instead)
• Investigating data corruption (use audit logger instead)

Design Decisions

This command implements real-time transaction monitoring with automated alerting because:

• Long-running transactions (>30s) block other queries and cause performance degradation
• Lock contention detection prevents cascade failures
• Rollback rate monitoring identifies application bugs early
• Automatic alerts reduce MTTR (Mean Time To Resolution)
• Historical trend analysis enables capacity planning

Alternative considered: Periodic manual checks

• No automated alerting on issues
• Relies on humans checking dashboards
• Slower incident response
• Recommended only for development environments

Alternative considered: Database log parsing

• Post-mortem analysis only
• No real-time alerts
• Requires custom log parsing logic
• Recommended for compliance/audit purposes

Prerequisites

Before running this command:

1. Database monitoring permissions (pg_monitor role or PROCESS privilege)
2. Access to pg_stat_activity (PostgreSQL) or performance_schema (MySQL)
3. Alerting infrastructure (Slack, PagerDuty, email)
4. Monitoring data retention strategy (metrics database or time-series DB)
5. Runbook for common transaction issues

Implementation Process

Step 1: Enable Transaction Monitoring

Configure database to track transaction statistics.

Step 2: Build Real-Time Monitor

Create monitoring script that polls transaction statistics every 5-10 seconds.

Step 3: Define Alert Thresholds

Set thresholds for long-running transactions, lock waits, and rollback rates.

Step 4: Implement Automated Actions

Auto-kill transactions exceeding thresholds or alert operators.

Step 5: Create Dashboards

Build Grafana dashboards for transaction metrics visualization.

Output Format

The command generates:

• monitoring/transaction_monitor.py - Real-time transaction monitoring daemon
• queries/transaction_analysis.sql - Transaction health diagnostic queries
• alerts/transaction_alerts.yml - Prometheus alerting rules
• dashboards/transaction_dashboard.json - Grafana dashboard configuration
• docs/transaction_runbook.md - Incident response procedures

Code Examples

Example 1: PostgreSQL Real-Time Transaction Monitor

# monitoring/postgres_transaction_monitor.py
import psycopg2
from psycopg2.extras import Dict Cursor
import time
import logging
from typing import List, Dict, Optional
from dataclasses import dataclass, asdict
from datetime import datetime, timedelta

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

@dataclass
class TransactionInfo:
    """Represents an active transaction."""
    pid: int
    username: str
    database: str
…

Example 2: Transaction Analysis Queries

-- PostgreSQL transaction health diagnostic queries

-- 1. Long-running transactions
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    NOW() - xact_start AS transaction_duration,
    NOW() - query_start AS query_duration,
    state,
    LEFT(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY xact_start;
…

Error Handling

Configuration Options

Monitoring Intervals

• check_interval: 5-10 seconds for real-time alerting
• long_transaction_threshold: 30-60 seconds (production), 300s (analytics)
• idle_in_transaction_timeout: 600 seconds (10 minutes)

Auto-Kill Thresholds

• Long-running OLTP: 60-300 seconds
• Long-running analytics: 3600 seconds (1 hour)
• Idle in transaction: 600 seconds (10 minutes)

Alert Thresholds

• Rollback rate: >5% warning, >10% critical
• Blocked transactions: >10 warning, >50 critical
• Active connections: >80% of max_connections

Best Practices

DO:

• Set statement_timeout in application connection strings
• Use connection pooling to limit total connections