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

Reply via email to