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

Re: how to avoid deadlock on masive update with multiples delete

From: "Anibal David Acosta" <aa(at)devshock(dot)com>
To: "'Claudio Freire'" <klaussfreire(at)gmail(dot)com>,"'Jeff Janes'" <jeff(dot)janes(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: 2012-10-05 18:33:49
Message-ID: 011201cda327$f99d73b0$ecd85b10$@devshock.com (view raw or flat)
Thread:
Lists: pgsql-performance
Process 1 (massive update): update table A set column1=0, column2=0 

Process 2 (multiple delete): perform delete_row(user_name, column1, column2)
from table A where user_name=YYY

The pgsql function delete_row delete the row and do other business logic not
related to table A.



-----Mensaje original-----
De: Claudio Freire [mailto:klaussfreire(at)gmail(dot)com] 
Enviado el: viernes, 05 de octubre de 2012 10:27 a.m.
Para: Jeff Janes
CC: Anibal David Acosta; pgsql-performance(at)postgresql(dot)org
Asunto: Re: [PERFORM] how to avoid deadlock on masive update with multiples
delete

On Thu, Oct 4, 2012 at 1:10 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> The bulk update could take an Exclusive (not Access Exclusive) lock.
> Or the delete could perhaps be arranged to delete the records in ctid 
> order (although that might still deadlock).  Or you could just repeat 
> the failed transaction.

How do you make pg update/delete records, in bulk, in some particular order?

(ie, without issuing separate queries for each record)



In response to

pgsql-performance by date

Next:From: viragDate: 2012-10-07 13:49:23
Subject: UPDATE execution time is increasing
Previous:From: Andres FreundDate: 2012-10-05 16:33:53
Subject: Re: how to avoid deadlock on masive update with multiples delete

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