DAG-processor keeps on crashing due to MySql deadlock errors

[msumit]

Apache Airflow version

Other Airflow 3 version (please specify below)

If "Other Airflow 3 version" selected, which one?

3.0

What happened?

DAG processor keeps on crashing due to Mysql Deadlock errors.

File "/opt/airflow/venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 330, in _query\n db.query(q)\n File "/opt/airflow/venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 280, in query\n _mysql.connection.query(self, query)\nsqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1213, 'target: xxxxx.0.primary: vttablet: rpc error: code = Aborted desc = Deadlock found when trying to get lock; try restarting transaction (errno 1213) (sqlstate 40001) (CallerID: xxxxx-airflow-dag-processor): Sql: "delete from dag_schedule_asset_alias_reference where dag_schedule_asset_alias_reference.dag_id in (select dag.dag_id from dag where dag.is_stale = :dag_is_stale /* INT64 */)", BindVars: {#maxLimit: "type:INT64 value:\\"1000001\\""dag_is_stale: "type:INT64 valu')\n[SQL: DELETE FROM dag_schedule_asset_alias_reference WHERE dag_schedule_asset_alias_reference.dag_id IN (SELECT dag.dag_id \nFROM dag \nWHERE dag.is_stale = 1)]\n(Background on this error at: https://sqlalche.me/e/14/e3q8)

What you think should happen instead?

The DAG processor should not crash and process DAGs.

How to reproduce

Setup Airflow 3.0 through virtualenv and use a Mysql instance to run dag processor.

Operating System

Debian GNU/Linux 12 (bookworm)

Versions of Apache Airflow Providers

apache-airflow-providers-cncf-kubernetes 10.4.3
apache-airflow 3.0.0
apache-airflow-core 3.0.0
apache-airflow-ctl 0.0.1
apache-airflow-providers-common-compat 1.6.0
apache-airflow-providers-common-io 1.5.4
apache-airflow-providers-common-sql 1.26.0
apache-airflow-providers-fab 2.0.1
apache-airflow-providers-http 5.2.2
apache-airflow-providers-smtp 2.0.3
apache-airflow-providers-standard 1.0.0
apache-airflow-providers-uber 1.0.1
apache-airflow-task-sdk 1.0.0

Deployment

Virtualenv installation

Deployment details

No response

Anything else?

The stacktrace is from Airflow running against Vitess-Mysql, but we hit the same issue with normal Mysql 8 as well.

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

[notatallshaw-gts]

I've not migrated to Airflow 3 yet, but if this PR remains open for a long time I'm happy to test your patch when I migrate, I have seen deadlock issues on Airflow 2.x, and in fact there's some features we've been wary of because of these (e.g. dynamic task mapping).

[potiuk]

I've not migrated to Airflow 3 yet, but if this PR remains open for a long time I'm happy to test your patch when I migrate, I have seen deadlock issues on Airflow 2.x, and in fact there's some features we've been wary of because of these (e.g. dynamic task mapping).

Maybe you simply "hold your phone wrongly" ;) (see the related PR)

[notatallshaw-gts]

Maybe you simply "hold your phone wrongly" ;) (see the related PR)

Stretching this metaphor to breaking point, our IT team is holding our phone and I'd rather they hold it wrong than I have to hold it.

[potiuk]

Maybe you simply "hold your phone wrongly" ;) (see the related PR)

Stretching this metaphor to breaking point, our IT team is holding our phone and I'd rather they hold it wrong than I have to hold it.

Perfectly understood :) ... in this case it's actually Airflow maintainers who take the phone from MySQL team, and hand it over to your IT team, adding a nice shiny cover around it, without even knowing that the cover is kinda forcing the IT team (if their hands are big enough) to hold it wrongly - all while they pass it to your ear ... but that's likely a very, very, very far stretch