Re: Insert race hazard condition.

On 25 Mar 2003, Roy Souther wrote:

> First of all I am a programmer not an DBA expert.
>
> There seems to be many ways to solve this. My first idea would be if I
> could make the INSERT some how return the index+1 that it creates but I
> can not find any way to make that work.
>
> My current idea is to have a short function that does both steps then
> returns the index value. This works and I think it should not cause any
> race hazard because the SQL execution should be completed before it
> returns to the program.

I think that it won't be guaranteed to work unless you lock the table.

> Is there a better way to do this? How do DBAs with a lot of connections
> safely create new records?

You might want to look at using a sequence to do your number generation.