Обсуждение: 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