MySQL Orphaned XA Transaction Rollback Guard with Kestra

id: mysql-orphaned-xa-transaction-rollback-guard
namespace: company.team

inputs:
  - id: mysql_url
    type: STRING
    defaults: jdbc:mysql://localhost:3306/
    description: JDBC URL of the MySQL server to guard. The connecting user needs
      XA_RECOVER_ADMIN to read and roll back prepared branches.

  - id: use_secret_auth
    type: BOOL
    defaults: true
    description: Leave true in production so the connection reads MYSQL_USERNAME and
      MYSQL_PASSWORD from secrets. Set false only to pass credentials inline in
      a disposable test environment.

  - id: mysql_username
    type: STRING
    defaults: root
    description: Username used only when use_secret_auth is false. Ignored in production.

  - id: mysql_password
    type: STRING
    defaults: ""
    description: Password used only when use_secret_auth is false. Ignored in production.

  - id: min_age_minutes
    type: INT
    defaults: 15
    description: Age threshold in minutes. A prepared branch is targeted only when
      the guard has seen it prepared for at least this long. A real in-flight
      two-phase commit completes in seconds, so this keeps the guard off a
      legitimate branch.

  - id: cursor_key
    type: STRING
    defaults: xa_orphan_first_seen
    description: KV key that stores the first time each prepared branch was seen.
      Use a distinct key per guarded server so two servers do not share one
      cursor.

  - id: dry_run
    type: BOOL
    defaults: true
    description: When true, report the orphaned branches without rolling anything back.

  - id: auto_remediate
    type: BOOL
    defaults: false
    description: When true and dry_run is false, roll back each targeted branch
      behind an approval gate, then re-read XA RECOVER.

triggers:
  - id: schedule
    type: io.kestra.plugin.core.trigger.Schedule
    disabled: true
    cron: "*/10 * * * *"
    description: Poll for orphaned prepared branches every ten minutes. Disabled by
      default and pinned to safe mode so it reports without rolling back until
      an operator enables it.
    inputs:
      dry_run: true
      auto_remediate: false

