Skip site navigation (1) Skip section navigation (2)

how to avoid deadlock on masive update with multiples delete

From: "Anibal David Acosta" <aa(at)devshock(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: how to avoid deadlock on masive update with multiples delete
Date: 2012-10-04 14:01:15
Message-ID: 005701cda238$ba4d0350$2ee709f0$@devshock.com (view raw or flat)
Thread:
Lists: pgsql-performance
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!

Responses

pgsql-performance by date

Next:From: Jeff JanesDate: 2012-10-04 16:10:08
Subject: Re: how to avoid deadlock on masive update with multiples delete
Previous:From: Jeff JanesDate: 2012-10-03 19:35:42
Subject: Re: Inserts in 'big' table slowing down the database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group