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:
> 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_sysuptimeThis proves that you're not showing us the important part. The
> 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;
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
