Re: Deferred constraints and locks...

Jan Wieck wrote:

> On 2/12/2008 3:04 PM, Tom Lane wrote:
>
>> Nathan Wilhelmi <wilhelmi@ucar.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