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 post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
