I knew I'd find a possible solution right after asking... Isn't that
always the way?
I found that I can do DBSession.execute("COMMIT") to get MySQL to commit
the temporary table but SQLAlchemy/transaction doesn't seem to pick up on
it and I don't lose the connection to the threadpool.
This seems kind of a hack, but does anyone have a better solution?
On Fri, Aug 23, 2013 at 4:12 PM, Tim Tisdall <[email protected]> wrote:
> I'll try to make this succinct...
>
> I'm creating a temporary table and then doing a query with it that takes a
> good length of time. I found that the source tables going into the
> temporary table were being locked after the temporary table was created
> even though they were no longer needed for the second much longer query.
> Apparently "create temporary table" in MySQL doesn't autocommit and so the
> transaction is locking a lot more than needed. So... I tried committing
> right after creating the temporary table, but now I randomly lose the
> temporary table because SQLAlchemy sees the commit as a reason to return
> the connection back to the connection pool and then get back another
> connection on the next query. So, as the temporary table is persistent
> only on that connection I usually lose the table as I usually don't get the
> very same connection back from the pool.
>
> I'd like to be able to tell MySQL to commit after creating the temporary
> table so I can drop the locks used to fill that table, but I want to make
> sure SQLAlchemy doesn't let go of the connection and return it to the pool.
>
> --
> 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/9dfigjQt1Bw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
