Re: Multithreaded queue in PgSQL

Stevo Slavić wrote:
> I'm trying to make implementation more generic, not to use Postgres
> specific SQL, and through Hibernate and Spring configuration make
> services acquire lock on batch of rows, when trying to acquire lock on
> batch of rows an exception should be thrown if rows are already locked
> by a different service, and through that exception I intend to signal
> to other services that they should try to handle and acquire lock on
> next batch of rows. Will see how that goes.
It's postgres specific, but a serializable transaction and
update/returning fits with how you want it to act.

begin transaction isolation level serializable;
update newsletter_recipients
set ....
where (...) in (select ... from newsletter_recipients where not_sent or
crashed limit 10)
returning *;
commit;

The update marks the rows as processing. The returning gives the
selected ones back to the application without having to issue a select
and an update. The serializable transaction throws an error in other
threads that try to claim the same rows. You could add an offset to the
limit to try to select different rows.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au