From: Shane Wright <me@shanewright.co.uk>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: table locking
Date: Mon, 18 Feb 2002 15:42:51 +0000
Message-ID: <200202181542.g1IFgcE27761@fullerruss.dsvr.co.uk> (raw)
In-Reply-To: <200202181534.g1IFYDP19471@candle.pha.pa.us>
References: <200202181534.g1IFYDP19471@candle.pha.pa.us>
Hi Bruce,
I've had a go with that, but its proving quite complicated because of the
number of combinations.
The main problem is that the initial SELECT doesn't match any rows (it isnt
supposed to), so I can use SELECT .. FOR UPDATE or anything.
The exact thing is this...
1 - generate an ID randomly
2 - SELECT in the table to see if a row already has that ID
3 - if it does, loop back and generate another
4- if not, insert the new row with that ID
So, I need to block other processes doing that same thing or they can (and
do) pick the same ID and it gets duplicated (or one fails because of a UNIQUE
constraint).
I know it's not the best method for generating IDs, but it works relatively
well given that I cant use sequences (gives away too much info about whats in
the system), and I dont want to use oids (not portable, and issues on the
oids surviving across databases).
--
Shane
On Monday 18 Feb 2002 3:34 pm, Bruce Momjian wrote:
> Shane Wright wrote:
> > Hi Bruce
> >
> > Thanks for the information (and the book link, straight in the bookmarks
> > and remembered for an Amazon search...)
> >
> > Anyway, the chapter was helpful, but I'm still stuck. SERIALIZABLE looks
> > like it would do the job, but I don't want any queries to fail/rollback -
> > I'd rather they stalled on SET TRANSACTION or LOCK TABLE until the first
> > was complete.
> >
> > Is this possible?
> >
> > I am also assuming that even with SERIALIZABLE, concurrent SELECTs can
> > proceed unhindered?
>
> Actually, I think the default READ COMMITTED will work fine for you.
> Backends will block waiting for your write, then continue when you
> commit, and reads will not be affected. I am not totally sure what you
> are testing, so I suggest starting two psql sessions and trying it.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: me@shanewright.co.uk, pgman@candle.pha.pa.us
Subject: Re: table locking
In-Reply-To: <200202181542.g1IFgcE27761@fullerruss.dsvr.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox