Re: Blocking excessively in FOR UPDATE

Claudio Freire <klaussfreire(at)gmail(dot)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