Re: SELECT ... FOR UPDATE performance costs? alternatives?

Douglas McNaught wrote:
> "D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:
> How quickly after you update the row status are you comitting (and
> releasing locks)?

I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
immediately after invocation.

SELECT reserve_next_tcqueue(?, ?, ?) AS result

> SELECT id FROM job_table WHERE status = 'New' FOR UPDATE;
> UPDATE job_table SET status = 'Processing' WHERE id IN (<set of IDs>);
> COMMIT; -- releases all locks
> <process each job in the list we got and update its status>
> This has worked very well for me.

Yes, this does work well for me also most of the time. It is only when
the database server begins to suffer from severe load (like 3+) that
PostgreSQL begins to log the reserve_next_tcqueue(...) queries as taking
a long time to complete. Here are some examples:

...

Aug 13 16:00:42 shed03 postgres[20264]: [5-2] reserve_next_tcqueue($1,
$2, $3) AS tcq_id]
Aug 13 16:00:53 shed03 postgres[17338]: [5-1] 17338 dbxxx 10.10.20.163
LOG: duration: 3159.208 ms statement: EXECUTE <unnamed> [PREPARE: SELECT

Aug 13 16:00:54 shed03 postgres[20447]: [5-2] reserve_next_tcqueue($1,
$2, $3) AS tcq_id]
Aug 13 16:00:54 shed03 postgres[20470]: [5-1] 20470 dbxxx 10.10.20.51
LOG: duration: 4162.031 ms statement: EXECUTE <unnamed> [PREPARE: SELECT

Aug 13 16:00:54 shed03 postgres[20470]: [5-2] reserve_next_tcqueue($1,
$2, $3) AS tcq_id]
Aug 13 16:00:59 shed03 postgres[20530]: [5-1] 20530 dbxxx 10.10.20.51
LOG: duration: 3672.077 ms statement: EXECUTE <unnamed> [PREPARE: SELECT

...

-- Dante