Thanks a lot! I tried pg_top and found locks been hold by "idle transaction", and then I found that I forgot to close a session!
On Jan 30, 9:43 am, Gunnlaugur Briem <gunnlau...@gmail.com> wrote: > 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.
