Thread: Insert race hazard condition.

First of all I am a programmer not an DBA expert.

For a long time now when I create a table I have an index field that is
of type int4 to be the primary key. All of the queries and activity on
each record uses this field to do the posting, manipulating and reading
of data to and from tables.

When I add a record to a table I would do it in two steps.
INSERT INTO mytable SELECT MAX(index)+1 FROM mytable
SELECT MAX(index) FROM mytable

The first part would create a new record with the next index value. The
second command would return to me the index value so that I could use it
to update data in that record using the index value.

Up till now this has worked fine in C C++ and Perl, the languages I use
mostly. These two commands happen very quickly so on a database that has
a low amount of activity it works fine. Now I am getting into a
situation where many connections are adding a lot of records very
quickly. Last week an error happened where two records where created
that had the same index value.

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.

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

Roy Souther
www.SiliconTao.com

Changing the way people do business.

On Tue, Mar 25, 2003 at 13:01:17 -0700, Roy Souther <roy@SiliconTao.com> wrote:
> First of all I am a programmer not an DBA expert. 
> 
> For a long time now when I create a table I have an index field that is
> of type int4 to be the primary key. All of the queries and activity on
> each record uses this field to do the posting, manipulating and reading
> of data to and from tables.

[snip]

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

The short answer is to use sequences. Use currval to get the value assigned
earlier in the same transaction.

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.

On Tue, Mar 25, 2003 at 01:01:17PM -0700, Roy Souther wrote:
> First of all I am a programmer not an DBA expert. 
> 
> For a long time now when I create a table I have an index field that is
> of type int4 to be the primary key. All of the queries and activity on
> each record uses this field to do the posting, manipulating and reading
> of data to and from tables.
> 
> When I add a record to a table I would do it in two steps.
> INSERT INTO mytable SELECT MAX(index)+1 FROM mytable
> SELECT MAX(index) FROM mytable


You probably want to call:
 Oid oid = PQoidValue( res ); /* assuming C interface */

And use:
 sprintf( sql, "select index from mytable where oid = %lu", oid );

instead of your second SQL statement.

[dd]

sidster
--
They who would sacrifice freedom for security will have neither.  -Ben Franklin