This is an automated email from the ASF dual-hosted git repository.
vincbeck pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git
The following commit(s) were added to refs/heads/main by this push:
new bee092f4f1b Fix psycopg 3 lock errors crashing the scheduler and Dag
processor (#77246)
bee092f4f1b is described below
commit bee092f4f1b9fd4dbb4ecf0fccca30b6fc504957
Author: Vincent <[email protected]>
AuthorDate: Thu Nov 19 11:25:34 2026 -0500
Fix psycopg 3 lock errors crashing the scheduler and Dag processor (#77246)
psycopg 3 is the default synchronous PostgreSQL driver since #69469, but its
errors carry the SQLSTATE in `sqlstate` instead of psycopg2's `pgcode`, and
their first argument is the message. A real "lock not available" error
(55P03) raised through psycopg 3 was therefore not recognised as one.
PostgreSQL raises that error when the scheduler's NOWAIT lock on the pool
rows meets a row lock held by another transaction, such as a pool update
from the API or the CLI, and when the 30 second lock timeout the Dag
processor uses to mark stale or deleted Dags expires. Both places are meant
to skip the iteration; with psycopg 3 the error escaped and stopped the
scheduler or the Dag processor. Contention between schedulers was not
affected, because the advisory lock taken first raises an error of its own.
Taking only the first code a driver exposes is not enough:
mysql-connector-python sets a generic `sqlstate` next to the MySQL error
code, which hid its deadlocks from the deadlock check.
PostgreSQL users on the default driver can now get the backfill routes' 503
for a lock error. Its text told every user that backfills are not supported
on SQLite, so it now says what happened and gives that advice only when the
error comes from SQLite.
---
.../core_api/routes/public/backfills.py | 13 ++--
airflow-core/src/airflow/utils/sqlalchemy.py | 32 ++++-----
.../core_api/routes/public/test_backfills.py | 35 +++++++---
airflow-core/tests/unit/utils/test_sqlalchemy.py | 79 +++++++++++++++++++++-
4 files changed, 127 insertions(+), 32 deletions(-)
diff --git
a/airflow-core/src/airflow/api_fastapi/core_api/routes/public/backfills.py
b/airflow-core/src/airflow/api_fastapi/core_api/routes/public/backfills.py
index 925e2ff5dfa..b34fa0770f2 100644
--- a/airflow-core/src/airflow/api_fastapi/core_api/routes/public/backfills.py
+++ b/airflow-core/src/airflow/api_fastapi/core_api/routes/public/backfills.py
@@ -16,6 +16,7 @@
# under the License.
from __future__ import annotations
+import sqlite3
from typing import Annotated, NoReturn
from fastapi import Depends, HTTPException, status
@@ -78,13 +79,13 @@ def _raise_locked_response_or_reraise(e: OperationalError,
action: str) -> NoRet
"""Map a database lock OperationalError to HTTP 503, or re-raise if not a
lock error."""
if not is_lock_not_available_error(e):
raise
- raise HTTPException(
- status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
- detail=(
- f"Database is locked. Backfill {action} is not supported on SQLite
"
- "under concurrent access. Please use PostgreSQL or MySQL."
- ),
+ detail = (
+ f"Backfill {action} could not acquire a database lock because another
transaction holds it. "
+ "Retry the request."
)
+ if isinstance(e.orig, sqlite3.OperationalError):
+ detail += " SQLite does not support concurrent writers; use PostgreSQL
or MySQL."
+ raise HTTPException(status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
detail=detail)
@backfills_router.get(
diff --git a/airflow-core/src/airflow/utils/sqlalchemy.py
b/airflow-core/src/airflow/utils/sqlalchemy.py
index c1bc393a3a9..29c55f07aac 100644
--- a/airflow-core/src/airflow/utils/sqlalchemy.py
+++ b/airflow-core/src/airflow/utils/sqlalchemy.py
@@ -747,26 +747,32 @@ def prohibit_commit(session):
return CommitProhibitorGuard(session)
+def _get_db_error_codes(orig: BaseException) -> tuple[Any, ...]:
+ """
+ Return the candidate error-code values of a DB-API exception, without
importing the drivers.
+
+ psycopg2 sets ``pgcode``, psycopg 3 sets ``sqlstate``, and the MySQL
drivers pass the numeric
+ error code as the first argument. mysql-connector-python sets both
``sqlstate`` (``40001`` for
+ a deadlock) and the first argument, so all of them are returned, not the
first one present.
+ """
+ return (getattr(orig, "pgcode", None), getattr(orig, "sqlstate", None),
*orig.args[:1])
+
+
def is_lock_not_available_error(error: OperationalError):
"""Check if the Error is about not being able to acquire lock."""
+ if error.orig is None:
+ return False
# DB specific error codes:
# Postgres: 55P03
# MySQL: 3572, 'Statement aborted because lock(s) could not be acquired
immediately and NOWAIT
# is set.'
# MySQL: 1205, 'Lock wait timeout exceeded; try restarting transaction
# (when NOWAIT isn't available)
- db_err_code = getattr(error.orig, "pgcode", None) or (
- error.orig.args[0] if error.orig and error.orig.args else None
- )
-
- # We could test if error.orig is an instance of
- # psycopg2.errors.LockNotAvailable/_mysql_exceptions.OperationalError, but
that involves
- # importing it. This doesn't
- if db_err_code in ("55P03", 1205, 3572):
+ if any(code in ("55P03", 1205, 3572) for code in
_get_db_error_codes(error.orig)):
return True
# SQLite: `database is locked` (SQLITE_BUSY) — check the error text since
# sqlite3.OperationalError.args[0] is a human-readable string, not a
numeric code
- if error.orig and "database is locked" in str(error.orig).lower():
+ if "database is locked" in str(error.orig).lower():
return True
return False
@@ -778,10 +784,4 @@ def is_deadlock_error(error: Exception) -> bool:
# DB specific error codes:
# Postgres: 40P01 (deadlock_detected)
# MySQL: 1213, 'Deadlock found when trying to get lock; try restarting
transaction'
- # psycopg2 exposes `pgcode`, psycopg3 exposes `sqlstate`
- db_err_code = (
- getattr(error.orig, "pgcode", None)
- or getattr(error.orig, "sqlstate", None)
- or (error.orig.args[0] if error.orig.args else None)
- )
- return db_err_code in ("40P01", 1213)
+ return any(code in ("40P01", 1213) for code in
_get_db_error_codes(error.orig))
diff --git
a/airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_backfills.py
b/airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_backfills.py
index 926d5271fbe..1796a5c0197 100644
---
a/airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_backfills.py
+++
b/airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_backfills.py
@@ -663,8 +663,27 @@ class TestCreateBackfill(TestBackfillEndpoint):
== "Dag has tasks for which depends_on_past=True. You must
set reprocess behavior to reprocess completed or reprocess failed."
)
- def test_create_backfill_database_locked(self, session, dag_maker,
test_client):
- """SQLite 'database is locked' during backfill creation returns HTTP
503."""
+ @pytest.mark.parametrize(
+ ("orig", "expected_detail"),
+ [
+ pytest.param(
+ sqlite3.OperationalError("database is locked"),
+ "Backfill creation could not acquire a database lock because
another transaction holds it. "
+ "Retry the request. SQLite does not support concurrent
writers; use PostgreSQL or MySQL.",
+ id="sqlite",
+ ),
+ pytest.param(
+ mock.Mock(
+ spec=["sqlstate", "args"], sqlstate="55P03", args=("could
not obtain lock on row",)
+ ),
+ "Backfill creation could not acquire a database lock because
another transaction holds it. "
+ "Retry the request.",
+ id="postgres",
+ ),
+ ],
+ )
+ def test_create_backfill_database_locked(self, session, dag_maker,
test_client, orig, expected_detail):
+ """A database lock error during backfill creation returns HTTP 503."""
with dag_maker(session=session, dag_id="TEST_DAG_1", schedule="0 * * *
*") as dag:
EmptyOperator(task_id="mytask")
session.scalars(select(DagModel)).all()
@@ -684,15 +703,12 @@ class TestCreateBackfill(TestBackfillEndpoint):
with mock.patch(
"airflow.api_fastapi.core_api.routes.public.backfills._create_backfill",
- side_effect=OperationalError(
- "statement", "params", sqlite3.OperationalError("database is
locked")
- ),
+ side_effect=OperationalError("statement", "params", orig),
):
response = test_client.post("/backfills", json=data)
- # OperationalError with "database is locked" should return 503
assert response.status_code == 503
- assert "database is locked" in response.json()["detail"].lower()
+ assert response.json()["detail"] == expected_detail
def test_create_backfill_cleans_up_orphan_on_lock_error(self, session,
dag_maker):
"""The partial Backfill row is removed when the cleanup runs after a
lock error."""
@@ -1176,7 +1192,10 @@ class TestCreateBackfillDryRun(TestBackfillEndpoint):
response = test_client.post("/backfills/dry_run", json=data)
assert response.status_code == 503
- assert "database is locked" in response.json()["detail"].lower()
+ assert response.json()["detail"] == (
+ "Backfill dry-run could not acquire a database lock because
another transaction holds it. "
+ "Retry the request. SQLite does not support concurrent writers;
use PostgreSQL or MySQL."
+ )
@pytest.mark.parametrize(
("reprocess_behavior", "expected_dates"),
diff --git a/airflow-core/tests/unit/utils/test_sqlalchemy.py
b/airflow-core/tests/unit/utils/test_sqlalchemy.py
index 6b1a9f3e64c..ee057399210 100644
--- a/airflow-core/tests/unit/utils/test_sqlalchemy.py
+++ b/airflow-core/tests/unit/utils/test_sqlalchemy.py
@@ -25,12 +25,13 @@ from uuid import UUID
import pytest
from kubernetes.client import Configuration, models as k8s
-from sqlalchemy import Column, MetaData, Table, text
+from sqlalchemy import Column, MetaData, Table, select, text, update
from sqlalchemy.dialects import mysql, postgresql, sqlite
-from sqlalchemy.exc import OperationalError, StatementError
+from sqlalchemy.exc import InternalError, OperationalError, StatementError
from sqlalchemy.schema import CreateTable
from airflow import settings
+from airflow.models.pool import Pool
from airflow.sdk import DAG
from airflow.sdk.timezone import utcnow
from airflow.serialization.enums import DagAttributeTypes, Encoding
@@ -44,7 +45,9 @@ from airflow.utils.sqlalchemy import (
ensure_pod_is_valid_after_unpickling,
get_dialect_name,
is_deadlock_error,
+ is_lock_not_available_error,
prohibit_commit,
+ with_db_lock_timeout,
with_row_locks,
)
from airflow.utils.state import State
@@ -52,6 +55,7 @@ from airflow.utils.types import DagRunTriggeredByType,
DagRunType
from tests_common.test_utils.config import conf_vars
from tests_common.test_utils.dag import sync_dag_to_db
+from tests_common.test_utils.db import clear_db_pools
pytestmark = pytest.mark.db_test
@@ -576,8 +580,79 @@ class TestIsDeadlockError:
orig = getattr(errors, error_name)("db error")
assert is_deadlock_error(self._make_error(orig)) is expected
+ def test_mysql_connector_deadlock(self):
+ # mysql-connector-python raises InternalError for 1213, with a generic
sqlstate next to the MySQL code.
+ orig = mock.Mock(
+ spec=["sqlstate", "args"],
+ sqlstate="40001",
+ args=(1213, "1213 (40001): Deadlock found when trying to get
lock", "40001"),
+ )
+ assert is_deadlock_error(InternalError("statement", {}, orig)) is True
+
def test_dbapi_error_without_orig_is_not_deadlock(self):
assert is_deadlock_error(OperationalError("statement", {}, None)) is
False
def test_non_dbapi_error_is_not_deadlock(self):
assert is_deadlock_error(RuntimeError("deadlock")) is False
+
+
+def _lock_default_pool_nowait(session):
+ Pool.slots_stats(lock_rows=True, session=session)
+
+
+def _update_default_pool_with_lock_timeout(session):
+ with with_db_lock_timeout(session, lock_timeout=1):
+ session.execute(update(Pool).where(Pool.pool ==
Pool.DEFAULT_POOL_NAME).values(slots=Pool.slots))
+
+
+class TestIsLockNotAvailableError:
+ @staticmethod
+ def _make_error(orig):
+ return OperationalError("statement", {}, orig)
+
+ @pytest.mark.parametrize(
+ "orig",
+ [
+ pytest.param(
+ mock.Mock(
+ spec=["sqlstate", "args"], sqlstate="55P03", args=("could
not obtain lock on row",)
+ ),
+ id="psycopg3",
+ ),
+ pytest.param(RuntimeError("55P03"), id="scheduler-advisory-lock"),
+ ],
+ )
+ def test_is_lock_not_available_error(self, orig):
+ assert is_lock_not_available_error(self._make_error(orig)) is True
+
+ @pytest.mark.parametrize(
+ ("error_name", "expected"),
+ [
+ pytest.param("LockNotAvailable", True,
id="psycopg3-lock-not-available"),
+ pytest.param("DeadlockDetected", False, id="psycopg3-deadlock"),
+ ],
+ )
+ def test_is_lock_not_available_error_psycopg3(self, error_name, expected):
+ errors = pytest.importorskip("psycopg.errors", reason="Test only runs
when psycopg v3 is installed.")
+ orig = getattr(errors, error_name)("db error")
+ assert is_lock_not_available_error(self._make_error(orig)) is expected
+
+ def test_error_without_orig_is_not_lock_error(self):
+ assert is_lock_not_available_error(OperationalError("statement", {},
None)) is False
+
+ @pytest.mark.backend("postgres", "mysql")
+ @pytest.mark.parametrize(
+ "lock_default_pool",
+ [
+ pytest.param(_lock_default_pool_nowait,
id="scheduler-pool-nowait"),
+ pytest.param(_update_default_pool_with_lock_timeout,
id="lock-timeout"),
+ ],
+ )
+ def test_lock_held_by_another_transaction(self, lock_default_pool):
+ clear_db_pools()
+ with Session.session_factory() as holder, Session.session_factory() as
waiter:
+ holder.execute(select(Pool.pool).where(Pool.pool ==
Pool.DEFAULT_POOL_NAME).with_for_update())
+ with pytest.raises(OperationalError) as ctx:
+ lock_default_pool(waiter)
+
+ assert is_lock_not_available_error(ctx.value) is True