Re: locking question - why is this not a deadlock?

On Tue, Jun 07, 2005 at 10:26:00AM -0400, peter royal wrote:
> i am using PostgreSQL 7.4.1 (i am aware of my need to upgrade :)
>
> i have a situation where i *believe* postgresql should be detecting a
> deadlock, but it is not. in the locks view you see below, all of the
> processes waiting on the 'numberfactory' table are blocked. (which
> smells like a deadlock to me). i killed pid 6829, and then i
> immediately saw the log entries that are at the bottom of this
> message regarding deadlocks amongst the remaining locks. pid 6829 was
> in that state of locks for close to an hour.
>
> * is this a bug?
> * if so, is this a known bug? fixed in the latest 7.4 release or
> would i have to migrate to 8.0?

I don't think it's a bug, nor a deadlock situation. The problem is the
"idle in transaction" server process, which holds some lock but isn't
doing anything useful with it. Probably work would continue if the
transaction was closed.

This view is incomplete anyway, because you left out the xid column from
the pg_locks view, which point out exactly to the locks that are
blocking the other processes.

I believe this problem wouldn't ocurr (or would be resolved in a different
manner) in 8.1. I'm too lazy to replicate your scenario to check though ...

> (I apologize for the wrapping, its also at http://pace2020.com/
> ~proyal/locks.html)

The <IDLE> tags display funny there, but it's definitely easier to read.

> deadlocks detected after i killed pid 6829:
>
> Jun 7 09:39:09 epace postgres[6857]: [4-1] ERROR: deadlock detected
> Jun 7 09:39:09 epace postgres[6857]: [4-2] DETAIL: Process 6857
> waits for ShareLock on transaction 121712126; blocked by process 5234.

Hmm, I guess this could be detected if the deadlock detection code was a
lot more complicated than it already is, but probably it won't matter on
8.1.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)