Another quick way of troubleshooting hangs is the tool pg_top, in
which you might see a process in the state “Idle in transaction”. This
state means that some database operations have been performed in a
transaction on that connection but the transaction has not yet been
committed.
Those database operations will have been granted locks, for which your
stalled session is waiting. Behind this idle-in-transaction connection
might be another SQLAlchemy session that you neglected to commit or
close. That's a common way for this situation to come up.
You can see the locks held by the connection using pg_top (hit L), or
you can find them with pg_locks as Alex mentioned. These locks may
give you a clue as to where in your code that other session was
created, helping you track down the bug to correct.
To avoid creating cases like this, I try to be careful about session
objects: I never store them (keep them on the stack, i.e. as local
variables and function arguments), and I always create and close them
using a construct like this:
from contextlib import closing
with closing(Session()) as session:
do_stuff()
session.commit() if I want to
Note that sessions are not the same as DB connections (which are
pooled further down in the layers of stuff going on), you gain nothing
by storing and reusing them, and you risk creating cases like this.
Per the docs, “Sessions are very inexpensive to make, and don’t use
any resources whatsoever until they are first used...so create
some!” (and close and discard them happily).
- G.
On Jan 29, 2:13 pm, Alex Brasetvik <a...@brasetvik.com> wrote:
> On Jan 29, 2010, at 15:01 , 一首诗 wrote:
>
> > What might cause this kind of problem?
>
> Possibly waiting on locks. Do you have any concurrent transactions modifying
> the same data?
>
> When the problem appears, run `select * from pg_stat_activity` to see whether
> there are locking issues.
>
> To see the locks involved, run `select * from pg_locks`.
>
> --
> Alex Brasetvik
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