tasks:
  - id: detect
    type: io.kestra.plugin.jdbc.mysql.Query
    description: List every prepared but not completed XA branch. CONVERT XID
      returns gtrid and bqual as hex so a binary xid rebuilds safely for the
      rollback.
    url: "{{ inputs.mysql_url }}"
    username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
      inputs.mysql_username }}"
    password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
      inputs.mysql_password }}"
    fetchType: FETCH
    sql: XA RECOVER CONVERT XID

  - id: compute
    type: io.kestra.plugin.core.output.OutputValues
    description: Record the first time each prepared branch was seen in a KV cursor
      and target only the branches seen at least min_age_minutes ago. The
      rollback statement for each target is rebuilt from the hex gtrid and
      bqual.
    values:
      summary: >-
        {{ {'rows': (outputs.detect.rows ?? []), 'cursor':
        (kv(inputs.cursor_key, errorOnMissing=false) ?? {}), 'minage':
        (inputs.min_age_minutes * 60)} | toJson | jq('. as $in | ($in.rows //
        []) as $rows | ($in.cursor | if type=="string" then (if .=="" then {}
        else fromjson end) else . end) as $prior | $in.minage as $minage |
        (now|floor) as $t | ($rows | map({(.data): ($prior[.data] // $t)}) | add
        // {}) as $nc | ($rows | map((.data|ltrimstr("0x")) as $hex |
        (.gtrid_length*2) as $gl | (.bqual_length*2) as $bl | {xid:.data,
        formatID:.formatID, gtrid_hex:$hex[0:$gl],
        bqual_hex:$hex[$gl:($gl+$bl)], first_seen:$nc[.data], age_seconds:($t -
        $nc[.data]), rollback_sql:("XA ROLLBACK 0x" + $hex[0:$gl] + (if $bl>0
        then ", 0x" + $hex[$gl:($gl+$bl)] else ", \u0027\u0027" end) + ", " +
        (.formatID|tostring))})) as $all | {prepared_count:($rows|length),
        target_count:($all|map(select(.age_seconds>=$minage))|length),
        new_cursor:$nc, targets:($all|map(select(.age_seconds>=$minage)))}') |
        first | toJson }}

  - id: save_cursor
    type: io.kestra.plugin.core.kv.Set
    description: Persist the first-seen map. It carries no ttl because an expired KV
      key throws on read. It is pruned to the branches still prepared so it
      cannot grow without bound.
    key: "{{ inputs.cursor_key }}"
    value: "{{ fromJson(outputs.compute.values.summary).new_cursor | toJson }}"

  - id: log_status
    type: io.kestra.plugin.core.log.Log
    message: |
      MySQL XA guard on {{ inputs.mysql_url }}: {{ fromJson(outputs.compute.values.summary).prepared_count }} prepared branch(es), {{ fromJson(outputs.compute.values.summary).target_count }} older than {{ inputs.min_age_minutes }} minute(s) and targeted.
      Mode dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}.
      Targets: {{ fromJson(outputs.compute.values.summary).targets | toJson }}

  - id: have_targets
    type: io.kestra.plugin.core.flow.If
    condition: "{{ fromJson(outputs.compute.values.summary).target_count > 0 }}"
    then:
      - id: alert_orphans
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Alert that orphaned prepared branches exist, in dry run and in
          remediate mode, so a human sees the stalled locks even when nothing is
          rolled back.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {"text": "MySQL XA guard: {{ fromJson(outputs.compute.values.summary).target_count }} orphaned prepared XA branch(es) on {{ inputs.mysql_url }} older than {{ inputs.min_age_minutes }} minute(s). dry_run={{ inputs.dry_run }} auto_remediate={{ inputs.auto_remediate }}. Execution {{ execution.id }}."}

      - id: mode
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: Hold the run until a human approves the rollback. XA ROLLBACK never
              runs on its own.
            pauseDuration: PT1H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                required: false
                description: Set true to roll back the targeted branches. Anything else leaves
                  them prepared.
          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: remediate_rollback
                type: io.kestra.plugin.core.flow.Loop
                description: Roll back each targeted branch one at a time. concurrencyLimit 1
                  because XA ROLLBACK changes shared state and parallel
                  rollbacks would race.
                concurrencyLimit: 1
                values: "{{ fromJson(outputs.compute.values.summary).targets | toJson }}"
                tasks:
                  - id: rollback_one
                    type: io.kestra.plugin.jdbc.mysql.Query
                    description: Roll back one orphaned prepared branch. allowFailure so one
                      rejected rollback does not stop the pass and the re-read
                      stays the real gate.
                    allowFailure: true
                    url: "{{ inputs.mysql_url }}"
                    username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
                      inputs.mysql_username }}"
                    password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
                      inputs.mysql_password }}"
                    fetchType: NONE
                    sql: "{{ fromJson(item.value).rollback_sql }}"
              - id: reverify
                type: io.kestra.plugin.jdbc.mysql.Query
                description: Re-read XA RECOVER after the rollbacks. This read is the proof, not
                  the XA ROLLBACK calls.
                url: "{{ inputs.mysql_url }}"
                username: "{{ inputs.use_secret_auth ? secret('MYSQL_USERNAME') :
                  inputs.mysql_username }}"
                password: "{{ inputs.use_secret_auth ? secret('MYSQL_PASSWORD') :
                  inputs.mysql_password }}"
                fetchType: FETCH
                sql: XA RECOVER CONVERT XID
              - id: verify_values
                type: io.kestra.plugin.core.output.OutputValues
                values:
                  still_targeted: >-
                    {{ {'targets':
                    fromJson(outputs.compute.values.summary).targets,
                    'remaining': (outputs.reverify.rows ?? [])} | toJson |
                    jq('(.targets | map(.xid)) as $t | (.remaining | map(.data))
                    as $r | [ $t[] | select(. as $x | $r | index($x)) ] |
                    length') | first }}
                  remaining_count: >-
                    {{ (outputs.reverify.rows ?? []) | jq('length') | first }}
              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.verify_values.values.still_targeted == '0' }}"
                then:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: "Rollback confirmed on {{ inputs.mysql_url }}. Before: {{
                      fromJson(outputs.compute.values.summary).target_count }}
                      targeted branch(es) prepared. After: 0 of them still
                      prepared, {{ outputs.verify_values.values.remaining_count
                      }} prepared branch(es) remain in total (younger than the
                      threshold or newly prepared)."
                else:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      Rollback did not clear every targeted branch on {{
                      inputs.mysql_url }}. {{
                      outputs.verify_values.values.still_targeted }} targeted
                      branch(es) are still listed by XA RECOVER. A branch may
                      have been committed by its coordinator between the read
                      and the rollback. The connecting user may also lack
                      XA_RECOVER_ADMIN. Check the run.
            else:
              - id: not_approved
                type: io.kestra.plugin.core.log.Log
                message: "Approval not granted, so no XA ROLLBACK ran. {{
                  fromJson(outputs.compute.values.summary).target_count }}
                  orphaned branch(es) on {{ inputs.mysql_url }} are still
                  prepared."
        else:
          - id: report_only
            type: io.kestra.plugin.core.log.Log
            message: "Report only (dry_run={{ inputs.dry_run }}, auto_remediate={{
              inputs.auto_remediate }}). {{
              fromJson(outputs.compute.values.summary).target_count }} orphaned
              branch(es) on {{ inputs.mysql_url }} would be rolled back. No
              change made."
    else:
      - id: clean_log
        type: io.kestra.plugin.core.log.Log
        message: "No action. {{ fromJson(outputs.compute.values.summary).prepared_count
          }} prepared branch(es) on {{ inputs.mysql_url }}, none older than {{
          inputs.min_age_minutes }} minute(s). A plain run records first-seen
          and leaves recent branches alone."

errors:
  - id: alert_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Post to Slack on any failure, including a connection error or a
      rollback that did not clear a targeted branch.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "MySQL XA guard FAILED for {{ inputs.mysql_url }} (execution {{ execution.id }}). A connection error or a rollback that did not clear a targeted branch. Check the run."}

A distributed transaction that reaches the prepared state then loses its coordinator is left in limbo. It holds row and metadata locks, blocks history-list purge, stalls writers that touch the same rows. Nothing clears it on its own. XA RECOVER lists these prepared but not completed branches. This guard reads them, waits out a safety age so a branch from a live two-phase commit is never touched, rolls back only the orphaned ones behind a gate, then proves they are gone.

This blueprint was created by zkasuran.

Safety

Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and records what it saw. It never rolls anything back.
A rollback happens only when auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.
The age gate is the core safety. A branch is targeted only after the guard has seen it prepared for at least min_age_minutes. A real two-phase commit prepares and commits in well under a second, so a legitimate in-flight branch is never old enough to be touched.
XA ROLLBACK discards the branch. If the coordinator chose commit, other participants keep it, so check the transaction manager first.
The run passes only when a second read of XA RECOVER shows every targeted branch gone.

How it works

detect (io.kestra.plugin.jdbc.mysql.Query) runs XA RECOVER CONVERT XID. CONVERT XID returns the gtrid and bqual as a hex string so a binary xid rebuilds safely.
compute (io.kestra.plugin.core.output.OutputValues) reads a KV cursor that holds the first time each branch was seen, keeps the entry for a branch still prepared, adds a timestamp for a new one, then targets only the branches first seen at least min_age_minutes ago. It rebuilds the XA ROLLBACK statement for each target from the hex gtrid, the hex bqual and the formatID.
save_cursor (io.kestra.plugin.core.kv.Set) writes the pruned cursor back. It carries no ttl because an expired KV key throws on read. Pruning to the branches still prepared keeps it bounded.
log_status (io.kestra.plugin.core.log.Log) records the prepared count, the targeted count and the mode.
have_targets (io.kestra.plugin.core.flow.If) ends with a clean log when nothing is old enough. Otherwise alert_orphans posts to Slack in every mode.
mode (io.kestra.plugin.core.flow.If) enters remediation only when auto_remediate is true and dry_run is false. Otherwise report_only logs what would run.
approval_gate (io.kestra.plugin.core.flow.Pause) holds the run for a human. remediate_rollback (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one XA ROLLBACK per targeted branch.
reverify re-reads XA RECOVER. verify_result fails the run through io.kestra.plugin.core.execution.Fail when any targeted branch is still listed.

What you get

A detector for the stuck prepared XA branch, the failure mode that silently holds locks and stalls writers after a transaction manager dies.
An age gate that tells an abandoned branch apart from a branch a live commit is still finishing, so recovery never races a healthy transaction.
A gated rollback that rebuilds a binary-safe xid from the hex XA RECOVER CONVERT XID output.
A real verification step. The run passes only when the second XA RECOVER read shows every targeted branch gone.
Slack alerts whenever an orphan is present and on any failure.

Who it's for

Platform engineers who run MySQL behind a distributed transaction manager (JTA, a messaging bridge, a two-phase commit across services) where a crashed coordinator can abandon a prepared branch.
DBAs chasing a purge that will not advance or writers that block on locks with no visible owner.
Teams who want a safe, scheduled recovery for prepared XA branches instead of a manual XA RECOVER plus XA ROLLBACK at 3am.

Why orchestrate this with Kestra

MySQL has no scheduler, no age memory and no way to prove a rollback worked. Kestra adds each piece. It polls XA RECOVER on a schedule, remembers the first time each branch was seen in a KV cursor so the age gate holds across runs, gates the rollback behind dry-run and human approval, re-reads XA RECOVER to turn a hopeful rollback into a verified one. A Slack alert fires the moment an orphan appears or a step fails.

Tested end to end

Run on Kestra 2.0.5 against a MySQL 8.0 server with real prepared XA branches forced by opening a session, running XA START, XA END, XA PREPARE, then closing the connection with no commit.

Detector can fail: a server with zero prepared branches returned prepared 0, target 0 and took the clean path. The same server with three abandoned branches returned prepared 3.
Dry run on three orphans detected them, alerted and rolled back nothing. XA RECOVER showed the same three branches before and after.
Remediate with approval rolled back all three. The re-read of XA RECOVER returned empty and the seeded rows were gone, confirmed with the mysql client.
Age gate: with min_age_minutes set to 10, a branch prepared seconds earlier was reported but not targeted, so XA RECOVER still listed it after the run.
Re-verify can fail: a run whose rollback could not clear a targeted branch failed on the second XA RECOVER read rather than reporting success.
An unreachable host failed the detect step and fired the Slack alert through the errors block.

Prerequisites

A MySQL server reachable over JDBC and a user that can run XA RECOVER and XA ROLLBACK. On MySQL 8.0 that user needs the XA_RECOVER_ADMIN privilege.

Secrets

MYSQL_USERNAME: MySQL user, read when use_secret_auth is true.
MYSQL_PASSWORD: password for that user.
SLACK_WEBHOOK_URL: used by the alerts and the errors block.
In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

Name	Type	Default	Description
`mysql_url`	STRING	`jdbc:mysql://localhost:3306/`	JDBC URL of the MySQL server to guard.
`use_secret_auth`	BOOL	`true`	Read credentials from secrets. Set false only for inline test credentials.
`mysql_username`	STRING	`root`	Username used only when use_secret_auth is false.
`mysql_password`	STRING	``	Password used only when use_secret_auth is false.
`min_age_minutes`	INT	`15`	Minimum time a branch must have been seen prepared before it is targeted.
`cursor_key`	STRING	`xa_orphan_first_seen`	KV key for the first-seen map. Use a distinct key per guarded server.
`dry_run`	BOOL	`true`	Report without rolling back.
`auto_remediate`	BOOL	`false`	Roll back targeted branches behind the approval gate when dry_run is false.

Quick start

Set MYSQL_USERNAME, MYSQL_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
Import this flow.
Run it with mysql_url pointed at the server. The default dry run reports prepared branches and records first-seen.
Enable the schedule trigger (safe mode) for min_age_minutes so the cursor ages the branches.
To clear the orphans, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

Lower min_age_minutes for a staging server where no real distributed transactions run. Raise it where long-lived prepared branches are expected.
Correlate a target with its held locks by reading performance_schema.data_locks and information_schema.innodb_trx before the rollback for a richer alert.
Open a ticket instead of rolling back, for an environment where every XA rollback needs a change record.
Point cursor_key at a per-server value and schedule one copy of the flow per MySQL instance.

MySQL Orphaned XA Transaction Rollback Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

MySQL Orphaned XA Transaction Rollback Guard

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

More Infrastructure Blueprints

New to Kestra?