From: | Csaba Nagy <nagy(at)ecircle-ag(dot)com> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to debug a locked backend ? |
Date: | 2005-11-19 13:14:40 |
Message-ID: | 1132406080.10890.523.camel@coppola.muc.ecircle.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
[snip]
> So what is the UPDATE doing? What is the query (see pg_stat_activity)
> doing? Is it updating a lot of rows? If the query does run for a long
> time holding any kind of lock, you're going to get strange effects like
> this.
The UPDATE is processing ~ 100 rows in a transaction (I'm not sure,
depends on how the application is set up, and it is a parameter in the
query so pg_stat or postgres logs will not show that). The rows are
previously locked by a SELECT ... FOR UPDATE. I can't give you the
actual queries, and I don't have time now to set up a similar case. I
plan to create a test case which does similar processing and let it run
on a test server maybe it can produce the lock.
In any case, I debugged through the process, and there's no exclusive
lock placed by it on any table (checked pg_locks after each executed
step). This is a straightforward processing, without if-branches, so I'm
100% sure it's always executing the same queries. So I'm puzzled by what
could have caused the ExclusiveLock I observed (based on the pg_locks
view during the lockup) on the updated table. You can see it in the
attachment to my post (look for "execute locks(5239)"):
http://archives.postgresql.org/pgsql-general/2005-11/msg00828.php
I have enough information now to know how to get a stack trace, so I'm
prepared for the next time... though I'd prefer there wouldn't be one
;-)
Thanks,
Csaba.
From | Date | Subject | |
---|---|---|---|
Next Message | David Roussel | 2005-11-19 15:36:59 | Re: shorter way to get new value of serial? |
Previous Message | Peter Eisentraut | 2005-11-19 11:46:21 | Re: permission issue |