Phinhas214 opened a new issue, #75830:
URL: https://github.com/apache/airflow/issues/75830
### Under which category would you file this issue?
Airflow Core
### Apache Airflow version
3.4.0
### What happened and how to reproduce it?
### Issue Description
Airflow allows one active backfill per Dag. Concurrent `POST
/api/v2/backfills` requests for the same Dag can create more than one. Related:
#68699 (same setup), #68705.
`_create_backfill` counts active backfills (`num_active`) without a lock,
then inserts the `Backfill` row. Two requests can both count 0 before either
one commits. Both get HTTP 200.
Only the first backfill gets Dag runs. The later ones have one
`backfill_dag_run` row per date with `exception_reason = 'in flight'` and no
Dag runs. The user sees success for a backfill that will never run anything.
### How to reproduce
Steps:
1. Use the Dag from #68699 (`@daily`, 2020-01-01 to 2022-12-31, 1096 runs).
2. Send N concurrent `POST /api/v2/backfills` for that Dag with the same
date range.
3. Count active backfills:
`SELECT id FROM backfill WHERE dag_id = 'test_backfill_bug' AND
completed_at IS NULL;`
My results on main went like this. (one "round" = N concurrent requests
after deleting all backfills):
| Requests per round | Rounds | Active backfills per round |
|---|---|---|
| 10 | 5 | 1, 1, 1, 1, 1 |
| 20 | 4 | 2, 3, 1, 3 |
| 100 | 1 | 3 |
Example (20 requests): backfill 28 is active with 1096 Dag runs. Backfill 29
is active with 1096 `in flight` rows and 0 Dag runs.
The race is in main today. #68705 replaces the early `session.commit()` with
a flush, so the window becomes the whole run creation and the race gets easier
to hit.
(With 20 or more requests, some requests also return HTTP 500. These are
SQLAlchemy `QueuePool` timeouts after 30 s with the default pool size (5 + 10
overflow). I think that is a separate load limit, not this bug.)
### What you think should happen instead?
### What you think should happen instead?
One request gets HTTP 200. All other requests get HTTP 409
(`AlreadyRunningBackfill`), until that backfill completes.
### Operating System
MacOS
### Deployment
Docker-Compose
### Apache Airflow Provider(s)
_No response_
### Versions of Apache Airflow Providers
_No response_
### Official Helm Chart version
_No response_
### Kubernetes Version
_No response_
### Helm Chart configuration
_No response_
### Docker Image customizations
_No response_
### Anything else?
### Options for a guard
1. **Row lock:** lock the `DagModel` row with `with_row_locks` before the
`num_active` count. Works on PostgreSQL and MySQL 8. Does nothing on SQLite (no
`FOR UPDATE`) or when `[scheduler] use_row_level_locking = False`. While the
lock is held, the scheduler skips this Dag (`skip_locked`).
2. **Partial unique index** on `backfill(dag_id) WHERE completed_at IS
NULL`. Works on PostgreSQL and SQLite. MySQL has no partial indexes, so it
needs a different design there. Needs a migration that handles existing
duplicates.
3. **SQLite only:** count again after the insert. On SQLite the second
writer waits for the first one's lock, so the second count can see the first
row. Not tested yet.
### Question for maintainers
Do you prefer an application-level guard (row lock on `DagModel`, plus
option 3 for SQLite) or a database constraint (partial unique index, with a
different design for MySQL)?
I am working on a fix.
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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]
