• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

duplicate key

 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks,

Yes, I've understood.

But do you have any solution to solve my problems?


JV.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks adeel ansari,

I understood.

JV.
 
I'm so happy! And I wish to make this tiny ad happy too:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic