On Jan 25, 2013, at 8:02 PM, Pedro Werneck wrote: > That works, but now I'll have to change how my models use the session.
hmm, is that because your model objects themselves are controlling the scope of the transaction ? That's another pattern I don't really recommend... > > Would this all be solved if I just use READ COMMITTED transaction isolation? maybe? If the problem is really just exactly those rows needing to be visible. But the long running "dormant" transaction thing is still kind of an antipattern that will generally have negative effects. > > > 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. > > -- 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.
