monitoring-database-transactions

name	monitoring-database-transactions
description	Monitor use when you need to work with monitoring and observability. This skill provides health monitoring and alerting with comprehensive guidance and automation. Trigger with phrases like "monitor system health", "set up alerts", or "track metrics".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)
version	1.27.0
author	Jeremy Longshore <[e-mail]>
license	MIT
tags	["database","monitoring","observability"]
compatibility	Designed for Claude Code, also compatible with Codex and OpenClaw

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.

3. Detect idle-in-transaction sessions that hold locks without executing queries. For PostgreSQL: SELECT pid, state, query_start, now() - state_change AS idle_duration FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - state_change > interval '5 minutes'.

4. Monitor lock contention by querying the lock manager. For PostgreSQL: SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype. For MySQL: SELECT * FROM information_schema.INNODB_LOCK_WAITS.

5. Track transaction throughput by sampling pg_stat_database (xact_commit, xact_rollback) or MySQL Com_commit / Com_rollback status variables at regular intervals. Calculate commits/second and rollback ratio.

6. 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.