Re: Record locking in deadlock

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.