Обсуждение: Correct usage of FOR UPDATE?
Hello,
I have a simple table-based queue system, and I'd looking for some advice on improving my dequeue function. it boils down to:
SELECT id
FROM queue
WHERE <whereclause>
FOR UPDATE NOWAIT;
which works well, in that no item gets dequeued more that once. The issue, however is that when a contention occurs, the error "could not obtain lock on row in relation" is raised.
Is there a way that I can rewrite this so that when a contention occurs, there is no error? I would like the "winning" process to lock the row, and the "losing" process to select null rather than raise the rowlock error.
thanks beforehand for any help!
Hey Joe,
You can wrap the given query in the PL/pgSQL function,
catch the exception and return NULL if it occurs. Please see
http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
--
// Dmitriy.
You can wrap the given query in the PL/pgSQL function,
catch the exception and return NULL if it occurs. Please see
http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
2010/12/9 Joe Carr <joe.carr@gmail.com>
Hello,I have a simple table-based queue system, and I'd looking for some advice on improving my dequeue function. it boils down to:SELECT idFROM queueWHERE <whereclause>FOR UPDATE NOWAIT;which works well, in that no item gets dequeued more that once. The issue, however is that when a contention occurs, the error "could not obtain lock on row in relation" is raised.Is there a way that I can rewrite this so that when a contention occurs, there is no error? I would like the "winning" process to lock the row, and the "losing" process to select null rather than raise the rowlock error.thanks beforehand for any help!
--
// Dmitriy.