Re: Row locking during UPDATE

Поиск
Список
Период
Сортировка
От Sam Barnett-Cormack
Тема Re: Row locking during UPDATE
Дата
Msg-id Pine.LNX.4.50.0309041630500.28585-100000@short.lancs.ac.uk
обсуждение исходный текст
Ответ на Re: Row locking during UPDATE  ("David F. Skoll" <dfs@roaringpenguin.com>)
Ответы Re: Row locking during UPDATE  ("David F. Skoll" <dfs@roaringpenguin.com>)
Список pgsql-admin
On Thu, 4 Sep 2003, David F. Skoll wrote:

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

It might be worth racking your brains to think of other ways. Query
timeouts?

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

All trying to access the same row seems a bad idea generally. Instead,
why not make it store a new record for each instance, and have a cronjob
each day update the statistics from that. It will be more efficient,
overall. It can be done hourly, even.

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Row locking during UPDATE
Следующее
От: Diko Sastroprawiro
Дата:
Сообщение: unsubscribe