Case study

Airflow tasks stuck in "queued" forever after a twelve-second database blip

A short network flap between two datacenters left a task in "queued" for two and a half hours, and its DAG with it. The cause is a lost executor event: SELECT … FOR UPDATE SKIP LOCKED skips exactly the row it needs. Why restarting the scheduler is the wrong fix.

3 min read · Markdown

TL;DR

• With Airflow 2.11 and the LocalExecutor, a task process that dies while updating its own row leaves an event the scheduler throws away. The scheduler reads executor events with SELECT … FOR UPDATE SKIP LOCKED, skips the row that is still locked, and drops the event. The task instance stays "queued", and nothing in the LocalExecutor ever times it out.
• The trigger was a database blip of about twelve seconds, caused by a link flap between datacenters. Every connection crossed that link twice.
• The fix is to clear the task instance. Restarting the scheduler kills every running task, because tasks are child processes in the scheduler's cgroup.

The symptom

One afternoon a sensor task sat in "queued" for two and a half hours. Its DAG allows one active run, so the whole pipeline behind it stopped until the on-call engineer cleared the task by hand. Alerts of the form "state attribute is queued" had appeared on three earlier days in the summer and autumn, and each time nobody found a cause.

The setup

• Airflow 2.11 with the LocalExecutor (parallelism 128) on a VM in datacenter A.
• The metadata database: PostgreSQL under Patroni, with pgbouncer on three VMs. Its leader is also in datacenter A.
• The way in: HAProxy pods in a Kubernetes cluster in datacenter B. They pick the leader with Patroni's REST health check and drop all sessions when a node is marked down.

So every database connection from the scheduler and its tasks went from A to B and back to A. Any flap on the A–B link broke all of them at once.

What the logs showed

The scheduler's event log has a gap of about twelve seconds, during which the database answered nobody. Then came server closed the connection unexpectedly, and less than a minute later one more drop, at connect time. Patroni did not fail over: the timeline had not changed in months.

Two kinds of task failures came out of the blip:

• A task that failed while connecting was handled correctly: marked failed, alert sent.
• A task that failed in the middle of updating its own row to "running", with the row already locked FOR UPDATE, left a trace like this in the scheduler log: Received executor event with state failed, without the TaskInstance Finished line that should follow.

The mechanism

1. The task process starts and runs UPDATE task_instance SET state = 'running' …. The row is locked.
2. The connection dies mid-transaction. The process exits; the LocalExecutor reports a failed event.
3. The scheduler processes executor events and loads the task instances with SELECT … FOR UPDATE SKIP LOCKED, so that several schedulers can share the work.
4. The row is still locked, most likely because the database has not yet noticed that the client is gone. SKIP LOCKED skips the row. The event is consumed and dropped.
5. The task instance stays "queued". The LocalExecutor in 2.11 has no handling for task_queued_timeout, so nothing ever revisits it.

The fix, and the wrong fix

Clear the stuck task instance. In the UI: Browse → Task Instances, state = queued, and check queued_when.

Do not restart the scheduler. The service runs with KillMode=control-group and a 90-second stop timeout. Tasks run as child processes inside the scheduler's cgroup, so a restart kills everything that is running. Some of our long tasks can only be restarted together with their whole DAG.

What we changed and recommend

• Alert on task instances queued longer than a few minutes, with the DAG and the queued_when time, and link the runbook entry: clear, don't restart.
• Look for a network flap first. On the next drop, check the inter-datacenter link channel for the same minute before anything else. The cause is invisible from Airflow's side.
• Fix the topology. Keep the path between the scheduler, the pooler and the database leader inside one datacenter, or accept that any link flap breaks every session twice.
• Collect logs while they exist. HAProxy pod logs rotated within about 30 minutes, and were gone by the time anyone looked. The scheduler writes about 5,500 lines a minute, so search it in one-hour windows.

Takeaways

• SKIP LOCKED trades correctness for throughput. Any event processed through it can be skipped, and something must come back for what was skipped.
• A task "queued" forever is a lost event, not a slow scheduler.
• Know your process model before you restart anything.
• Topology is part of reliability. A connection that crosses a link twice fails twice as often.

All writing · A similar problem? contact@ivanblinov.com