Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: LevĂ­ Teodoro da SilvaDate: 2008-11-06 11:50:35
Subject: [DUMP] Make a dump with special characters
Previous:From: Tim BowdenDate: 2008-11-05 17:51:08
Subject: Re: basic security

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group