1

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

Suneel
  • 817
  • 3
  • 10
  • 23
  • You could first establish if the member is duplicate or not, then start multiple threads to do multiple things based on that knowledge. – rid Nov 08 '11 at 21:22
  • Those requests can come from web browsers, web service clients. So the request thread creation is not in my control. – Suneel Nov 08 '11 at 21:25
  • Shouldnt the final table have unique constraints? I understand the part where you have to check multiple sources for integrity. – r0ast3d Nov 08 '11 at 21:27
  • I don't know if there is a way to add a unique constraint across multiple tables and varies based on the country of the member. – Suneel Nov 08 '11 at 21:30

2 Answers2

3

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.

corsiKa
  • 81,495
  • 25
  • 153
  • 204
1

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.

Matthijs Bierman
  • 1,720
  • 9
  • 14