Guaranteeing same connection for scoped session

[Kent]

In cases where we interact with the database session (a particular Connection) to, for example, obtain an application lock which is checked out from database for the lifetime of the database session (not just the duration of a transaction), it is important that I guarantee future scoped session instances get the same connection (and, for example, the pool_recycle or something else has thrown out that connection and grabbed a new one).

Please advise me where I can best implement this guarantee.  A Session subclass's connection() method seems it might be the appropriate place, but let me know if there is a better recipe.

The Session.connection() method's docs say:
"If this Session is configured with autocommit=False, either the Connection corresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the Connection returned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted)."

If the session is one registered in my scoped registry, I'd like to always return the same connection to guarantee I am using the one with the database-side checked-out application lock.

What's my best option?

Thanks much!

[Michael Bayer]

Kent <jkent...@gmail.com> wrote:

> In cases where we interact with the database session (a particular Connection) to, for example, obtain an application lock which is checked out from database for the lifetime of the database session (not just the duration of a transaction), it is important that I guarantee future scoped session instances get the same connection (and, for example, the pool_recycle or something else has thrown out that connection and grabbed a new one).
>
> Please advise me where I can best implement this guarantee. A Session subclass's connection() method seems it might be the appropriate place, but let me know if there is a better recipe.

you’d want to create that Session associated with the Connection directly:

my_session = scoped_session(bind=some_connection)

then of course make sure you .close() it and .close() the connection at the end of the use of that session.

>
> The Session.connection() method's docs say:
> "If this Session is configured with autocommit=False, either the Connection corresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the Connection returned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted)."
>
> If the session is one registered in my scoped registry, I'd like to always return the same connection to guarantee I am using the one with the database-side checked-out application lock.
>
> What's my best option?
>
> Thanks much!
>

> --
> 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 sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Kent]

Thanks very much Mike.

[Kent Bower]

About a year ago you helped me ensure my scoped session gets the same connection to the database, which might be important.  

I found out using "bind=connection" doesn't guarantee the session_maker uses that connection if something went wrong with the session and ScopedSession.remove() was called. Is there a way to guarantee this?

See attached script that fails on version 1.0.12

Is this the intended behavior when sessionmaker has a specific connection as bind?

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/WcdRsvBTozk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

Well scopedsession.remove throws away the session, so yeah either don't call that , or set up the connection immediately on the next session.

Visit this group at https://groups.google.com/group/sqlalchemy.

[Kent Bower]

Will the connection.info dict always be new if a new underlying raw connection has been grabbed? (Such that I can reliably detect this situation?)

[Jonathan Vanasco]

On Wednesday, April 13, 2016 at 7:25:16 PM UTC-4, Mike Bayer wrote:

Well scopedsession.remove throws away the session, so yeah either don't call that , or set up the connection immediately on the next session. 

I thought "this is obvious, the session is closed on `remove`", but then dug into the docs -- and I can see how this is misleading.

The narrative docs (http://docs.sqlalchemy.org/en/latest/orm/session_api.html#session-and-sessionmaker)

Session = sessionmaker()

# bind an individual session to a connection
sess = Session(bind=connection

It's easy to miss the importance of "individual"

And then the API makes it seem like a bind(connection) would persist via sessionmaker. 

http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.sessionmaker.__init__

• bind – a Engine or other Connectable with which newly createdSession objects will be associated.

http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session

• bind – An optional Engine or Connection to which this Session should be bound. When specified, all SQL operations performed by this session will execute via this connectable.

Unless one were more familiar, the `remove` behavior wouldn't be apparent... and the notion of a particular connection being bound to a Session Maker might seem like a good thing (it's actually not, because you would inherently preclude the utility of connection pools , aside from  other effects)

[Kent Bower]

Yeah, it seems to me that if you pass a specific connection to a sessionmaker for some (whatever) reason, that sessionmaker shouldn't ever silently take a different one.

I'll need to work on detecting or sabotaging new connections from a sessionmaker which was passed a specific connection.  (I know the obvious question might be: why even use a sessionmaker/scoped session? and the answer is that those parts are all well buried in the framework, along with scopedsession.remove() call, so under certain circumstances only, I want the session to be guaranteed only one.)  

Any help on how to sabotage a sessionmaker/scoped_session to prevent later creation of a new connection would be appreciated.  Thanks!

--

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/WcdRsvBTozk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.