CODEWITHSUNDEEP
pythonsqlitesqlalchemypyramid
Sergey Stolyarov
Sergey Stolyarov

Reputation: 2657

Sqlalchemy query returns incorrect and outdated results (for sqlite engine)

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).

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!

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.

Upvotes: 0

Views: 3441

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121884

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.

Upvotes: 0

Bleeding Fingers
Bleeding Fingers

Reputation: 7129

You could issue a Session.refresh with the database object you want to get the value of.

Upvotes: 1

Related Questions