Well... I'm afraid it's not as simple as that. I'll give an example: I have a webservice A, which triggers a callback and calls webservice B, creating a new row in the database with status = 0 and commiting the transaction.
Then I have a script which finds all rows with status = 0, and sends their id, one by one, to a worker, who is supposed to get lots of data from many sources and then send that to another webservice C. Now, sometimes, especially when things happen too fast, the query the worker does for the row with that id returns empty, even though that isn't in an uncommited transaction, and the script who called the worker itself found it. In principle, if things are running smoothly, that isn't supposed to happen. Get the problem? The worker doesn't have uncommitted changes, actually it never does any changes at all. It got the id from a script who got the row, so it exists for someone who just started a new session. So, how can I be sure the worker will see that new row? I'm doing a commit with the empty transaction the worker has, as soon as it's called, and it seems to be working, but is there any better way? On Fri, Jan 25, 2013 at 7:42 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Jan 25, 2013, at 4:33 PM, Pedro Werneck wrote: > > > I'm having a problem with many concurrent scripts, workers and uwsgi > instances writing and reading the same tables and rows almost > simultaneously, and sometimes one of them seems to get an older state, even > from an object it never touched in the first place and I'm querying for the > first time. I find that weird, but I assume it has to do with the database > isolation level. > > sure, if the updates to that row are still pending in an uncommitted > transaction, the outside world would still see the old data. > > > > > > The problem is, how to adequately deal with that and make sure it never > happens? I added a session.commit() before doing anything and it works, I > assume rollback would work too. Is there any better solution? > > You should be committing *after* you've done some work. Then when a new > request comes in, it should start out with a brand new Session which will > make a new database connection as soon as the database is accessed. When > the request completes, the Session should be closed out. The > documentation at > http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questionsdiscusses > this, and continues the discussion at > http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- --- Pedro Werneck -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
