Re: Serialization, Locking...implement processing Queue with a table

> (i) no jobs are pending. Commit your transaction,
> >>sleep for an appropriate delay period, and try again.
> (ii) The row has been reserved by another transaction running at the
> same time. In this case, restart at SELECT FOR UPDATE. You can stay
> in the same transaction. And you don't need to sleep (this has
> already happened while SELECT FOR UPDATE was blocked), unless there
> are lots of job processors in which case it might be advisible to
> sleep for a short random time.
>
> How do you distinguish between (i) and (ii)? Just do
> SELECT job_id FROM job_table
> WHERE processor_id = 0 LIMIT 1;
> If this returns 0 rows, you have (i).
> If it returns one row, you have (ii).

You can't do it this way because if this select IS successful,
you'll still need to LOCK the row for the update. Any way you
try, you're in a race condition whereby another running thread
may lock and/or update that row after you select. Remember, we
are in READ COMMITTED mode. Without a lock on the row or a table,
your chances are that the row will be updated out from under you.

Here is a process that I've come up with that locks a bogus table
for me to guarantee that only one process has the right to enter
the stored procedure:

---------- 8< --------------------
BEGIN TRANSACTION;
LOCK TABLE proc_lock IN EXCLUSIVE MODE;
SELECT proc($process_id) AS RESULT;
COMMIT;
---------- 8< --------------------

If I wrap my stored procedure calls with a call like this, I can
guarantee that only one process gets INSIDE the procedure at a
time. The "proc_lock" table can be any table and for my cases,
I use a table named the same as the table I'm going to be operating
on with _lock added to the end. This way I don't have to actually
lock a table which is working for other selects by other apps that
don't care about concurrency locks for queue processing.

Now, my stored procedure can do any logic it wants and doesn't
have to worry about concurrency. I tried to put the 'LOCK TABLE'
statement as the first line in the PL/PGSQL procedure, but it
didn't seem to work there.

ex:

---------- 8< --------------------
CREATE OR REPLACE FUNCTION reserve_next_queued (bigint)
RETURNS bigint AS'
DECLARE
processor_id ALIAS FOR $1;
my_reserved_id BIGINT;
BEGIN
LOCK TABLE proc_lock IN EXCLUSIVE MODE;
...
RETURN (my_reserved_id);
END;
'LANGUAGE 'plpgsql';
---------- 8< --------------------

Apparently locks work in the stored proc but READ COMMITTED
does not? For me, it looks like once the procedure has been
entered postgres taken a snapshot of the state of the database
BEFORE the procedure was called. So, the lock blocks as it
should, but the subsequent select will see the OLD state and
not the updated COMMIT from the rows that were just updated by
the other thread that held the lock.

In other words, it looks like THIS is happening with
app (1) and app (2) trying the same stored proc call:

(1) begin transaction
(2) begin transaction
(1) call PL/PGSQL function
(2) call PL/PGSQL function
(1) LOCK table <- no wait, lock granted immediately
(2) LOCK table <- lock already held BLOCKED
(1) process PL/PGSQL function
(1) exit PL/PGSQL function
(1) UNLOCK table
(2) LOCK table <- lock granted
(2) process PL/PGSQL function
(1) COMMIT;
(2) exit PL/PGSQL function
(2) COMMIT;

Is that possible? Can it bee that the lock is being freed
before the COMMIT is called? Or is the procedure somehow
not allowing my other apps to see the committed data
immediately?

Pulling this lock out of the PL/PGSQL function to the calling
transaction wrapper worked, though.

In the future, I'd like to see PL/PGSQL allow me to embed
transactions stuff like that. For now, I think my current
solution is clean enough to use without having to write try/fail
routines. This method trys, waits, then succeeds. It'd have to
WAIT that long anyhow, but this this approach, the wait is handled
but PostgreSQL and not my app.

I'm interested in knowing if anyone sees a FLAW with this design.
>From my tests, I have code that works exactly as designed but not
as cleanly as I'd like. What are your thoughts?

Dante

D. Dante Lorenso
dante(at)direct2prospect(dot)com
972-333-4139