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.
