On 02/13/2015 11:30 PM, Michael Bayer wrote:
Ed Rahn <edsr...@gmail.com> wrote:
I have several programs that are Multi Process and long running, they open
up 30 or so connections and do selects periodically. For a select query,
the default behaviour is to begin a transaction if not currently in one,
but not commit afterwards. This leaves a large number of postgresql
processes with a status of "idle in transaction". Which means they “lock”
tables, so you can not drop them or alter them including add and drop
indexes. I have also seen some problems were connections do not get closed
if the connecting process exits, although I haven’t verified this is the
cause.
Is this a problem others have had in the past or am I just being overly
worried?
yeah that’s kind of bad. you want the connections to be “idle”, but
definitely not “idle in transaction”. that will cause problems.
If it is a problem is there any other way to fix it beside commit()’ing
after each select query? I tried add an event handler after queries get
run, but I didn't see a way to get to the current transaction.
Well at least on the connection itself, if its used in non-autocommit mode
(by which I refer to psycopg2’s autocommit flag), the “idle in transaction”
will remain until either commit() or rollback() is called on that
connection.
So if you have a Session, and want to stay at that level, your options are
to commit() it, to rollback() it, to close() it which returns the connection
to the pool which does a connection-level rollback, or you could use the
Session in autocommit=True mode, which means after each query it returns the
connection to the pool for the same effect.
I'd really like to not use autocommit mode. There are parts of the code
that I need to maintain DB consistency with transactions.
And I need to keep the objects attached to a session so automatically
closing it isn't an option.
So I guess my only option is to commit after each select, which seems
like a lot of work as the code base is fairly large.
This seems like a fairly common use case, do people just not care about
it or how do they handle it?
If you want to turn off transactions completely with the DBAPI connection
itself, even though this overhead is very minimal for Postgresql you could
set it to isolation level of AUTOCOMMIT which for psycopg2 sets the
“autocommit” flag on the connection. The commit()/ rollback() calls from
SQLAlchemy would have no effect.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
