When does flask sqlalchemy.orm.exc.StaleDataError occur and how to fix it?-CodePudding

error log:

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

code:

#app.py
app = Flask(__name__)
db = SQLAlchemy()


# work.py
import concurrent.futures
import signal
import threading
import time

from app import app, db

class Worker():

    def __init__(self, app) -> None:
        self._lock = threading.Lock()
        self._app = app


    def do_work(self):
        with self._app.app_context():
            with self._lock:
                record = MyTable1.query.filter_by(status='pending').first()
            if br is None:
                time.sleep(10)
                return
            record.status = 'running'
            db.session.add(record)
            db.session.commit()
            '''
            ...do something
            '''
            # ******************************
            # error is occur in this query
            # sqlalchemy.orm.exc.StaleDataError: UPDATE statement on \
            # table 'my_table1' expected to update 1 row(s); 0 were matched.
            # ******************************
            config = MyTable2.query.filter_by(name='xxx').first()
    

    def run(self):
        work_exec = concurrent.futures.ThreadPoolExecutor(
            max_workers=4)

        for i in range(4):
            work_exec.submit(self.do_work)
        signal.pause()

In the function do_work, when I query Table2, the error sqlalchemy.orm.exc.StaleDataError occurs, what makes me wonder is that the log of the error is Table1.
During this period, I didn't modify the record again. Why didn't an error occur during the first session.add?

Sorry I can't provide a small complete example as this error occurs less frequently and I can't reproduce it manually.

CodePudding user response：

From this website: https://prog.world/error-in-sqlalchemy-sqlalchemy-orm-exc-staledataerror/

Occurs when intersecting db.relationship… When relationships are specified in each other’s models.

CodePudding user response：

If you only want to update the table, delete db.session.add(record)

Example

record = MyTable1.query.filter_by(status='pending').first()
record.status = 'running'
db.session.commit()