Re: table locking

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.