airflow db clean --tables dag_version --skip-archive hangs forever (MDL deadlock) when an old dag_version is FK-pinned by task_instance #66177

Under which category would you file this issue?

Airflow Core

Apache Airflow version

3.2.1 (also reproduced on 3.1.8)

What happened and how to reproduce it?

Issue Description

airflow db clean --tables dag_version --skip-archive --yes hangs forever on a MySQL backend when any old dag_version row is referenced by a live task_instance row (which is the normal case for any DAG with run history older than the cleanup horizon).

The hang is silent — no error is logged, the CLI just blocks. In a Kubernetes CronJob this means stuck pods accumulate indefinitely; the default MySQL lock_wait_timeout is 31_536_000 seconds (one year).

Root cause (verified against the source):

1. task_instance.dag_version_id has ON DELETE RESTRICT.
2. _do_delete in airflow-core/src/airflow/utils/db_cleanup.py issues a multi-table DELETE FROM dag_version USING dag_version, _airflow_deleted__... WHERE ..., which fails with MySQL error 1451 Cannot delete or update a parent row.
3. The except BaseException as e: raise e block (lines 285–286) re-raises without calling session.rollback(). The session/connection therefore still holds SHARED_WRITE on dag_version and SHARED_READ on the archive table.
4. The finally: block (lines 288–291) calls target_table.drop(bind=session.get_bind()), which checks out a second connection from the pool. DROP TABLE requires EXCLUSIVE metadata-lock → blocked forever waiting for the first connection (still alive in the same call stack) to release its SHARED_* locks.

The _do_delete body in 3.2.1 is byte-identical to 3.1.8. The only dag_version-related change in 3.2.1 was adding keep_last=True, keep_last_group_by=["dag_id"] (lines 165–172) which mitigates only the trivial case of a single dag_version row per DAG.

Steps to reproduce

Fully containerised reproducer (MySQL 8.4 in Docker + venv install of pinned Airflow with constraints file):

https://github.com/diogosilva30/airflow-db-clean-dag-version-deadlock

git clone https://github.com/diogosilva30/airflow-db-clean-dag-version-deadlock
cd airflow-db-clean-dag-version-deadlock
./run.sh                    # AIRFLOW_VERSION=3.2.1 by default
AIRFLOW_VERSION=3.1.8 ./run.py  # also reproduces

run.py boots MySQL, installs Airflow with constraints, seeds two dag_version rows for one DAG (one back-dated to 2024-01-01, one current), inserts a task_instance pinning the old one, then runs airflow db clean --tables dag_version --clean-before-timestamp 2026-01-01 --skip-archive --yes with a 30s timeout and prints MySQL processlist + metadata_locks.

Observed output during the hang (verbatim from a 3.2.1 run):

=== processlist ===
id   command secs  state                            query
34   Query   16    Waiting for table metadata lock  DROP TABLE _airflow_deleted__dag_version__20260430191651
35   Sleep   16                                     NULL                ← failed-DELETE conn, locks held

=== metadata locks ===
TABLE  airflow  dag_version                            SHARED_WRITE  GRANTED  thread 662
TABLE  airflow  _airflow_deleted__dag_version__...     SHARED_READ   GRANTED  thread 662
TABLE  airflow  _airflow_deleted__dag_version__...     EXCLUSIVE     PENDING  thread 661  ← DROP, blocked

What you think should happen instead?

The CLI should fail fast and clearly when it can't delete, not deadlock silently. A minimal two-line fix:

except BaseException:
    session.rollback()
    raise
finally:
    if target_table is not None and skip_archive:
        target_table.drop(bind=session.connection())
        session.commit()

I patched a live 3.2.1 install with this and re-ran the same reproducer:

This stops the hang and surfaces the real error. It does not by itself enable cleanup of FK-pinned dag_version rows; doing that needs a separate change to _build_query for dag_version to exclude rows still referenced by task_instance.dag_version_id (and dag_run.created_dag_version_id). Without such filtering, dag_version cannot be safely included in default --tables lists at all — any environment with run history older than the cleanup horizon will trip this.

A diff of the proposed fix is in patch/db_cleanup_fix.diff of the reproducer repo.

Operating System

Reproduced on macOS 14 (Docker Desktop) and Linux (GKE node, Container-Optimized OS) — not OS-specific; the hang is a MySQL metadata-lock issue.

Deployment

Other 3rd-party Helm chart

Apache Airflow Provider(s)

mysql

Versions of Apache Airflow Providers

apache-airflow-providers-mysql==6.4.0
apache-airflow-providers-standard==1.7.0

(Both versions resolved via the official constraints-3.2.1 / constraints-3.1.8 files for Python 3.12.)

Official Helm Chart version

Not Applicable

Kubernetes Version

Not Applicable (reproduces directly against MySQL; production hit was on GKE 1.30, but unrelated)

Helm Chart configuration

Not Applicable

Docker Image customizations

Not Applicable — bug reproduces on a clean pip install apache-airflow==3.2.1 against an unmodified mysql:8.4.

Anything else?

• Reproduces 100% of the time on 3.1.8 and 3.2.1 when an old dag_version is FK-pinned by a task_instance.
• Does not reproduce on Postgres (Postgres releases the failed-statement locks differently and the same-connection vs. pool issue doesn't manifest as MDL deadlock; not yet verified whether a different failure mode exists there).
• The orphan _airflow_deleted__dag_version__YYYYMMDD... archive tables left behind after a forced kill are easy to spot and can be dropped manually.
• Operational mitigation users can apply today: exclude dag and dag_version from airflow db clean --tables until this is fixed.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct