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.