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 (
pgstatactivity, 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
- Query the active transaction view to establish a baseline. For PostgreSQL:
SELECT pid, state, querystart, now() - querystart AS duration, query FROM pgstatactivity 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'.
- 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.
- Detect idle-in-transaction sessions that hold locks without executing queries. For PostgreSQL:
SELECT pid, state, querystart, now() - statechange AS idleduration FROM pgstatactivity WHERE state = 'idle in transaction' AND now() - statechange > interval '5 minutes'.
- Monitor lock contention by querying the lock manager. For PostgreSQL:
SELECT blockedlocks.pid AS blockedpid, blockinglocks.pid AS blockingpid, blockedactivity.query AS blockedquery FROM pgcatalog.pglocks blockedlocks JOIN pgcatalog.pglocks blockinglocks ON blockinglocks.locktype = blockedlocks.locktype. For MySQL: SELECT * FROM informationschema.INNODBLOCK_WAITS.
- Track transaction throughput by sampling
pgstatdatabase (xactcommit, xactrollback) or MySQL Comcommit / Comrollback status variables at regular intervals. Calculate commits/second and rollback ratio.
- Create monitoring scripts that run on a cron schedule (every 30-60 seconds) to capture transaction metrics and write to a time-series store or log file.
- Configure alerting thresholds: transactions exceeding 60 seconds, idle-in-transaction sessions exceeding 5 minutes, lock wait queues exceeding 10 waiters, and rollback ratio exceeding 5%.
