Manage long-running transactions for AWS DMS performance

This article explains how long-running transactions impact AWS DMS change data capture (CDC) source latency and provides monitoring solutions to detect and resolve these issues before they stall replication pipelines.

• Long-running transactions force DMS to buffer all subsequent changes, causing CDC source latency to climb from seconds to hours
• Monitoring scripts for Oracle, PostgreSQL, MySQL, and SQL Server detect transactions exceeding configurable thresholds (default 15 minutes)
• Scripts use TLS/SSL encryption, publish alerts to Amazon SNS, and filter false positives from system processes
• Ready-to-use bash scripts available on GitHub with interactive prompts, prerequisite validation, and cron scheduling support
• Demonstration shows Oracle script detecting long-running sessions and alerting operations teams with session details including SID, username, duration, and wait events
• Security best practices include no command-line credentials, IAM-based alerting, and AWS Secrets Manager integration for automated scheduling

Proactive monitoring of long-running transactions enables teams to avoid unnecessary replication instance scaling, reduce cutover downtime, and keep database migrations on schedule.