On Nov 13, 2012, at 12:28 PM, ckv wrote: > Thanks, I will stick to the default as it is not causing too much trouble. > > One more thing I have been noticing in the last couple of days is that a row > I'm querying in the database using filter_by() on three predicates and > limited by first() is being returned as None. I turned on mysql query logs > and then replayed the query printed for that exact attempt on my database and > the query returns one row fine. There is no apparent pattern to this > happening, as most of the times the query works fine and returns the result > object. > > Any idea why this would happen? It leads to some conditions of my daemon > failing and thereby freezing it. Any idea why this would happen?
there may be a MySQL bug. a row not being returned under some circumstances sounds familiar but you'd have to search through http://bugs.mysql.com/ to see what it might be. or maybe the row isn't actually there yet due to some concurrency situation on your end, you'd have to keep investigating. > > On Sunday, November 11, 2012 2:12:44 AM UTC+5:30, Michael Bayer wrote: > > On Nov 10, 2012, at 3:04 PM, ckv wrote: > > > I am using SQLAlchemy for a multi-threaded daemon. MySQL is my backend. I > > use it in declarative mode with scoped_session and auto_commit set to > > False. Each thread requests for a scoped_session when it is spawned inside > > of the thread and then closes it when it is supposed to die. > > > > I see in my mysql query logs that there is a commit and then it is > > immediately followed by a rollback. > > > > Is this normal? > > yes, the connection pool has the behavior that a connection will have > .rollback() called on it before returning it to the pool, in the event that > any lingering state is left on the connection. If your Sessions are > unconditionally calling commit() at the very end, and nothing else, then > technically this rollback() isn't necessary. However, if you were to close() > a Session, or otherwise lose references to it before commit() or rollback() > were called, this rollback() makes sure that the connection is definitely > rolled back before going back to the pool. > > > Will this affect my performance in the long run? > > maybe. There is an option to disable this behavior, and specifically it's > handy if you're using MySQL MyISAM tables, which don't support transactions > anyway. We have had users report that this rollback() causes a little bit > of latency on MySQL, which is unfortunate since a transaction that is > essentially stateless shouldn't be having this issue. > > > > > I have been perf testing the daemon locally before pushing it to prod and > > wanted to get some pointers as to why this is happening. > > you can turn it off, or even have it do a commit() instead, using the > "reset_on_return" flag described at > "http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html";. But if you set it > to None you really have to make sure your Sessions are closed cleanly, if > you're using InnoDB. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/o812RI9YjPUJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- 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. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
