Re: Help: massive parallel update to the same table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.Grittner@xxxxxxxxxxxx> wrote:
Red Maple <redmapleleaf@xxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux