seanmuth commented on issue #65712:
URL: https://github.com/apache/airflow/issues/65712#issuecomment-4959139200
**Confirming this is still present on 3.2.2 (i.e. with #67246 already
applied), at scale.**
Adding a concrete reproduction from a large on-prem deployment in case the
numbers help narrow the hot path.
**Environment**
- Airflow **3.2.2** (upgraded from 3.1.8), CeleryExecutor
- ~5,000 DAGs; the DAG involved in the repro has **~350 tasks**
- Postgres metadata DB behind PgBouncer
**Symptom**
Sessions pile up in `idle in transaction` while still **holding
`task_instance` row locks**, and the count grows over time until PgBouncer's
pool is exhausted and the api-servers/schedulers restart. The blocking chains
are dominated by two query shapes:
1. The heartbeat update (matches @SamWheating's report above):
```sql
UPDATE task_instance
SET last_heartbeat_at = '…', updated_at = '…'
WHERE task_instance.id = '…'::uuid
AND task_instance.state = 'running'
AND task_instance.hostname = '…'
AND task_instance.pid = …;
```
2. A grid/TI read (matches @mdw00d's report above):
```sql
SELECT task_instance.task_id, task_instance.state,
task_instance.dag_version_id,
task_instance.start_date, task_instance.end_date,
dag_version.version_number
FROM task_instance
LEFT OUTER JOIN dag_version ON task_instance.dag_version_id =
dag_version.id
WHERE task_instance.dag_id = '<large_dag>'
AND task_instance.run_id = 'scheduled__…'
ORDER BY task_instance.task_id;
```
**Correlated endpoint latencies** (captured while clearing tasks on the
~350-task DAG and browsing the grid concurrently):
| Endpoint | Result | Duration |
|---|---|---|
| `POST .../clearTaskInstances` | 200 | ~90s, ~170s, ~175s, ~325s |
| `POST .../clearTaskInstances` | **500** | ~345s, ~365s, ~375s, ~485s |
| `GET /ui/grid/structure/<large_dag>` (limit=10) | 200 | ~205s, ~245s,
~250s, ~265s |
**Trigger:** clearing a large task group while the grid UI is being polled
for the same DAG. The idle-in-transaction sessions holding `task_instance`
locks block the heartbeat `UPDATE`s, workers then time out heartbeating and
retry/terminate, and the scheduler stalls on the same table — consistent with
the cascade described in the issue.
**Interim mitigation we're validating** (not a fix): scoping a tighter
`idle_in_transaction_session_timeout` on the Airflow DB role to forcibly
release the leaked locks and keep the pool from exhausting. It stops the
api-server restarts but obviously doesn't address the sessions being held open
across slow work in the first place.
Happy to pull more detail (full `pg_stat_activity` snapshots with
`wait_event`/`state`, blocking-PID chains) if useful.
---
🤖 Generated with [Claude Code](https://claude.com/claude-code) — Claude Opus
4.8 (1M context)
--
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]
