6

I need to insert employee in Employee table What i want is is to avoid duplicate inserts i.e. if thwo thread tries to insert same employee at same time then last transaction should fail. For example if first_name and hire_date is same for two employees(same employee coming from two threads) then fail the last transaction.

Approach 1:- First approach i can think of put the constraint at column level(like combined unique constraint on first_name and hire_date) or in the query check if employee exist throw error(i believe it will be possible through PL/SQL)

Approach 2:- Can it be done at java level too like create a method which first check if employee exists then throw error. In that case i need to make the method scynchronized (or synchronized block) but it will impact performance it will unnecassrily hold other transactions also. Is there a way i can make put the lock(Reentrant lock) or use the synchronized method based on name/hiredate so that only those specific thransaction are put on hold which has same name and hiredate

public void  save(Employee emp){
    //hibernate api to save
}

I believe Approach 1 should be preferred as its simple and easier to implement. Right ? Even yes, i would like to know if it can be handled efficiently at java level ?

Improve this question
3
  • 2
    I would think it'd make much more sense to do this at the database level, the database should be concerned with satisfying its own constraints, not Java. Also it means you don't have to worry about concurrency between threads which is a major pain. Commented Oct 19, 2016 at 13:22
  • Unless you're using an embedded database, the only certain way is to check constraints on the database level. Otherwise anyone can just connect to the database and insert anything they want. Commented Oct 19, 2016 at 13:23
  • Your real problem is why your application tries to insert the same employee twice. And how do you determine that they're the same? I hope first_name and hire_date is just an example, since that wouldn't allow you to hire two different John's on the same day. Commented Oct 19, 2016 at 13:25

5 Answers 5

Reset to default
4

What i want is is to avoid duplicate inserts

and

but it will impact performance it will unnecassrily hold other transactions also

So, you want highly concurrent inserts that guarantee no duplicates.

Whether you do this in Java or in the database, the only way to avoid duplicate inserts is to serialize (or, Java-speak, synchronize). That is, have one transaction wait for another.

The Oracle database will do this automatically for you if you create a PRIMARY KEY or UNIQUE constraint on your key values. Simultaneous inserts that are not duplicates will not interfere or wait for one another. However, if two sessions simultaneously attempt duplicate inserts, the second will wait until the first completes. If the first session completed via COMMIT, then the second transaction will fail with a duplicate key on index violation. If the first session completed via ROLLBACK, the second transaction will complete successfully.

You can do something similar in Java as well, but the problem is you need a locking mechanism that is accessible to all sessions. synchronize and similar alternatives work only if all sessions are running in the same JVM.

Also, in Java, a key to maximizing concurrency and minimizing waits would be to only wait for actual duplicates. You can achieve something close to that by hashing the incoming key values and then synchronzing only on that hash. That is, for example, put 65,536 objects into a list. Then when an insert wants to happen, hash the incoming key values to a number between 1 and 65536. Then get that object from list and synchronize on that. Of course, you can also synchronize on the actual key values, but a hash is usually as good and can be easier to work with, especially if the incoming key values are unwieldly or sensitive.

That all said, this should absolutely all be done in the database using a simple PRIMARY KEY constraint on your table and appropriate error handling.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Matthew Mcpeak I think if it I consider it to in java(though will avoid for sure ), I can synchronize on unique values in employee object(like employee SSN + DOB +Name) so that only limited(same employee) transactions are waited. Right?
Yes, that's right. However you do it in Java: using exact values or a hash of them and either using the synchronized keyword or the (better) Lock interface, you need to make sure that all sessions / threads see the same lock objects. E.g.,you can't just have each session take the key values and do a new MyLockObject(keyVal1, keyVal2,...) and then synchronize on it, because they won't be the same object. You need lock objects that all threads can see. That's one reason why the hash approach can be easier: you can create the locking objects statically ahead of time for all threads.
Matthew That's correct. That's why I was planning to use the synchronized block on string interned value which points to same object. For example if I do Synchronized(employee.firstName.interned() + employee.SSN.interned()), then lock should not allow to proceed different employee object but with same firstName and SSN
Well, it's all academic because you're implementing it in the database, right? :) But to answer your question, I think you'd need synchronized((employee.firstName+employee.SSN)).intern()) or something similar. Concatenating two intern() Strings` makes a new String, I think. Also, consider including some distinctive constant for you process in the lock key, like "EMPINS" or something, to not interfere with other areas of the code that implement the same idea in other areas.
1

One of the main reasons of using databases is that they give you consistency.

You are volunteering to put some of that responsibility back into your application. That very much sounds like the wrong approach. Instead, you should study exactly which capabilities your database offers; and try to make "as much use of them as possible".

In that sense you try to fix a problem on the wrong level.

Improve this answer

Comments

0 
Pseudo Code :
void save (Employee emp){
   if(!isEmployeeExist(emp)){
       //Hibernate api to save
   }
}

boolean isEmployeeExist(Employee emp){
    // build and run query for finding the employee
    return true; //if employee exists else return false
}
Improve this answer

1 Comment

How will this ensure that two concurrent threads won't insert the same employee in the database?
0

Good question. I would strongly suggest using MERGE (INSERT and UPDATE in single DML) in this case. Let Oracle handle txn and locks. It's best in your case.

You should create Primary Key, Unique constraint (approach 1) regardless of any solution to preserve data integrity.

-- Sample statement

MERGE INTO employees e
    USING (SELECT * FROM hr_records) h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);
Improve this answer

Comments

0

since the row is not inserted yet, the isolation level such as READ_COMMITED/REPEATABLE_READ will not be applicable on them.

Best is to apply DB constraint(unique) , if that does not exist then in a multi node setup you can't achive it thru java locks as well. As request can go to any node. So, in that case we need to have distributed lock kind of functionality. We can create a table lock where we can define for each table only one/or collection of insertion is possible at a node. Ex: Table_Name, Lock_Acquired emp, 'N'

no any code can get READ_COMMITED on this row and try to update Lock_acuired to 'Y' so , any other code in other thread or other node wont be able to proceed further and lock will be given only when the previous lock has been released. This will make sure highly concurrent system which can avoid duplication, however this will suffer from scalibiliy issue. So decide accordingly what you want to achive.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.