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屋!