How to diagnose a transaction hang problem?
一首诗
code is like this:
def update_user(user_id, sess):
user = sess.query(User).get(user_id).one()
user.last_activity_time = datetime.now()
session.commit() <------------ It hangs here forever.
In the code above, sess is a scoped session.
I don't have any clue of what happened. In most case the code above
worked. But suddenly it hangs and any other thread that want to talk
to database after that line is hit are also hanged. I have to kill
the process.
It does not look like that this a problem of database since after I
restart my application, it works again.
What might cause this kind of problem?
Alex Brasetvik
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
Gunnlaugur Briem
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.
一首诗
(actually the same line of data in database).
But I didn't expect that might cause problem before!
Oh, if that's true, then I have to add some lock in my code to avoid
that. That's a big problem.
Gunnlaugur Briem
takes locks on behalf of your code (in operations initiated by your
code) as necessary to ensure transactional consistency. That's one of
the benefits of an RDBMS.
What you do have to do is to make sure that those database locks don't
stay around forever (blocking other transactions). That happens when
you forget to complete (commit or rollback/close) the transaction that
creates them. If it eventually completes (either way), then things
will work fine for the most part --- and at least not hang.
(There is a lot more to this; you should read up on transactions and
locks, it's fascinating stuff. But your immediate problem is very
likely just a session/connection that you forget to close.)
Regards,
- Gulli
一首诗
That means I have check the transaction **before** the hanged commit,
maybe I forgot to commit or close a session at that time.
一首诗
I tried pg_top and found locks been hold by "idle transaction", and
then I found that I forgot to close a session!