On 8/17/06, Dawid Kuroczko <qnex42@xxxxxxxxx> wrote:
On 8/17/06, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On 8/16/06, Dawid Kuroczko <qnex42@xxxxxxxxx> wrote: > > -- then create a function to retrieve the values: > > CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$ > > DECLARE > > n integer; > > BEGIN > > SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t > > FOR UPDATE; > > UPDATE gapless_seq SET gapless_value = n WHERE gseq_name = t; > > RETURN n; > > END; > > $$ STABLE LANGUAGE PLpgsql; > > > > the problem here is if you have two concurrent transactions which call > this funtion, it is possible for them both to return the same sequence > number in read comitted mode. Using this funtion outside of > transactions is no different that using a sequence except that it is > slower. Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE; The first-to-obtain the gapless sequence transaction will establish a lock onthe "tax_id" row. The other transaction will block until the first transaction finishes (and the row is updated) and will establish the row lock on it.
yes, you are right...i didnt think the problem through properly. merlin
