Re: Row locking during UPDATE

On Thu, 4 Sep 2003, Tom Lane wrote:

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

Right.  And it waits on a semaphore, right?  So there's no way to
use select() to wait for EITHER the semaphore OR the loss of the connection?
I hate SysV IPC. :-(

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

It's required.  The clients are e-mail filters and they must reply
quickly, before the end of the SMTP transaction.  If they take too long,
they must be killed so the SMTP transaction can be tempfailed.  If they
are not killed, the SMTP sessions pile up and eventually kill the machine.

> 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.

It's on a high-volume mail server that receives around 500K
messages/day.  About 180,000 of those are viruses, so we often have
multiple processes trying to update the virus statistics row.

> Which process does have the row lock, and why isn't it completing its
> transaction?

I don't know the details of PostgreSQL's implementation, but it seems
that when lots of processes are waiting to update the same row, it
gets incredibly slow.

--
David.