pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Correct usage of FOR UPDATE?
2+ messages / 2 participants
[nested] [flat]

* Correct usage of FOR UPDATE?
@ 2010-12-09 19:29  Joe Carr <joe.carr@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Joe Carr @ 2010-12-09 19:29 UTC (permalink / raw)
  To: pgsql-sql

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!

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Correct usage of FOR UPDATE?
@ 2010-12-11 10:32  Dmitriy Igrishin <dmitigr@gmail.com>
  parent: Joe Carr <joe.carr@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Dmitriy Igrishin @ 2010-12-11 10:32 UTC (permalink / raw)
  To: Joe Carr <joe.carr@gmail.com>; +Cc: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2010-12-11 10:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-09 19:29 Correct usage of FOR UPDATE? Joe Carr <joe.carr@gmail.com>
2010-12-11 10:32 ` Dmitriy Igrishin <dmitigr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox