Prevent duplicate inserts without using unique keys in Oracle

This is a use case in member enrollment via web application/web service. We have a complex algorithm for checking if a member is duplicate, by looking at multiple tables like phone,address etc. The algorithm varies based on member's country. So this restriction cannot be implemented using primary key/unique key constraint.

So we have the checks in Java code. But if there are 2 duplicate concurrent requests, the 2 Java threads see that the member doesn't exist and they both insert the record resulting in duplicates. How can I prevent such duplicate inserts?

I can prevent updates by using row level locks or Hibernate's optimistic concurrency. I can think of table level locks to prevent such inserts, but limits the application performance as it also blocks updates. Another option I think would be to create a lock table with a record with id='memberInsert', and force all inserts via JDBC to obtain a row level lock on this record.

Thanks Suneel


If it's going to be anywhere, I'd expect it to be in a write trigger, not in the Java code. Some other application or some other area of the application could do something badly.

Offloading this on the database gives you two advantages. 1) It prevents the race condition you mention up there and 2) It protects the integrity of the data by not allowing some errant application to modify records putting them in an illegal state.

Can't you hash the outcome of the algorithm or something and simply use that as a unique primary key?

As long as the database is not aware of your requirements, it will not help you. And then you probably have no other choice than table level locking.

Category:java Time:2011-11-08 Views:0

Related post

  • How to use SqlBulkCopy to prevent duplicates insert? 2012-03-29

    I am going to use SqlBulkCopy, but I need to prevent duplicates insert. What is the best way to do it? Have I do check existence for each item before make execute WriteToServer command? --------------Solutions------------- Bring all the values form t

  • Rails: Prevent duplicate inserts due to pressing back button and save again 2011-01-11

    Think about a simple Rails scaffold application with a "new" action containing a form to add records to a database with a "save" button. After the "create" action the controller redirects to the "show" action, where the user can use the "edit" link t

  • Sql insertion via insensitive unique key 2012-09-10

    I have a column on my table which has unique key property, but i also want it to be unique in an insensitive manner.What happens is if there is a key like xyz, insert statement with values Xyz,xYZ works properly, which I want to prevent. What can i u

  • How do I insert into a unique key into a table? 2010-03-12

    I want to insert data into a table where I don't know the next unique key that I need. I'm not sure how to format my INSERT query so that the value of the Key field is 1 greater than the maximum value for the key in the table. I know this is a hack,

  • Prevent duplicate insert without session 2011-01-31

    im working on sharepoint 2010 (using visual webpart) is there any way to prevent duplicate insert in a list when page is refreshed using F5, without using session thx --------------Solutions------------- The sharepoint "way" is usually to redirect to

  • Preventing Duplicate Inserts Into SQL With PHP 2008-11-09

    I'm going to running thousands of queries into SQL and I need to prevent the duplication of field 'domain'. Never had to do this before and any help would be appreciated. --------------Solutions------------- You probably want to create a "UNIQUE" con

  • Find or insert based on unique key with Hibernate 2011-02-16

    I'm trying to write a method that will return a Hibernate object based on a unique but non-primary key. If the entity already exists in the database I want to return it, but if it doesn't I want to create a new instance and save it before returning.

  • prevent duplicate insert by using updatepanel 2011-01-14

    i wrote a page which are several fields and there is gridview which keeps the data inserted bottom of the page and i added a updatepanel... when i click the insert double data inserting to grid how to prevent it... --------------Solutions------------

  • can isolation levels prevent duplicate insert? 2012-04-09

    i have a table like this UserPageId (Primary Key) int UserId (user Foreign Key) int PageId (page Foreign Key) int and the whole senario is to prevent user to add one page more than once,,according to each user can be use with several person and we mi

  • Time complexity : Insertion sort with unique key 2012-04-16

    I have a random ordered array of 30 elements with only 3 distinct keys (TRUE, FALSE and NULL) that I want to sort using insertion sort. What will be the time complexity? Will it be O(n2) assuming worst case or O(n) assuming best case since there are

  • How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key 2010-04-07

    I receive a daily XML file that contains thousands of records, each being a business transaction that I need to store in an internal database for use in reporting and billing. I was under the impression that each day's file contained only unique reco

  • Preventing Duplicate Table Inserts 2011-06-17

    The database is SQL Server 2008. I have a query which extracts rows from one or more tables and then attempts to insert them into a table variable. I'd like an efficent way to prevent duplicate inserts, so what I came up with was: INSERT INTO @MyTabl

  • Unique key violation on insert of special char string 2010-05-04

    I'm having issues when trying to insert nvarchar values in sql server with a linq-to-sql + c# program. Here is the code: public partial class Recipient { private static object _recipientLock = new object(); public static Recipient Find(string email,

  • preventing duplicate table inserts using jdo in android connected appengine 2012-03-18

    I would like to know how to prevent duplicate inserts when doing an RPC call from an Android client connected to app engine. Below is my code and what I tried at the back-end but when I do this I get an "Internal Server Error". public void createenti

  • Violation of UNIQUE KEY Contstraints - Cannot insert duplicate key 2010-09-30

    I am pulling out my hair over here. I have a table that has a UNIQUE Key (IX_tblTable) and the unique key is on a column Number. I am parsing some data from the web and storing it in the table. My latest collection of data from the web contains numbe

  • Violation of UNIQUE KEY constraint. Cannot insert duplicate key in object 2012-02-13

    We are trying to increase the scope of database compatibility for our web application. Our application is Java EE with JSP, Servlets, and EJBs. The database we are trying to make our application compatible with is SQL Server 2008. The problem we are

  • Should I lock an ISAM table to insert a value into a unique key field? 2008-10-04

    I have an ISAm table in mySql that was created similar to this: create table mytable ( id int not null auto_increment primary key, name varchar(64) not null ); create unique index nameIndex on mytable (name); I have multiple processes inserting rows

  • How to prevent duplicate rows in insert query using Oracle? 2009-10-31

    I have table A in Oracle that has a primary key (id). I need to insert data into this table. How do I prevent duplicate rows? --------------Solutions------------- If the id column is marked as the PK you will not be able to insert a duplicate key, th

  • Prevent duplicate record insert on manual page refresh 2010-09-16

    I have a problem which has only just surfaced itself. I'm working in a MVC environment. The method names in my interface class match those of the request module and action i.e. ?module=test&action=action would result in a method called public fun

Copyright (C), All Rights Reserved.

processed in 0.491 (s). 13 q(s)