Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Pablo.

Asked: April 11, 2002 - 10:37 pm UTC

Last updated: April 12, 2002 - 10:08 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked


We have an application that insert record into a table called "PERSONS", there are a lot of user using this application, before to insert each record a max(id) (id is the primary_key) value from the persons table is gotten. Also there are batch process that insert records into the same table at the same time, about 2000 records each. The problem is a very frecuent primary key violation because 2 or more user get the same max, one of the user can insert the record with the new id=max(id)+1 but the others get a primary constraint violation. I know that this is a very bad design, a sequence would be a better solution , isn't it?, but the developers don't want to re-write and re-compile the programs. My question is how can i do to avoid the locking and the primary constraint violation at the same time using the max(id) approach?

Thanks in Advance
Pablo R.

and Tom said...

alter session set rant=TRUE;
alter session set rant_level=MAXIMUM;

You cannot.

Here are the steps to fix this problem:

a) fire the existing developers. They obviously don't use databases very often.

b) hire developers who have experience writing large scale, scalable applications, have read the documenation and are willing to fix bugs in their code when discovered.

Sorry to be so blunt but this is "database 101" stuff here.

Yes -- a sequence is the proper design choice.
Yes -- select max(id)+1 from table is the SINGLE WORST WAY to do this.
Yes -- using a sequence would solve your concurrency issue
Yes -- using a sequence would fix the "dup val on index" bug they have introduced

The ONLY solution here is to FIX the application which has a very well defined problem and an even more well defined solution. Period.



  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Ringo Li, April 12, 2002 - 10:08 am UTC

I like the word of firing the non db programmer.

If the developer does not want to make use of the db feature, they can either slove the problem on the application level (by themself) or accept the fact that they do not know how to do it. And start to learn the DB !

Blaming the DBA is not the means to slove the problem