aspose file tools*
The moose likes Servlets and the fly likes duplicate key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "duplicate key" Watch "duplicate key" New topic
Author

duplicate key

Viet Jav
Ranch Hand

Joined: Dec 27, 2001
Posts: 55
I have method insert(UserData[] ud)(Servlet) to insert array UserData into DB(Postgres 7.1) when user click Submit button (JSP)
My problems are when more than a user click Submit button simultaneously, Error duplicate key throw.

Before inserting call method ret = getMaxValues() that return max value of primary key from DB and plus 1.
Primary key in each SQL statement has value is ret ++.

Anyone has experienced my problems, please help.

Thank in advance.

JV.
[ September 20, 2004: Message edited by: Viet Jav ]
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Actually, it is about transaction management. just observe it carefully.

one of your method insert a record in a table STORY, say PK of the record is 1111. after insertion and before commit, whether your conn object's auto commit is true or false, the transaction considered to be dirty. cuz you can rollback it afterwards.

Now at the same time the other thread query that particular table to get the max of your primary key and then increment it by 1. now try to insert a new record with a max(PK)+1.

when this thread query the table to get the primary key max, it gets 1110. because the record 1111 is visible to the same DB session but not to any other. Only when your first thread commit its transaction then any other thread will get the max(pk) you want.

i hope it would be clear now.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
I think gettin the max(pk) and then increment it by 1 is not a better way of primary key generation.

- it will be expensive in terms of performance. and suppose if your table has millions of record then it may sucks. because it requires a full table scan to get the max primary key.

- and the other problem is the same you are gettin.

- suppose you handle the duplication problem somehow then there would be a huge problem in implementing connection pooling mechanism.

therefore, i would like to refer you to this for your unique key generation.
[ September 20, 2004: Message edited by: adeel ansari ]
Viet Jav
Ranch Hand

Joined: Dec 27, 2001
Posts: 55
Thanks,

Yes, I've understood.

But do you have any solution to solve my problems?


JV.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Viet i have refered you to a thread in my previous message. have a look. and then if have some more queries please ask.
Viet Jav
Ranch Hand

Joined: Dec 27, 2001
Posts: 55
Thanks adeel ansari,

I understood.

JV.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: duplicate key