On Wed, Oct 24, 2018 at 7:15 PM Jonathan Vanasco <[email protected]> wrote:
>
> A new suite of unittests shed light on what appears to be anti-pattern in an
> application. I'm not sure how to address this one.
>
>
> The general flow of a particular web request is this:
>
>
> Phase 1- load some database objects for general verification
>
> Phase 2- loop through a handful of routines to create some items. each
> routine is an isolated transaction - not a subtransaction
>
>
> so it looks like this...
>
>
> # Phase1
> foo = dbSession.query(FOO).all()
>
> # Phase2
> for i in (a, b, c):
> bar = BAR()
> dbSession.add(bar)
> dbSession.commit()
>
>
>
>
> The problem occurs sporadically in Phase2, when the code attempts to address
> a lazyloaded attribute of the `foo` loaded in Phase1, and I get the error
> exc.ResourceClosedError("This Connection is closed")
that's not normal unless you are holding a Connection around and
calling close() on it, have a stack trace ? or is this inside an
event handler?
>
> Digging into the events API and tracking everything, it appears my connection
> is returned to the pool and closed on every `commit`.
session.commit() you mean, yes this is normal if the Session is bound
just to an engine. if you bind the Session to a specific Connection
then it will keep using the same connection.
>
> I'm roughly seeing the events happen like this (the order of where the
> 'action' happens may be shifted up or down a line):
>
> Phase 1
> connect
> checkin
> engine_connect
>
> Phase 2 Action
> commit
> reset
> checkout
> checkin
> engine_connect
>
> Phase 2 Action
> commit
> reset
> checkout
> checkin
> engine_connect
That seems strange ? the order should be, checkout, engine_connect,
commit, reset, checkin,
here's a demo:
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import event
import mock
e = create_engine("sqlite://")
m1 = mock.Mock()
event.listen(e, "commit", m1.commit)
event.listen(e, "reset", m1.reset)
event.listen(e, "checkout", m1.checkout)
event.listen(e, "checkin", m1.checkin)
event.listen(e, "engine_connect", m1.engine_connect)
s = Session(e)
s.execute("select 1")
s.commit()
s.execute("select 1")
s.commit()
for c in m1.mock_calls:
print(c[0])
output:
checkout
engine_connect
commit
reset
checkin
checkout
engine_connect
commit
reset
checkin
>
> Phase 2 Action
> EXCEPTION
>
I don't see any cause for that EXCEPTION. the Session keeps on
connecting as needed.
> Is there a way to ensure the session doesn't close/checkin the connection on
> certain commits
bind the Session to a Connection, but all the other things you're
observing don't make any sense
> or is there a better strategy to deal with this anti-pattern -- perhaps
> figuring out a way to update the object sessions/connections ?
update....what exactly?
>
> I only have a handful of situations where there are multiple transactions
> like this - maybe 1% of the potential views.
>
> It does look like I have two problems here too:
>
> 1. The symptom/problem of not being able to load this data from a previous
> transaction.
> 2. The underlying problem of losing a connection in a request, when I know I
> want to keep it for immediate use again.
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
