how to avoid deadlocks?

From: Gabriele Messineo <gabriele(dot)messineo(at)winext(dot)eu>
To: pgsql-novice(at)postgresql(dot)org
Subject: how to avoid deadlocks?
Date: 2008-11-06 08:38:10
Message-ID: 200811060938.10653.gabriele.messineo@winext.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,
I would like to ask you an advice because I have a database that seems to
deadlock on Exclusive locks every couple of days.

The issue comes at night when I reindex some tables, it seems unable to
perform the task (a reindex on a table of 75 records for 2 relpages after
several hours is still running), and next queries and next analyzes are
locked (and becomes everytime worst).

On that table there are lots of joins and selections (also quite heavy
queries), but I don't think it's the load that cause the issue (it doesn't
happen on heavier installations).

However I would expect a serialization of them that would slowly process all
the queries in the queue. Why is the DBMS instead blocking?
Am I doing something wrong? Given the load on the DBMS have I to stop querying
before reindexing the tables?

PS: I'm using debian package postgresql-8.3 = 8.3.4-2 . If it can be useful I
collected some data from pg_stat_activity and pg_class.

Thanks and regards,

Gabriele Messineo

Browse pgsql-novice by date

  From Date Subject
Next Message Leví Teodoro da Silva 2008-11-06 11:50:35 [DUMP] Make a dump with special characters
Previous Message Tim Bowden 2008-11-05 17:51:08 Re: basic security