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

Re: Help: massive parallel update to the same table

From: Red Maple <redmapleleaf(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Help: massive parallel update to the same table
Date: 2011-03-22 14:13:00
Message-ID: AANLkTin0ooBVRjtbvBiB5WcASgsN=jZDWMQmf-dtB6S9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I have found the bug in my code that made the update to the same row in the
table instead of two different row. Now I have all cores up and running
100%.

Thank you for all your help.

On Fri, Mar 18, 2011 at 3:21 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Red Maple <redmapleleaf(at)gmail(dot)com> wrote:
>
> > Here is my function. If I comment out the update then it would run
> > all the cores, if not then only one core will run....
>
> > CREATE OR REPLACE FUNCTION
>
> > [...]
>
> >       select sysuptime
> >         into this_sysuptime
> >         from ap_sysuptime
> >         where ap_id = this_id
> >         for update;
> >
> >       -- ==================================================
> >       -- >>>>>>>> if I comment out the next update
> >       -- >>>>>>>>   then all cores will be running,
> >       -- >>>>>>>>   else only one core will be running
> >       -- ==================================================
> >       update ap_sysuptime
> >         set sysuptime      = this_sysuptime,
> >             last_contacted = now()
> >         where ap_id = this_id;
>
> This proves that you're not showing us the important part.  The
> update locks the same row previously locked by the SELECT FOR
> UPDATE, so any effect at the row level would be a serialization
> failure based on a write conflict, which doesn't sound like your
> problem.  They get different locks at the table level, though:
>
>
> http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html#LOCKING-TABLES
>
> Somewhere in code you're not showing us you're acquiring a lock on
> the ap_sysuptime table which conflicts with a ROW EXCLUSIVE lock but
> not with a ROW SHARE lock.  The lock types which could do that are
> SHARE and SHARE ROW EXCLUSIVE.  CREATE INDEX (without CONCURRENTLY)
> could do that; otherwise it seems that you would need to be
> explicitly issuing a LOCK statement at one of these levels somewhere
> in your transaction.  That is what is causing the transactions to
> run one at a time.
>
> -Kevin
>

In response to

pgsql-performance by date

Next:From: Mahadevan, MridulaDate: 2011-03-22 16:13:30
Subject: Analyze on temp table taking very long
Previous:From: Merlin MoncureDate: 2011-03-22 13:54:15
Subject: Re: Request for feedback on hardware for a new database server

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