Serialization, Locking...implement processing Queue with a table - Mailing list pgsql-general
From | D. Dante Lorenso |
---|---|
Subject | Serialization, Locking...implement processing Queue with a table |
Date | |
Msg-id | 038a01c3184c$b9ed65b0$1564a8c0@ROMULUS Whole thread Raw |
Responses |
Re: Serialization, Locking...implement processing Queue with a table
|
List | pgsql-general |
I want to implement a processing Queue with records in a table. This means that I'd like to have multiple processors performing operations against a PostgreSQL database but have those processors only operating on one row each. Ideally, I'd have a PL/PGSQL function that uniquely updates (reserves a row for operation by a given process or thread) and returns only one row at a time from a table like this: int row_id = reserve_next_row(int processor_id); I'm tripping all over myself with transaction isolation modes, transactions, pl/pgsql and basic table locks. The behavior I expect is not happening. How should I go about implementing a synchronized process id queue that will select one unique row from a table at a time and make the selection be safe with concurrent accesses? To get more technical with my attempts, I am using Java/JDBC, PostgreSQL 7.3.2, PL/PGSQL, and have the following code: ---------- 8< -------------------- 8< -------------------- 8< ---------- CREATE OR REPLACE FUNCTION reserve_next_import (bigint) RETURNS bigint AS' DECLARE processor_id ALIAS FOR $1; my_import_id BIGINT; my_number INTEGER; my_import_state CHAR; BEGIN -- Gotta get a handle on this semaphore before you can get in here LOCK TABLE import IN EXCLUSIVE MODE; my_import_id := -1; -- Find the import ID we wish to reserve and get a lock on that row SELECT import_id, import_state INTO my_import_id, my_import_state FROM import WHERE import_state = ''Q'' AND import_processor_id IS NULL ORDER BY import_id LIMIT 1; --FOR UPDATE; -- set this, and processes hit the NOT FOUND below IF NOT FOUND THEN RAISE NOTICE ''No Items left in the Queue.''; RETURN (-1); END IF; -- now go reserve the right to process that record UPDATE import SET import_processor_id = processor_id, import_prc_start = NULL, import_prc_end = NULL, import_state = ''R'' WHERE import_id = my_import_id; -- return the ID for us to process... RETURN (my_import_id); END; 'LANGUAGE 'plpgsql'; ---------- 8< -------------------- 8< -------------------- 8< ---------- Ideally, I could call this function from psql or JDBC and have it block all access to other processes or threads by calling 'LOCK TABLE import IN EXCLUSIVE MODE' and make sure that only one process gets inside the function at a time (like Java's 'synchronized()' function). Well, problem is that my psql instances are still seeing different views of the data and even though one process reserves an ID and updates the state to 'R', the next process doesn't see the update (if it has already started the function as is waiting at the lock) and so it will reserve the same ID in the SELECT. I attempted to fix this by using SELECT FOR UPDATE on my select statement, but problem there is that then the SELECT from the second process fails and a -1 is returned by my function. OK, so you see what I want? I want a function that locks all access to a table, reserves an ID and then releases the lock. I then want any other processes that are waiting for that lock to immediately see the updated information as they process the code inside the function. If I set TRANSACTION ISOLATION level to SERIALIZED in JDBC, then calls will fail if another process is inside the function already. I don't want this either. I want processes to WAIT at the lock then get in and successfully get their own ID. How is this Done? Anybody have an answer for me? Am I going about this the right way? Much help appreciated! Dante D. Dante Lorenso dante@lorenso.com 972-333-4139
pgsql-general by date: