boqiang commented on issue #59120:
URL: https://github.com/apache/airflow/issues/59120#issuecomment-6158568975
Hi maintainers — is anyone currently working on a fix for this issue, or is
there an active PR I should coordinate with? I would be happy to contribute a
MySQL deadlock regression test and help with a fix. I noticed the prior
session-isolation/savepoint proposals and would like to avoid duplicating work.
Below is a sanitized technical analysis, without organization-specific data.
### Conclusion
A MySQL deadlock during DAG metadata autoflush can become a fatal scheduler
error when the original database exception is caught and execution continues
with a failed SQLAlchemy Session. The next ORM operation raises
`PendingRollbackError`, which can escape the scheduling loop.
### Failure mechanism
The relevant path is `_create_dag_runs` and its enclosing transaction/retry
boundary. Concurrent updates to parsing fields (`last_parsed_time`,
`last_parse_duration`) and scheduling fields (`next_dagrun*`) can contend on
`dag` rows. The deadlock is the trigger; reusing the failed Session turns it
into a scheduler failure. The DAG named in a creation-error message need not be
the DAG whose pending UPDATE was autoflushed.
```mermaid
sequenceDiagram
participant S as Scheduler
participant O as SQLAlchemy Session
participant D as MySQL
S->>O: Create DagRun / autoflush metadata
O->>D: UPDATE dag amid concurrent metadata writes
D-->>O: Deadlock (1213)
O-->>S: Database exception
Note over S,O: Broad handler catches error and continues
S->>O: Next ORM operation on failed Session
O-->>S: PendingRollbackError
Note over S: Exception escapes scheduling loop
```
### Supporting code evidence
* In the official 3.1.8 source, `_create_dag_runs` catches `Exception`
around DagRun creation and continues.
* `retry_db_transaction` / `run_with_db_retries` in
`airflow/utils/retries.py` handles `DBAPIError`, including rollback before
retry. Swallowing the original error prevents that path; the later
`PendingRollbackError` is not the configured retry category.
* #59121 adds a TODO describing this problem, rather than implementing
recovery.
### Proposed direction — not implemented or validated
Could database exceptions be propagated to the existing owning
transaction/retry boundary instead of being swallowed by per-DAG handlers? For
example, explicitly re-raising `DBAPIError` before a generic handler is one
candidate to evaluate, including other broad handlers on the same path. The
owner would roll back the failed unit, discard/reload ORM state and retry with
bounded backoff, re-querying candidates and reacquiring required HA locks.
```mermaid
sequenceDiagram
participant S as Scheduler
participant T as Owning transaction/retry boundary
participant O as SQLAlchemy Session
participant D as MySQL
S->>T: Schedule a batch
T->>O: Query / flush
O->>D: Metadata transaction
D-->>O: Deadlock (1213)
O-->>T: Propagate original DBAPIError
T->>O: Roll back failed unit and reload state
Note over T: Bounded backoff, re-query and reacquire HA locks
T->>O: Retry the transaction
O->>D: Retry metadata work
D-->>O: Success
T-->>S: Continue scheduling
```
This is a proposal for discussion, not a claim that adding one exception
clause is sufficient. It needs real MySQL testing for full-transaction deadlock
rollback, HA lock correctness, duplicate-run prevention, retry exhaustion and
continued scheduler heartbeats. Simply increasing retry counts or adding
`PendingRollbackError` to retry eligibility would not address the swallowed
original error. Savepoint approaches also need to account for `prohibit_commit`
and MySQL deadlock semantics.
Would this recovery boundary be an acceptable direction, or is there a
preferred approach / existing PR I could help validate?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
