I have figured out the problem. When I do a BEGIN; and then a SELECT an
AccessShareLock is obtained on the table, and then not released until the
transaction is over. Then the vacuum comes in and tries to acquire an
exclusive lock, and in the process blocks any readers who are trying to
SELECT on the same table. My app reads from both db connections in the
same thread, thus the deadlock occurs.
So why isn't the AccessShareLock dropped as soon as the SELECT is over?
On Tue, 12 Oct 2004, Tom Lane wrote:
> Joseph Shraibman <jks@selectacast.net> writes:
> > Last night one of these vacuum fulls deadlocked with a query on this
> > table. Both were stuck doing nothing until I did a kill -INT on the
> > backends doing the vacuum.
>
> > So my questions:
> > 1) What can I do to avoid this?
> > 2) What do I do next time this happens to get more debugging info out of
> > the situation?
>
> Look in pg_locks and pg_stat_activity.
>
> I think it is highly unlikely that there was a deadlock inside the
> database. Far more likely that both jobs were waiting on some
> idle-in-transaction client whose transaction was holding a lock
> on the table.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>