[sqlalchemy] Usage of Pool.dispose() to close connections using SQLAlchemy 0.8

Post by Joe Biggert
We've got some legacy code using SQLAlchemy 0.8 (we're actively looking to
upgrade to the latest) and we've got a wrapper around our requests that
# work
Session.commit()
Session.rollback()
raise
Session.remove()
Session.bind.pool.dispose()

the dispose at the end is completely wrong, sorry.

Post by Joe Biggert
Now there's a couple flavors of that using local session but considering we
are using scoped_session, that probably doesn't make a big difference.
Please correct me if I'm wrong, but the workflow goes like this: we're doing
whatever work we have then committing the transaction... if an exception
occurs, we're rolling back that transaction... and lastly, we're doing a
remove() which rollbacks any underlying transaction and then releases the
connection(s) back to the connection pool per
http://docs.sqlalchemy.org/en/latest/orm/contextual.html#contextual-thread-local-sessions.
The last part is the one that I'm confused about whether it's intended
usage.

it is not at all. you never need to use dispose() unless you are in a
test suite that is testing connection pools and engines, or if you are
starting up a new process using either os.fork() or multiprocessing.

Reading the documentation on the dipose() method,

Post by Joe Biggert
http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.Pool.dispose,
it leads me to believe our usage of this method isn't needed... and if

This method leaves the possibility of checked-out connections remaining
open, as it only affects connections that are idle in the pool.

I believe the intent of the usage here is to explicitly close connections
after we're done with them because we've experienced some areas where
connections are left open and idle in transaction.

idle in transaction is not related to the pool, it's related to not
closing transactions and/or not returning connections to the pool
correctly. when a connection is returned to the pool, it calls
rollback() on that connection before making it available again, so
that there is no idle in transaction (assuming you have not modified
pool reset_on_return, which you should not have, if you did that, you
need to take that out). calling pool.dispose() cannot fix this issue
because dispose() only affects connections that were already returned
to the pool successfully and would therefore not be idle in
transaction.

Post by Joe Biggert
I've been searching
around and haven't found anywhere suggesting this function is what should be
used...

which is because...you almost never need to use it :)

Post by Joe Biggert
this link,
https://groups.google.com/forum/#!topic/sqlalchemy/09h4az61oXo, also
suggested not to use it for this purpose.
Can anyone clear this up for me? The only thing I can think of is that, in
0.8, something finicky may have been going on so it was recommended at some
time to us this?

at least make sure you are on the latest 0.8. the dispose() you are
calling has the effect of essentially not using pooling at all. If
the 0.8 pool is giving you problems, then just use NullPool in your
create_engine:

from sqlalchemy.pool import NullPool
e = create_engine("...", poolclass=NullPool)

with the above, you aren't pooling anything at all. if your
application is still leaving sessions or connections hanging around
outside of the pool, you still would get "idle in transaction", but if
your issue is due to a race condition in the pool itself that would
resolve it. I've reviewed the 0.8 and 0.9 changelogs and there are
several pool-related bugs that are fixed, however none of them I think
would affect the "reset on return" feature or cause connections to not
be checked in correctly.

Post by Joe Biggert
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Jonathan Vanasco

2018-01-16 19:59:59 UTC

Post by Mike Bayer
the dispose at the end is completely wrong, sorry.

...

Post by Mike Bayer
or if you are starting up a new process using either os.fork() or
multiprocessing.

Since this appears to be in a web context, I believe `.dispose()` is also
required if you're using a forked application server (e.g. uwsgi, mod_wsgi
in many contexts, etc).

if that is the case and your code is connecting to the database at all
before the fork, you should be able to define an "at fork" routine that
calls `dispose()` for whatever deployment framework you're using.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Joe Biggert

2018-01-16 22:09:21 UTC

Mr. Vanasco,

Can you point me to where I can read more about the cases where
`.dispose()` is required? You are correct, this is in a web context... and
we are using mod_wsgi.

Post by Mike Bayer
the dispose at the end is completely wrong, sorry.

...

