Mitigating database lock contention during concurrent Airflow DAG runs

Question

When multiple DAG runs are triggered simultaneously, Airflow's Scheduler and Executor often experience latency spikes. This behavior is typically linked to database lock contention on dag_run and task_instance tables, as well as GIL-related parsing delays when high DAG counts overlap.
The goal is to determine which specific configuration adjustments or architectural patterns consistently mitigate these latencies without sacrificing overall system throughput.
Constraints and uncertainties include:

Contention patterns vary significantly depending on the executor (LocalExecutor vs. CeleryExecutor).
While Airflow 2.8.x updated lock acquisition strategies, the exact threshold for row-level lock serialization remains poorly documented.
The interaction between max_active_runs_per_dag, max_active_tasks_per_dag, and the executor's pool size is non-deterministic.

What configuration changes can be applied to reduce latency spikes when multiple DAG runs are triggered concurrently? Does adjusting concurrency limits relative to worker pool size reliably resolve database lock contention?

Tasadduq Burney · Answer

Direct Answer
Adjust these configuration parameters to reduce latency spikes from database lock contention during concurrent DAG runs:

scheduler__max_tis_per_query: 512 (default in Airflow 2.8+) — limits task instances fetched per scheduling loop, reducing row-lock hold time.
  scheduler__max_dagruns_per_loop_to_schedule: 10 — caps DAG runs processed per scheduler iteration.
  core__max_active_runs_per_dag: 16–32 — prevents queued runs from piling up in dag_run with state=queued.
  core__max_active_tasks_per_dag: 16–64 — aligns with worker slot capacity to avoid task polling storms.
  scheduler__use_row_level_locking: true (default 2.8+) — requires PostgreSQL 12+ or MySQL 8.0+; older versions fall back to table locks.
  celery__worker_concurrency: ≤ (DB max_connections - 20) / 2 — each worker process holds a DB connection.
  celery__task_acks_late: true with worker_prefetch_multiplier: 1 — prevents task hoarding that amplifies task_instance row contention.
  scheduler__min_file_process_interval: 30 and scheduler__dag_dir_list_interval: 300 — staggers DAG parsing to reduce GIL contention across scheduler processes.

For LocalExecutor, also set core__parallelism ≤ CPU cores × 2 and core__sql_alchemy_pool_size=5 with max_overflow=10.

Mitigating database lock contention during concurrent Airflow DAG runs

1 answer

Direct Answer

Your perspective belongs here.

0 question comments