File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Inserting Duplicate rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Inserting Duplicate rows" Watch "Inserting Duplicate rows" New topic

Inserting Duplicate rows


Joined: Nov 20, 2004
Posts: 3
Hi all,
Ours is a WebServices application. Whenever a request comes it is first checked for duplicate transaction from the table.
If a duplication transaction ID exists the request is rejected else it is processed and at the end...the record is inserted in the database.

Now the problem is if 2 or more requests having same id comes at same time...the application process them paralelly and unable to identify duplicate id and finally getting inserted in database after completion of process.

Unique Constraint will only help in not inserting duplicate row...but before that the actual processing has already taken place.

Is there any way in JDBC to check for duplicate records at the same time.

Annie Smith
Ranch Hand

Joined: Mar 05, 2005
Posts: 172
I don't see a reason why a unique constraint won't solve the problem. Two requests can never be precisely at the same time.

However, you could consider having some transaction handling and table locking.

Cheers!<br /><b>Annie</b>
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

We're pleased to have you here with us in the JDBC forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Forum Bartender

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63866

"Jumbalika", you had already been asked before (in this topic) to adjust your display name to confirm to JavaRanch standards. This is not optional. Be aware that accounts with invalid display names are subject to removal.

Forum bartender

[Asking smart questions] [About Bear] [Books by Bear]
Jayesh Lalwani
Ranch Hand

Joined: Nov 05, 2004
Posts: 502
You should be storing the request in the database before processing it. This helps in 2 ways

a) you will be able to detect duplicate requests
b) In case your application stops because of critical error or the server is rebooted, you will hae the request saved in the database. So, when you start your application back again, you can process the rquest again
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
What about using a stored procedure ? Let the Sp do the work and use a sequence, so that the unique constraint won't get voilated.

Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Luis Canizares

Joined: Mar 12, 2005
Posts: 12
Try not to use "distinct" restrictions in your SQL clauses
Good luck !!
Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
Hello Jumbalika,

I think unique constraint should work for avoiding duplication, and Mr. Annie there might be occurs situation where 2 or 3 requests can come at the same time, mostly in web applications.

Cheers<br />Nitin
I agree. Here's the link:
subject: Inserting Duplicate rows
It's not a secret anymore!