Re: apparent deadlock

On Tue, Nov 18, 2008 at 11:43 AM, Michael P. Soulier
<michael_soulier(at)mitel(dot)com> wrote:
> Hello,
>
> I'm working with Pg 7.4 on CentOS, and I'm seeing an apparent deadlock which
> is not resolving itself.
>
> According to the postgres documentation, deadlocks should be detected and
> resolved by killing one or more transactions involved.
>
> http://www.postgresql.org/docs/current/static/explicit-locking.html
>
> What I'm seeing is an upgrade of an application running a series of alter
> table commands to migrate the db schema, but blocking during the process and
> never continuing. I finally had to restart the db to unblock the process.

Just because you've got a blocking transaction doesn't mean it's a
deadlock. A deadlock means you have two transactions, each of which
is waiting for the other to continue before it can go on. If one
query is holding up another query, but not vice versa, it's not a
deadlock, just a lock.

> Does anyone have any suggestions for troubleshooting a potential deadlock like
> this? I'm not sure how to query existing locks on tables, for example, which
> might tell me where the issue is.

First place to start is the view pg_locks