Sqlalchemy 查询返回不正确和过时的结果(对于 sqlite 引擎) [英] Sqlalchemy query returns incorrect and outdated results (for sqlite engine)

问题描述

我将 sqlalchemy 与 sqlite 引擎(开发服务器)一起使用，刚刚发现在更新查询之后，下一个 web 请求中的查询返回过时的数据集(这取决于哪个线程用于请求的事实，据我所知)是一个线程池).

I'm using sqlalchemy with sqlite engine (development server) and just discovered that after update query, queries in next web-requests return outdated data set (that depends on fact which thread is used for the request, as I understand there is a pool of threads).

我正在使用 scoped_session 和文档中的其他推荐内容 (DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())).

I'm using scoped_session and the other recommended stuff from docs (DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))).

这是网络请求的示例以及在那里执行的内容.

Here is the example of web requests and what's executed there.

request-1:thread-1: SELECT * FROM table WHERE id=1 -> (id:1, data:1)
request-2:thread-2: UPDATE table SET data=2 WHERE id=1; COMMIT
request-3:thread-1: SELECT * FROM table WHERE id=1 -> (id:1, data:1) // STILL data:1 !
request-4:thread-4: SELECT * FROM table WHERE id=1 -> (id:1, data:2) // NEW DATA!     
request-5:thread-1: SELECT * FROM table WHERE id=1 -> (id:1, data:1) // AND AGAIN OLD DATA!

这是什么?我怎样才能避免这种行为?在上面的示例中，所有 Web 请求都按连续顺序执行，因此 SQL 查询不相交.

What is this? How can I avoid this behaviour? In the example above all web requests are executed in consecutive order, so SQL-queries do not intersect.

推荐答案

您需要激活 pyramid_tm tween.

You need to activate the pyramid_tm tween.

[app:main]

pyramid.includes =
    pyramid_tm

补间在每次请求后提交事务，当新请求进入时隐式启动一个新事务.

The tween commits transactions after each request, implicitly starting a new transaction when a new request comes in.

不启动新事务时，旧事务将看不到其他事务(线程)提交的数据；这是数据库事务的固有特性，否则会导致不一致错误.

When you do not start a new transaction, the old transaction will not see data committed in other transactions (threads); this is a inherent feature of database transactions, as not doing so would lead to inconsistency errors.

这篇关于Sqlalchemy 查询返回不正确和过时的结果(对于 sqlite 引擎)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！