On Jan 25, 2013, at 5:12 PM, Pedro Werneck wrote: > 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.
Is there some kind of distribution to the database, like master/slave? Otherwise, once data is committed, it is readable to all new transactions subsequent to that commit. If the script that is searching for status=0 is finding rows that are committed, then the worker that is querying for those rows should be able to see them, unless the worker has been holding open a long running transaction. Long running transactions here are more of the antipattern. The worker should ensure it responds to new messages from the status=0 script with a brand new transaction to read the message in question. > > 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? The worker should wait for a request from the script in a non-transactional state, without a Session. A request from the script comes in- the worker starts a new Session to respond to that request, hence new transaction. Thinking about transaction demarcation in reverse still seems to suggest that this worker is leaving a dormant connection open as it waits for new jobs. All of that said, this is only based on what you're telling me so far. There may be many more details here that entirely change how this might have to work. -- 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.
