Re: Multithreaded queue in PgSQL - Mailing list pgsql-general

Hello all,

I've initially brought the question to Nix, so I'll try to clarify situation.

Whole point is to have multiple services accessing same table and dividing the work, so locking with waiting for lock to be released is out of question.

I want to deploy same Java Spring Web application to multiple servers all present in same environment accessing same database. Thus, on each server there will be same business services running. One of them is quartz scheduling service which sends newsletters to newsletter recipients. Hibernate is used for persistence, with Postgres as RDBMS. It is not important for this discussion how newsletters are being created but they end up in the database in following tables:

newsletter (newsletter_id, newsletter_content, newsletter_status, newsletter_status_date)
newsletter_recipient (newsletter_id, newsletter_recipient_email, newsletter_recipient_status, newsletter_recipient_status_date)

newsletter and newsletter_recipient stand in one-to-many relationship with newsletter_recipient.newsletter_id being a FK to newsletter.newsletter_id. PK of each relation is underlined.

Idea is that each service checks for newsletter recipients which either have status NOT_SENT, or have status PROCESSING but newsletter_recipient_status_date is more than 1h old (indicating that newsletter has been tried to be sent, but service didn't complete sending or at least didn't complete updating status to SENT, so we need to retry). Each service should take, e.g. up to 10 such rows, and lock them, so other services see these rows as locked and they shouldn't wait for rows to become unlocked, but should try getting next 10 rows, all until either such batch has been acquired or there are no more such rows.

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.

Regards,
Stevo.

valgog wrote:

On Jun 10, 1:58 pm, alok...@yahoo.com (Nikola Milutinovic) wrote: 

You may find that the PGQ component of skytools is what you want:
 http://pgfoundry.org/projects/skytools
 http://skytools.projects.postgresql.org/doc/
 http://skytools.projects.postgresql.org/doc/pgq-sql.html     

Thanks, we will look into it. Still, I am surprised to learn that SQL as such cannot handle it. I do realize that the question is not trivial. Would setting transaction isolation level to SERIALIZABLE help in any way? Would locking of the entire table help in any way?

Nix.   

The easiest solution that I have found by now, is to use advisory
lock, so that every thread is waiting until the other has released the
lock.

-- Valentine