SQLAlchemy StaleDataError: expected to update 1 row(s); 0 were matched (tested)

sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'users' expected to update 1 row(s); 0 were matched means the row your session loaded is gone or changed (deleted, primary key changed, or a newer version) by the time it flushed. SQLAlchemy refuses to pretend the UPDATE happened.

Blocks 1, 2 and 4 import this common.py (a SQLite file with one user):

import os
from sqlalchemy import create_engine, String, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
def fresh(path="/tmp/sd.db"):
    if os.path.exists(path): os.remove(path)
    eng = create_engine(f"sqlite:///{path}")
    Base.metadata.create_all(eng)
    with Session(eng) as s:
        s.add(User(id=1, name="Ada")); s.commit()
    return eng

1. The error: the row changed under you

from common import *
eng = fresh()
s1 = Session(eng)
u = s1.get(User, 1)             # loaded in session 1
with eng.begin() as c:          # someone else deletes the row
    c.execute(text("DELETE FROM users WHERE id = 1"))
u.name = "Grace"
try:
    s1.commit()
except Exception as e:
    print(f"{type(e).__module__}.{type(e).__name__}: {e}")

Output:

sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'users' expected to update 1 row(s); 0 were matched.

When the session flushes a change it runs UPDATE users ... WHERE users.id = ? and checks the row count. Here another connection deleted the row after session 1 loaded it, so the UPDATE matched 0 rows and SQLAlchemy raised StaleDataError instead of silently writing nothing. The same happens if something else changed the primary key, or a long-lived session holds an object whose row was removed by a bulk delete(), a cron job or another worker.

2. DELETE only warns

import warnings
from common import *
eng = fresh()
s1 = Session(eng)
u = s1.get(User, 1)
with eng.begin() as c:
    c.execute(text("DELETE FROM users WHERE id = 1"))
s1.delete(u)
with warnings.catch_warnings(record=True) as w:
    warnings.simplefilter("always")
    s1.commit()
    for x in w: print(f"{x.category.__name__}: {x.message}")
print("commit succeeded")
s1.close(); eng.dispose()

Output:

SAWarning: DELETE statement on table 'users' expected to delete 1 row(s); 0 were matched.  Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.
commit succeeded

Deleting an already-deleted row is a SAWarning, not an error, and the commit succeeds. Without a version column, only UPDATEs raise.

3. Optimistic locking with version_id_col: catch, reload, retry

from sqlalchemy import create_engine, String, Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase): pass
class Doc(Base):
    __tablename__ = "docs"
    id: Mapped[int] = mapped_column(primary_key=True)
    body: Mapped[str] = mapped_column(String(50))
    version: Mapped[int] = mapped_column(Integer, nullable=False)
    __mapper_args__ = {"version_id_col": version}
eng = create_engine("sqlite:////tmp/sd3.db")
Base.metadata.drop_all(eng); Base.metadata.create_all(eng)
with Session(eng) as s: s.add(Doc(id=1, body="v1")); s.commit()
a, b = Session(eng), Session(eng)
da, db = a.get(Doc, 1), b.get(Doc, 1)
print("both loaded version", da.version, db.version)
da.body = "edit by A"; a.commit()
print("A committed, version now", da.version)
db.body = "edit by B"
try:
    b.commit()
except Exception as e:
    print(f"{type(e).__name__}: {e}")
    b.rollback()
    db = b.get(Doc, 1)   # reload, then re-apply
    print("reloaded:", db.body, "version", db.version)
    db.body = "edit by B"; b.commit()
    print("retry ok, version", db.version)

Output:

both loaded version 1 1
A committed, version now 2
StaleDataError: UPDATE statement on table 'docs' expected to update 1 row(s); 0 were matched.
reloaded: edit by A version 2
retry ok, version 3

With version_id_col the UPDATE also says WHERE version = 1. Two sessions loaded version 1; A committed first (version 2), so B's UPDATE matched 0 rows. Here StaleDataError is the feature working: it stops B from overwriting A's edit. Roll back, reload the current row, re-apply or show the user the conflict, then commit again.

4. Check the row still exists before writing

from common import *
eng = fresh()
s1 = Session(eng)
u = s1.get(User, 1)
with eng.begin() as c:
    c.execute(text("DELETE FROM users WHERE id = 1"))
# check before writing: refresh() fails if the row is gone
try:
    s1.refresh(u)
except Exception as e:
    print(f"refresh: {type(e).__name__}: {e}")
s1.rollback()
print("s1.get after rollback:", s1.get(User, 1))

Output:

refresh: InvalidRequestError: Could not refresh instance '<User at 0x7faec4762350>'
s1.get after rollback: None

refresh() fails on a row that no longer exists, and after a rollback get() returns None. Short-lived sessions (one per request or job) make this rare: load, change and commit in one go instead of holding objects for minutes.

Fixes in order

• Find who else writes to the row (another worker, raw SQL, a bulk query.delete()/update(), a trigger) and why your session still has it.
• Use one short session per request or job, not a global session.
• If concurrent edits are expected, add version_id_col and handle StaleDataError with rollback + reload + retry.
• Do not "fix" it by catching and ignoring: your UPDATE did not happen.

Related: SQLAlchemy IntegrityError · DetachedInstanceError.

Written with AI assistance. Measured on 2026-10-23 with SQLAlchemy 2.1.6 and Python 3.14.7 (SQLite, python:3-alpine in Docker). Each block was run verbatim and the output pasted unedited. PlainHeaders is our extension; Python and SQLAlchemy are free.