Обсуждение: Deferred constraints and locks...

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. So I am guesses this is more
of an internal design issue. All the FKEY constraints are deferrable and
initially deferred, could this be causing pg to be pulling locks on rows
that are part of the fkey? Something else I should be looking for?

Thanks!
-Nate

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?

            regards, tom lane

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?
>
>            regards, tom lane
>
>
Good question, is there anyway to figure out which rows are locked?

-Nate

Nathan Wilhelmi <wilhelmi@ucar.edu> writes:
> Tom Lane wrote:
>> Insert statements would naturally take RowExclusiveLock, but that
>> doesn't block other DML operations.  So the question is what *else*
>> are you doing?

> Good question, is there anyway to figure out which rows are locked?

RowExclusiveLock is a table-level lock, not a lock on a specific row.
See
http://www.postgresql.org/docs/8.2/static/explicit-locking.html

            regards, tom lane

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

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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