Post by Mike Bayer
or if you are starting up a new process using either os.fork() or
multiprocessing.

Mike Bayer

2018-01-16 23:26:28 UTC

Post by Joe Biggert
Mr. Vanasco,
Can you point me to where I can read more about the cases where `.dispose()`
is required? You are correct, this is in a web context... and we are using
mod_wsgi.

use cases for dispose are documented at:

http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=multiprocessing#engine-disposal

a specific recipe for multiprocessing that works better than dispose() is at:

http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=multiprocessing#using-connection-pools-with-multiprocessing

dispose() is not needed inside of a web context. however, the kind
of web container you're using as well as if you are using something
like gevent or eventlet may be significant in why you are getting idle
transactions.

Post by Mike Bayer
the dispose at the end is completely wrong, sorry.

...

Post by Mike Bayer
or if you are starting up a new process using either os.fork() or
multiprocessing.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Joe Biggert

2018-01-17 02:11:35 UTC

Thank you Mr. Bayer! I appreciate the detailed responses!

Post by Joe Biggert
Mr. Vanasco,
Can you point me to where I can read more about the cases where

`.dispose()`

Post by Joe Biggert
is required? You are correct, this is in a web context... and we are

using

Post by Joe Biggert
mod_wsgi.

http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=multiprocessing#engine-disposal
http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=multiprocessing#using-connection-pools-with-multiprocessing
dispose() is not needed inside of a web context. however, the kind
of web container you're using as well as if you are using something
like gevent or eventlet may be significant in why you are getting idle
transactions.

we've got a wrapper around our requests that basically looks like
the dispose at the end is completely wrong, sorry.

...

or if you are starting up a new process using either os.fork() or
multiprocessing.

Since this appears to be in a web context, I believe `.dispose()` is

also

Post by Jonathan Vanasco
required if you're using a forked application server (e.g. uwsgi,

mod_wsgi

Post by Jonathan Vanasco
in many contexts, etc).
if that is the case and your code is connecting to the database at all
before the fork, you should be able to define an "at fork" routine that
calls `dispose()` for whatever deployment framework you're using.

Groups

Post by Joe Biggert
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send

an
<javascript:>.

Post by Joe Biggert
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Jonathan Vanasco

2018-01-17 05:21:43 UTC

On Tuesday, January 16, 2018 at 6:26:34 PM UTC-5, Mike Bayer wrote:

dispose() is not needed inside of a web context. however, the kind

Post by Mike Bayer
of web container you're using as well as if you are using something
like gevent or eventlet may be significant in why you are getting idle
transactions.

Mike- I thought `dispose()` was necessary with forking webservers if
connections are made *before* the process forked, because the servers
(typically) implement fork() behind the scenes. uwsgi offers a @postfork
decorator (and an explicit hook) that I use to call the dispose(), and
gunicorn has a `post_fork` hook. i don't recall the specifics of other
servers.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Mike Bayer

2018-01-17 14:40:16 UTC

On Wed, Jan 17, 2018 at 12:21 AM, Jonathan Vanasco

Post by Mike Bayer
dispose() is not needed inside of a web context. however, the kind
of web container you're using as well as if you are using something
like gevent or eventlet may be significant in why you are getting idle
transactions.

Mike- I thought `dispose()` was necessary with forking webservers if
connections are made *before* the process forked, because the servers
decorator (and an explicit hook) that I use to call the dispose(), and
gunicorn has a `post_fork` hook. i don't recall the specifics of other
servers.

if you create your engine in your parent process, and then let that
engine travel into a fork(), then yes, you need to call dispose() just
once when the fork starts, so that you get new connections in your
forked process. I mentioned that above. however, there's a neater
way that we do this in openstack which I also refer to above in the
pooling docs, that is to use an event to track which process a
particular connection was created within, and invalidate it if the two
don't match. yet another thing that could be just added to the pool
:) create_engine(..., protect_across_forks=True)

Post by Jonathan Vanasco
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Jonathan Vanasco

2018-01-17 17:12:44 UTC