Re: Serialization, Locking...implement processing Queue with a table - Mailing list pgsql-general
From | D. Dante Lorenso |
---|---|
Subject | Re: Serialization, Locking...implement processing Queue with a table |
Date | |
Msg-id | 08d401c319fc$50114d30$1564a8c0@ROMULUS Whole thread Raw |
In response to | Serialization, Locking...implement processing Queue with a table ("D. Dante Lorenso" <dante@lorenso.com>) |
Responses |
Re: Serialization, Locking...implement processing Queue with a table
|
List | pgsql-general |
> ---------- 8< -------------------- 8< ---------- > FUNCTION reserve_job > BEGIN > SELECT and LOCK row_id matching our criteria. (SELECT FOR UPDATE) > > IF (row_id was found) THEN > RESERVE row_id (UPDATE) > RETURN (row_id) -- done > ELSE > Ask "Are you Sure?" there are no rows matching our criteria? > IF (certainly no row_id exists) THEN > RETURN (0) -- no row ID exists > ELSE > RETURN reserve_job -- recursive call > END IF > END IF > END > I am going to try to implement your suggestion and see > what I get in comparison to what I am seeing now with my > LOCK/WAIT code. Well, DAMNIT, this doesn't work. The problem goes back to whatever the heck is happening inside the PL/PGSQL function?! The problem is that this function does not see the COMMITED data from other functions. I've updated my code to the following: ---------- 8< -------------------- 8< -------------------- 8< ---------- CREATE OR REPLACE FUNCTION "public"."subs_get_next_queued" (bigint) RETURNS bigint AS' DECLARE in_pid ALIAS FOR $1; my_reserved_id BIGINT; BEGIN -- initialize the id my_reserved_id := -1; -- Find the ID we wish to reserve and get a lock on that row SELECT subs_id INTO my_reserved_id FROM subscription WHERE subs_start_bill_date <= now() AND subs_next_bill_date <= now() AND subs_processor_id IS NULL ORDER BY subs_id ASC LIMIT 1 FOR UPDATE; -- abort if there are no queued rows IF NOT FOUND THEN -- check again to see if there is something I need to process... SELECT subs_id INTO my_reserved_id FROM subscription WHERE subs_start_bill_date <= now() AND subs_next_bill_date <= now() AND subs_processor_id IS NULL ORDER BY subs_id ASC LIMIT 1; IF NOT FOUND THEN RETURN (-1); ELSE RAISE NOTICE ''GOT LOCKED IN RACE [%]'', my_reserved_id; SELECT subs_get_next_queued(in_pid) INTO my_reserved_id; RETURN (my_reserved_id); END IF; END IF; -- now go reserve the record with our processor id UPDATE subscription SET subs_processor_id = in_pid WHERE subs_id = my_reserved_id; -- this is the row we reserved... RETURN (my_reserved_id); END; 'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; ---------- 8< -------------------- 8< -------------------- 8< ---------- Here is what is happening... Process one is started and is humming along just fine. Then, I kick process 2 into gear and as soon as a collision occurs, process 1 spins out of control and goes nuts in an infinite loop. Apparently, it does not see the changes of process 2 even though they have already been committed. PROCESS 1 PROCESS 2 subs_get_next_queued ---------------------- 126 (1 row) subs_get_next_queued ---------------------- 129 (1 row) subs_get_next_queued ---------------------- 140 (1 row) NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] NOTICE: GOT LOCKED IN RACE [140] ... (continues forever and takes down the PostgreSQL server. OUCH!) subs_get_next_queued ---------------------- 176 (1 row) subs_get_next_queued ---------------------- 182 (1 row) (Continues until PostgreSQL dies because of other processes doings) Can someone please explain what is happening to the PL/PGSQL function in regards to commit visibility and transactions? Dante D. Dante Lorenso dante@lorenso.com 972-333-4139
pgsql-general by date: