Re: Non returning Transactions/Many Locks in Postgres 9.0.4 and 9.0.1

On Mon, Jun 6, 2011 at 10:20 AM, Tarabas <tarabas(at)tarabas(dot)de> wrote:
> Hello!
>
> I am currently having a recurring Locking problem on my Postgres 9.0.4
> Database. I had the same Problem on 9.0.1 and updated to 9.0.4 then.
> It worked fine for a while and just resurfaced.
>
> Suddenly it seems as though there is some kind of "deadlock" in the
> database, which prevents my client from getting results for the open
> transactions, resulting in the system to stop delivering content.
>
> Something seems to trigger a lot of simultaneous locks and i cannot
> identity the problem at the moment.
>
> I also attached my pg_locks. It contains a lot of locks on my mainly
> used table with "AccessShareLock". Is there any way to get more
> information on the Locks and transactions that are currently being
> processed?
>
> Restarting my Application closes the open transactions, which are
> showing the status "in transaction" in my pg-admin and solves the
> problem for a while ... until it returns and I have to once again
> restart due to the locks.
>
> Can anyone point me in the right direction how i can find out more and
> close in on the Problem?
>
> Best regards
> Manuel
>
> Here's my pg_locks at the time of occurrence:

Snip. Those are ALL either AccessShareLock (which is very low level
and non-blocking) or virtual tx locks, which again don't block
anything but their own transaction. Nothing there screams "locks!"
for a better view of locks and how they're blocking things you can use
the queries from here: http://wiki.postgresql.org/wiki/Lock_Monitoring