That works, but now I'll have to change how my models use the session. Would this all be solved if I just use READ COMMITTED transaction isolation?
On Fri, Jan 25, 2013 at 8:45 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Jan 25, 2013, at 5:35 PM, Pedro Werneck wrote: > > 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. >> >> Exactly. > > >> 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. >> >> > That's the point. What's the best way to do that, considering the worker > is never updating anything, only reading? Should I commit in the end of > every task then, even without anything to commit? Should I start a new > session on every call? The commit does that automatically if I'm not using > autocommit=True, right? > > > just do it like this: > > > def receive_some_request(args): > session = Session(some_engine) # "connect" to the database (in > reality, pulls a connection from a pool as soon as the Session is used to > emit SQL) > try: > .. do things with session ... > session.commit() # if you have data to commit > finally: > session.close() # close what was opened above. > > just like it were a plain database connection. that's per request > received by your worker. > > > > > > >> 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. >> > > I'm pretty sure it does. I'm using Flask SQLAlchemy and Celery for the > workers. The workers reach the global app for the session and are keeping > the connection open, but they do have work almost all the time and never > sleep for more than a few secs. > > > --- > Pedro Werneck > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@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. > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@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.
