From: Anibal David Acosta <aa@devshock.com>
To: pgsql-performance@postgresql.org
Subject: how to avoid deadlock on masive update with multiples delete
Date: Thu, 4 Oct 2012 10:01:15 -0400
Message-ID: <005701cda238$ba4d0350$2ee709f0$@devshock.com> (raw)
Hi,
I have a table with about 10 millions of records, this table is update and
inserted very often during the day (approx. 200 per second) , in the night
the activity is a lot less, so in the first seconds of a day (00:00:01) a
batch process update some columns (used like counters) of this table
setting his value to 0.
Yesterday, the first time it occurs, I got a deadlock when other process try
to delete multiple (about 10 or 20) rows of the same table.
I think that maybe the situation was:
Process A (PA) (massive update)
Process B (PB) (multiple delete)
PA Block record 1, update
PA Block record 2, update
PA Block record 3, update
PB Block record 4, delete
PB Block record 5, delete
PA Block record 4, waiting
PB Block record 3, waiting
The other situation could be that update process while blocking rows scale
to block page and the try to scale to lock table while the delete process as
some locked rows.
Any ideas how to prevent this situation?
Thanks!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aa@devshock.com
Subject: Re: how to avoid deadlock on masive update with multiples delete
In-Reply-To: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox