Обсуждение: Re: Record locking in deadlock

----- 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

 

On Thu, 3 Jul 2003, Robert Wille wrote:

> 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?

I'd wonder if you're possibly running into concurrency problems from
foreign keys.  Is it possible that you're ending up locking the same row
in a primary key table somewhere on the constraint checks?  That'd still
leave you with needing to find which transaction is holding the lock you
want.

"Robert Wille" <a2om6sy02@sneakemail.com> writes:
> 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?

Update to PG 7.3, and see the pg_locks view.  There is no very useful
way to see the locking state in earlier releases.

            regards, tom lane