From: Tom Lane <tgl@sss.pgh.pa.us>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Blocking excessively in FOR UPDATE
Date: Thu, 03 Nov 2011 14:45:36 -0400
Message-ID: <6734.1320345936@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGTBQpY7zd9h+xv4Gvh0+-G1UgiWg1zd7zUsXPDFvQoGa4JScA@mail.gmail.com>
References: <CAGTBQpY7zd9h+xv4Gvh0+-G1UgiWg1zd7zUsXPDFvQoGa4JScA@mail.gmail.com>
Claudio Freire <klaussfreire@gmail.com> writes:
> The same query, without FOR UPDATE, takes just 68 milliseconds.
> With the FOR UPDATE, it takes like half a minute or more to finish.
> Now, I understand the for update part may be blocking on some other
> transaction, and it's probably the case.
Yeah, that's what I'd guess.
> But I cannot figure out which transaction it would be. There *are*, in
> fact, connections in <idle in transaction> state, which makes me think
> those would be the culprit. But for the life of me, I cannot make
> sense of the pg_locks view, which shows all locks as granted:
A block on a row would typically show up as one transaction waiting on
another's XID. Did you capture this *while* the query was blocked?
Also, I'm suspicious that you may be using a view that filters out
the relevant lock types --- that's obviously not a raw display of
pg_locks.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, klaussfreire@gmail.com
Subject: Re: Blocking excessively in FOR UPDATE
In-Reply-To: <6734.1320345936@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox