Re: Help: massive parallel update to the same table

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
>