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 ]
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.
Joined: Aug 15, 2004
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 ]
Joined: Dec 27, 2001
Yes, I've understood.
But do you have any solution to solve my problems?
Joined: Aug 15, 2004
Viet i have refered you to a thread in my previous message. have a look. and then if have some more queries please ask.