Re: Record locking in deadlock

I no longer believe that I have gotten into a deadlock situation, but
rather, it appears that somehow a portion of my table somehow gets locked,
and stays locked, even after whatever locked it is done. I have added code
to my application to track what every connection is doing. In the two cases
I have been able to analyze, when the problem first occurs, all connections
are hung trying to do single row updates, except for one, which is reading.
A multi-row update eventually joins the throng of blocked updates, but it
appears that the first calls that end up blocking invariably are single-row
updates.

Incidentally, these single-row updates occur within a transaction. Nothing
else happens in the transaction, so it should be irrelevant, but I thought
I'd mention it anyway.

Any idea how this could happen or how to track it down? Is there any way to
look at what records are locked, and what caused them to be locked?

Thanks in advance again.

Robert

----- Original Message -----
From: Robert Wille a2om6sy02-at-sneakemail.com |postgres users' group|
To: robertw@willeweb.com
Sent: Wednesday, July 02, 2003 1:43 PM
Subject: [GENERAL] Record locking in deadlock

I have an application that uses Postgres 7.2.3. It appears to deadlock from time to time. I'm not absolutely sure that it deadlocks, but I'm pretty sure because all of a sudden all my updates block, and my postgres processes are all idle.

The strange thing is that my application never concurrently modifies any row (although it does modify multiple rows in the same table within a given transaction), so I don't know how this could happen, unless perhaps locking occurs on pages rather than rows. Can someone explain to me how/why my application could be deadlocking?

Thanks in advance.

Robert Wille