MySQL Long-Running Transaction and Lock Contention Sentinel

In MySQL applications utilizing the InnoDB storage engine, uncommitted long-running transactions pose severe operational risks. When an application initiates a transaction and performs updates without issuing a timely commit or rollback, InnoDB maintains row-level locks and retains undo log history. Downstream concurrent queries attempting to modify the same rows enter lock wait states, ultimately resulting in lock wait timeouts (Error 1205) and connection pool saturation.

This blueprint establishes an automated database reliability watchdog for MySQL. Running on a 5-minute schedule or on-demand, it queries information_schema.innodb_trx joined with information_schema.processlist to detect transactions open longer than your threshold. If offending sessions are found, it dispatches an actionable Slack notification containing the thread ID, client host, execution duration, and remediation commands.

How it works

1. Scheduled Health Check: The scheduled_health_check trigger (io.kestra.plugin.core.trigger.Schedule) executes every 5 minutes, or runs on-demand via the Kestra UI.
2. Transaction Inspection: The audit_running_transactions task (io.kestra.plugin.jdbc.mysql.Query) queries information_schema.innodb_trx and information_schema.processlist to compute elapsed seconds and row lock counts.
3. Violation Evaluation: The evaluate_transaction_violations flowable task (io.kestra.plugin.core.flow.If) branches based on whether any active transaction exceeds max_transaction_age_seconds.
4. Slack Alert Dispatch: If violations exist, notify_dba_slack (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) broadcasts an alert card containing the thread ID, query snippet, and suggested KILL command.
5. Healthy Confirmation: When all transactions are within threshold, log_healthy_status records nominal database status in the execution log.
6. Audit Manifest: The export_audit_manifest task records execution metrics for historical tracking and reliability dashboards.

What you get

• Early detection of abandoned or runaway database transactions before connection pools exhaust.
• Visibility into row lock counts and blocking thread IDs.
• Direct Slack notifications with pre-formatted remediation commands.
• Zero human overhead for continuous MySQL transactional health monitoring.

Who it is for

• Database Administrators (DBAs) and Site Reliability Engineers (SREs) managing MySQL production clusters.
• Backend engineers debugging transaction timeouts and locking bottlenecks.
• Data platform engineers maintaining transactional relational storage.

Why orchestrate this with Kestra

Monitoring database lock contention typically requires third-party agent installations or manual command-line inspection during incidents. Kestra provides declarative, serverless orchestration: it connects directly via JDBC, schedules regular health checks, branches conditionally based on violation thresholds, securely handles credentials, and integrates seamlessly with Slack.

Inputs

Name	Type	Default	Description
mysql_url	STRING	jdbc:mysql://localhost:3306/production	JDBC connection string targeting the MySQL database instance.
max_transaction_age_seconds	INT	60	Threshold in seconds beyond which an open uncommitted transaction triggers an alert.
slack_channel	STRING	#dba-alerts	Slack channel destination for database performance alerts.

Expected outputs

• {{ outputs.audit_running_transactions.rows }}: Array of active transaction records containing trx_id, thread_id, db_user, client_host, duration_seconds, trx_rows_locked, and query_snippet.
• {{ outputs.audit_running_transactions.size }}: Total number of transactions exceeding the runtime threshold.
• {{ outputs.evaluate_transaction_violations }}: Result of conditional branch evaluation.
• {{ outputs.export_audit_manifest.value }}: Structured JSON manifest with audit timestamp, violation count, and operational status.

Prerequisites

• A MySQL instance (version 5.7+ or 8.0+) with the InnoDB storage engine.
• A database user with PROCESS privilege to query information_schema.processlist and information_schema.innodb_trx.
• A Slack Incoming Webhook URL targeting your monitoring channel.

Secrets

• MYSQL_USERNAME: Database username with read permissions on information_schema.
• MYSQL_PASSWORD: Password for the MySQL user.
• SLACK_WEBHOOK_URL: Slack Incoming Webhook endpoint URL used for sending alert notifications.

Quick start

1. Configure MYSQL_USERNAME, MYSQL_PASSWORD, and SLACK_WEBHOOK_URL in your Kestra namespace secrets.
2. Import this flow YAML into your Kestra instance.
3. Click Execute in the UI to run an initial audit against your MySQL instance.
4. Review execution outputs to inspect active transaction durations and connection statistics.

Common pitfalls and troubleshooting

• PROCESS Privilege Requirement: In MySQL 8.0, querying the complete processlist requires the PROCESS global privilege. Without this, non-root users will only see their own connection threads.
• Timezone Drift: The query calculates transaction duration using NOW(). Ensure your MySQL server timezone matches the system clock to avoid negative or inflated duration values.
• Transient Background Threads: InnoDB internal threads or event schedulers may appear in processlist; the join with innodb_trx guarantees only user transactions are evaluated.

How to extend

• Add io.kestra.plugin.email.MailSend inside the then: block to dispatch incident summaries to on-call engineers.
• Integrate with PagerDuty using io.kestra.plugin.pagerduty.notifications.PagerDutyAlert for tier-1 production databases.
• Automate termination of idle transactions older than 300 seconds by adding a secondary io.kestra.plugin.jdbc.mysql.Query task executing KILL <thread_id>.

Links

• Kestra MySQL Plugin Documentation: https://kestra.io/plugins/plugin-jdbc-mysql
• Kestra Slack Plugin Documentation: https://kestra.io/plugins/plugin-notifications
• MySQL InnoDB Transaction Table Documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-trx-table.html