Database Transaction Monitor

Overview

Monitor active database transactions in real time to detect long-running queries, lock contention, uncommitted transactions, and transaction throughput anomalies across PostgreSQL, MySQL, and MongoDB.

Prerequisites

• Database credentials with access to system catalogs (pg_stat_activity, information_schema.PROCESSLIST, or MongoDB currentOp)
• psql, mysql, or mongosh CLI installed
• Permissions to view other sessions' transactions (PostgreSQL: pg_monitor role; MySQL: PROCESS privilege)
• Baseline metrics for normal transaction duration and throughput
• Alerting infrastructure (email, Slack webhook, or PagerDuty) for notifications

Instructions

1. Query the active transaction view to establish a baseline. For PostgreSQL: SELECT pid, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC. For MySQL: SELECT id, user, host, db, command, time, state, info FROM information_schema.PROCESSLIST WHERE command != 'Sleep'.

2. Identify long-running transactions by filtering for duration exceeding the application's expected transaction time. Set initial thresholds at 30 seconds for OLTP workloads or 5 minutes for batch/reporting workloads.