Re: Help: massive parallel update to the same table

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