Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: Record locking in deadlock

Re: Record locking in deadlock

From
"Robert Wille"
Date:
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 -----
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
 

Re: Record locking in deadlock

From
Stephan Szabo
Date:
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.

Re: Record locking in deadlock

From
Tom Lane
Date:
"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