Re: Possible multiprocess lock/unlock-loop problem in Postgresql 9.2

On Sat, Jan 04, 2014 at 12:14:42PM +0100, Yngve N. Pettersen wrote:
> The update query looks like this:
>
> UPDATE queue SET state = E'S' WHERE state = E'I' AND id IN (<list
> of integers>) RETURNING id;
>
> There is a BEGIN/COMMIT wrap around the operation, including the
> SELECT query.

Am I right that you're doing the SELECT, pulling that up into the
application, and then that becomes your <list of integers>? If so, my
first advice is, "Don't do that," because you're making the
transaction run longer (which is probably part of the problem).
Instead, you probably want something like

UPDATE . . . AND id IN (SELECT . . .LIMIT. . .) RETURNING id;

This won't solve your problem perfectly, though.

> My guess is that the active processes get into a lock/unlock loop
> regarding the "state" field because the list of ids overlap, and for
> some reason, instead of completing the operation according to some
> kind of priority order.

My guess is that you have a "lock inversion", yes, but it's hard to
guess what. You want to look at the pg_locks view to figure what's
blocking what. It seems likely that your case is not strictly a
deadlock. A deadlock is a case where transaction A has a lock on
something that transaction B needs, and needs to wait for a lock on an
object that is locked by transaction B. Neither one could possibly
complete, and you get a deadlock detection.

There's an additional possibility that is suggested by your
description, and that is that it's not locks at all, but that you're
running into some kind of system issue. Did you adjust the setting of
sort_mem? It's a long shot, but it could be that if there are enough
sorts in the SELECT (which you don't show us), you're chewing through
a lot of sort_mem. Remember that sort_mem is allocated _per sort_, so
it could be that a lot of these allocations fit fine in real memory if
only 8 processes are doing it; but if 10 do, you pass your threshold
for physical memory and start swapping. I wouldn't expect high CPU
under that case, though, but high I/O. So I think it's doubtful.

Best regards,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca