Re: insert with select as value

On Wed, Jun 23, 2004 at 13:42:12 +0200,
Milos Prudek <prudek(at)bvx(dot)cz> wrote:
> >If your purpose in doing this is just to generate unique keys, you should
> >be using sequences instead.
>
> I would need 150 separate sequences, because each idsection needs its
> own, independent sequence of idthread.

Not if you just want to generate unique keys. In that case you can use one
sequence for each idsection. If you need more than uniqueness then you
don't want to use sequences.

> >Note that you probably want to lock the table before doing this or
> >two transactions running at the same time can generate the same
> >value for idthread.
>
> That's a surprise. I could have made two separate queries (a select and
> then insert) in my programming language (Python), but I wanted to make
> it in one query PRECISELY because I thought that would prevent the race
> condition that you describe. Are you quite sure?

Yes. If two transactions are proceeding at the same time they can both
see the same highest value and hence pick the same next value. You need
to do some sort of locking to prevent this. Lock table is the simplest.
You could also use select for update, but I believe this may result
in occassional deadlocks, so you will need to be able to retry queries
when that happens.