sushrutikhar opened a new issue, #74948:
URL: https://github.com/apache/airflow/issues/74948
### Under which category would you file this issue?
Airflow Core
### Apache Airflow version
3.3.2 (the code is unchanged on main as of 2026-10-18; line numbers below
are 3.3.2's)
### What happened?
#74239 and #74247 fixed two responses that queried the database inside
`dump_json`, on the event loop: `get_dag_details` and `get_pools`. `GET
/api/v2/dags` does the same.
`generate_dag_with_latest_run_query` adds `selectinload(DagModel.tags)`
(`api_fastapi/common/db/dags.py:49`). The route returns the un-iterated
`ScalarResult` (`dags = session.scalars(dags_select)`,
`core_api/routes/public/dags.py:178`) into `dags: Iterable[DAGResponse]`
(`core_api/datamodels/dags.py:173`), which pydantic validates lazily. So the
selectin query on `dag_tag` runs while FastAPI 0.136.3 serializes the response
on the loop (`fastapi/routing.py:292-306`: validation in the threadpool,
serialization on the loop).
The query uses the route's own connection, because function-scoped
dependencies exit after serialization (`fastapi/routing.py:118-121`). While it
waits (on a lock, a slow disk or a busy server), the process answers nothing
else, including `/execution/health/ping` and `/api/v2/monitor/health`.
With `dag_tag` locked for 60 s and one `GET /api/v2/dags` in flight
(commands under *How to reproduce*). `t` is seconds from taking the lock; the
listing was sent at t+2s:
```
before: ping 200 2.696615s | health 200 0.005201s | dags 200 0.023518s
t+3s: ping 000 3.001651s | health 000 3.002222s
t+10s: ping 000 3.002274s | health 000 3.002221s
t+17s: ping 000 3.002221s | health 000 3.002275s
t+24s: ping 000 3.002732s | health 000 3.002301s
t+31s: ping 000 3.002335s | health 000 3.002271s
t+38s: ping 000 3.002375s | health 000 3.002221s
t+45s: ping 000 3.002259s | health 000 3.002239s
t+52s: ping 000 3.002253s | health 000 3.002248s
t+59s: ping 200 0.907544s | health 200 0.005766s
t+61s: ping 200 0.002542s | health 200 0.005371s
GET /api/v2/dags: 200 after 58.041028s
```
As a control, the same lock with `GET /api/v2/pools` in flight (which reads
no `dag_tag`) gives `pools 200 0.026610s`, and ping and health answer in 2-6 ms
throughout.
The api-server's main thread during the hold (`py-spy dump`, t+~10s):
```
Thread 2414 (idle): "MainThread"
do_execute (sqlalchemy/engine/default.py:952)
_exec_single_context (sqlalchemy/engine/base.py:1969)
_execute_context (sqlalchemy/engine/base.py:1848)
_execute_clauseelement (sqlalchemy/engine/base.py:1643)
_execute_on_connection (sqlalchemy/sql/elements.py:526)
execute (sqlalchemy/engine/base.py:1421)
orm_execute_statement (sqlalchemy/orm/context.py:306)
_execute_internal (sqlalchemy/orm/session.py:2271)
execute (sqlalchemy/orm/session.py:2373)
_load_via_parent (sqlalchemy/orm/strategies.py:3426)
_load_for_path (sqlalchemy/orm/strategies.py:3350)
invoke (sqlalchemy/orm/loading.py:1564)
chunks (sqlalchemy/orm/loading.py:247)
iterrows (sqlalchemy/engine/result.py:566)
dump_json (pydantic/type_adapter.py:677)
serialize_json (fastapi/_compat/v2.py:231)
serialize_response (fastapi/routing.py:306)
app (fastapi/routing.py:695)
app (fastapi/routing.py:120)
wrapped_app (starlette/_exception_handler.py:42)
...
run (uvicorn/server.py:77)
_run_api_server_with_uvicorn
(airflow/cli/commands/api_server_command.py:138)
Thread 2455 (idle): "AnyIO worker thread"
wait (threading.py:355)
get (queue.py:171)
run (anyio/_backends/_asyncio.py:1089)
```
Where the same request checks connections out, with no lock, using the
checkout listener from the script in #74239 on `create_app("core")`:
```
GET /api/v2/dags -> 200, total_entries 1, checkouts 3, on the event loop 1
[event loop] core_api/security.py:155 get_user >
core_api/security.py:126 resolve_user_from_token > ... (is_revoked, #66493)
[AnyIO worker thread] core_api/security.py:368
depends_permitted_dags_filter > ... base_auth_manager.py:588
get_authorized_dag_ids
[AnyIO worker thread] core_api/routes/public/dags.py:152 get_dags > ...
common/db/common.py:197 paginated_select > utils/db.py:1762 get_query_count
```
The `dag_tag` query is not among the checkouts, because it runs on the
connection the route already holds. A checkout listener therefore does not show
this case; a statement listener, or a stack dump as above, does.
### What you think should happen instead?
Response serialization should not query the database. A slow or locked
`dag_tag` should then slow `GET /api/v2/dags` only, not every request the
process serves.
Materialise the rows in the route, inside the threadpool:
`session.scalars(dags_select).all()`, as `patch_dags` already does, or `dags:
list[DAGResponse]`, as #74247 did for pools. Then the selectin query runs while
the route builds the response.
**Scope (from reading 3.3.2; only `get_dags` reproduced).**
- 19 response fields in `core_api/datamodels` (16 files) are typed
`Iterable[...]`, plus 2 in `execution_api/datamodels`.
- 13 route handlers assign a bare `session.scalars(...)` result instead of
calling `.all()`: 12 in the core API and 1 in the Execution API.
- Any of these whose rows load a relationship, or read a lazy attribute
while they are produced, will query on the loop the same way. `pools` was one
of them before #74247.
A blanket alternative is to serialize sync endpoints in the threadpool, the
same place FastAPI already validates them (`fastapi/routing.py:292-297`). We
run that as a workaround; it keeps the loop free, but each response then holds
a threadpool slot while it serializes.
### How to reproduce
Environment: unmodified `apache-airflow[postgres]==3.3.2` on the 3.3.2
constraints, Python 3.12.3, PostgreSQL 16.15, Ubuntu 24.04. `airflow
api-server` runs with its defaults (uvicorn, 1 worker).
```bash
export AIRFLOW_HOME=/tmp/repro
export
AIRFLOW__DATABASE__SQL_ALCHEMY_CONN=postgresql+psycopg2://postgres:[email protected]:5432/postgres
export AIRFLOW__CORE__DAGS_FOLDER=/tmp/repro/dags
AIRFLOW__CORE__LOAD_EXAMPLES=False
export
AIRFLOW__CORE__AUTH_MANAGER=airflow.api_fastapi.auth.managers.simple.simple_auth_manager.SimpleAuthManager
export AIRFLOW__CORE__SIMPLE_AUTH_MANAGER_ALL_ADMINS=True
export AIRFLOW__API_AUTH__JWT_SECRET=$(python -c "import secrets;
print(secrets.token_hex(32))")
mkdir -p /tmp/repro/dags
cat > /tmp/repro/dags/tagged.py <<'EOF'
from airflow.sdk import DAG, task
with DAG("tagged", schedule=None, tags=["example"]):
@task
def noop():
pass
noop()
EOF
airflow db migrate && airflow dags reserialize
airflow api-server --port 8080 &
```
Get a token with
`get_auth_manager().generate_jwt(SimpleAuthManagerUser(username="admin",
role="admin"))`, as in the script in #74239. Then:
```bash
# session 1: hold dag_tag for 60 s
psql -c "BEGIN; LOCK TABLE dag_tag IN ACCESS EXCLUSIVE MODE; SELECT
pg_sleep(60); COMMIT;" &
sleep 2
# session 2: one listing
curl -s -o /dev/null -w 'GET /api/v2/dags: %{http_code} after
%{time_total}s\n' \
-H "Authorization: Bearer $TOKEN" http://127.0.0.1:8080/api/v2/dags &
# session 3: the probes, 3 s timeout each
curl -s -o /dev/null -m 3 -w '%{http_code} %{time_total}s\n'
http://127.0.0.1:8080/execution/health/ping
curl -s -o /dev/null -m 3 -w '%{http_code} %{time_total}s\n'
http://127.0.0.1:8080/api/v2/monitor/health
```
### Operating System
Ubuntu 24.04 (Linux 6.17), Python 3.12.3
### Versions of Apache Airflow Providers
`apache-airflow-providers-postgres` 7.0.2 (the `postgres` extra) and the
providers `apache-airflow` 3.3.2 installs by default, all at the versions
pinned by `constraints-3.3.2/constraints-3.12.txt`. The reproduction uses none
of them beyond the PostgreSQL driver.
### Deployment
Other
### Deployment details
`pip install "apache-airflow[postgres]==3.3.2"` with the 3.3.2 constraints,
and `airflow api-server` started directly (uvicorn, 1 worker), against
PostgreSQL 16.15. FastAPI 0.136.3, Starlette 1.6.0, pydantic 2.13.5
(pydantic-core 2.46.5), SQLAlchemy 2.0.52, uvicorn 0.52.4, anyio 4.15.1,
psycopg2-binary 2.9.13.
### Anything else?
The same pattern exists elsewhere in the code (the scope list above), but
only `get_dags` has been reproduced. The one checkout on the loop in the
listing above is `is_revoked`, which is #66493 / #73422, not this issue.
--
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]
