Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TJlzM-0002t4-QK for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 14:01:21 +0000 Received: from mail-yx0-f174.google.com ([209.85.213.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TJlzL-00020h-Mz for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 14:01:20 +0000 Received: by mail-yx0-f174.google.com with SMTP id m12so91148yen.19 for ; Thu, 04 Oct 2012 07:01:18 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer :thread-index:content-language:x-gm-message-state; bh=0oxEusiCpqt2LxKrZW7VD2ut1uDFQDcLCAPbSWaoBXo=; b=jy3/2/ffl6+cGjSypml9K06dIfn4qB4Ce1b986EYsfwHDwiiMr2arq05BEfbIGvvB9 d4g05K/tvmS/FrrU5YfUBC8IaCKfJBI3uM2tO2A4Xxdly+MSN7OnIMEHTm996zFwRwLG nLB/kCDRYcn0WEgWHlgr1QVsTB6w0EiBcrguMDytT7rmbKci4fUYt39cYlM6Pk69shTk BwI0rmMrGNrT8NV4KN26xO87puw54SKknB5+K3wQUDJTb59kmPuchDgY3UVjRdaKYD6q eWIU/3Y6CrjIzlHkSfAHafOa5Eq2SqXngfiJom8JCDwaLh9j/hZNjcTBOdFs7Tfqx4LZ Mr4w== Received: by 10.236.177.70 with SMTP id c46mr5258140yhm.33.1349359278850; Thu, 04 Oct 2012 07:01:18 -0700 (PDT) Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86]) by mx.google.com with ESMTPS id h22sm10067362yhk.13.2012.10.04.07.01.17 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 04 Oct 2012 07:01:18 -0700 (PDT) From: "Anibal David Acosta" To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0058_01CDA217.333C26A0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac2iOLgOTRNZ/NTwQ1a1sc4S9b6bRA== Content-Language: es-py X-Gm-Message-State: ALoCoQnvVljEQ02BSvTvqXwG51LuFeNzd5fUeIem8y1/RklciCK8YxwuJpJ8tU9ZjcT5v0czjp7s X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201210/23 X-Sequence-Number: 47982 This is a multipart message in MIME format. ------=_NextPart_000_0058_01CDA217.333C26A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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! ------=_NextPart_000_0058_01CDA217.333C26A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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!

------=_NextPart_000_0058_01CDA217.333C26A0--