Skip to Main Content

Breadcrumb

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

Tom:

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.


exit;



Rating

  (1 rating)

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

Comments

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

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.