Postgres Pro
Downloads
Home   >   mailing lists

Thread: Correct usage of FOR UPDATE?

Correct usage of FOR UPDATE?

From
Joe Carr
Date:
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!

Re: Correct usage of FOR UPDATE?

From
Dmitriy Igrishin
Date:
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

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 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!



--
// Dmitriy.