Re: Row locking during UPDATE

"David F. Skoll" <dfs(at)roaringpenguin(dot)com> writes:
> What appears to be happening is this:

> 1) Sometimes, a whole bunch of clients try updating the same row. I
> see sevaral postgresql processes marked "UPDATE waiting"

Any process that arrives at the row and finds it already modified by
some concurrent transaction will wait for that concurrent transaction
to complete.

> 2) Because the client processes must complete rather quickly, a supervisor
> process kills them if they don't complete soon.

Zapping clients that are in the middle of database operations is bad
design IMHO.

> 3) The postgresql processes in an "UPDATE waiting" state seem to be
> blocked waiting for a semaphore, and they do not notice the closing of
> the connection when the client is killed.

That's correct, a backend will generally not notice client disconnect
until it next waits for a client command. It's not totally clear why
you've got so many processes waiting to update the same row, though.
Which process does have the row lock, and why isn't it completing its
transaction?

regards, tom lane