Re: Deferred constraints and locks...

From: Nathan Wilhelmi <wilhelmi(at)ucar(dot)edu>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: Deferred constraints and locks...
Date: 2008-02-14 18:33:44
Message-ID: 47B48988.2050606@ucar.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Jan Wieck wrote:

> On 2/12/2008 3:04 PM, Tom Lane wrote:
>
>> Nathan Wilhelmi <wilhelmi(at)ucar(dot)edu> writes:
>>
>>> Hello - Trying to track down a lock contention problem, I have a
>>> process that does a series of select / insert operations. At some
>>> point the process grabs a series of RowExclusiveLock(s) and has the
>>> obvious effect of stalling other processes. I logged all the
>>> statements and don't see any for update or explicit lock statements.
>>
>>
>> Insert statements would naturally take RowExclusiveLock, but that
>> doesn't block other DML operations. So the question is what *else*
>> are you doing?
>
>
> Those SELECT statements aren't by chance FOR UPDATE, are they?
>
>
> Jan
>
Not that I can see, at least from the PG statement logs. So as the
process runs (it a long running parsing process) it does a number of
selects/inserts/updates. A pattern I am seeing is that a row is inserted
and then later updated as more information is known during the parsing
process. I don't see any explicit lock or for updates statements being
sent. I can't get a breakpoint low enough in the app yet, but it looks
like some of the subsequent update statements are pulling
RowExclusiveLock lock(s), maybe inserts as well. This is where I am a
bit confused, from Tom's response these are table level locks. From my
understanding of MVCC, I didn't think this should happen. I would expect
that other transactions wouldn't see updated values which is what I am
after, but the problem is that reads are blocking until this long
transaction completes. What am I missing here.... I can see this both on
8.2 and latest 8.3.

Thanks!

-Nate

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2008-02-14 18:35:15 Re: Maximum realistic number of database user accounts?
Previous Message Shane Ambler 2008-02-14 18:24:33 Re: How to cope with low disk space