Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, however, this function begins to
take a long time to execute and I begin to suspect that the FOR
UPDATE lock might be locking the whole table and not just the row.
How do I know if this is the case, how can I optimize this procedure,
and how should I be doing this differently? ...
Thoughts?
SELECT ... FOR UPDATE should only be locking the rows returned by your
the select statement, in this case the one row. You can check what
locks exist on a table (and their type) with the pg_locks system view.
Is that correct? Documentation section 12.3.1. Table-Level Locks states
'The list below shows the available lock modes ...Remember that all of
these lock modes are table-level locks, even if the name contains the
word "row"'.
I wonder why bother with the SELECT statement at all. Why not just go
straight to the UPDATE statement with something like
UPDATE queue SET
status = in_new_status,
ts_start = NOW(),
ts_end = NULL,
hostname = COALESCE(in_hostname, hostname)
WHERE tcq_id = (SELECT tcq_id FROM queue q WHERE q.status =
in_status ORDER BY tcq_id ASC LIMIT 1);
He may need to trap an exception for the "not found" case, but what's
the big deal with that?
UPDATE statements acquire a ROW EXCLUSIVE on the table, which conflicts,
among other things, with ROW EXCLUSIVE, so it will block other UPDATE
statements initiated by other transactions.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
