SQLAlchemy sending a rollback after each commit

[ckv]

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? Will this affect my performance in the long run?

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.

Thanks

[Michael Bayer]

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.

[ckv]

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?

[Michael Bayer]

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