• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

JPA optional uniqueness check possible?

 
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys,

We have the following problem that I'm hoping you guys have a solution for :

We have a table "Item" with a column "name". We want to be able to determine in a consistent, concurrency safe way that we can add a new record of type "Item" with a value for "name" that is unique. As most readers will know JPA only supports optimistic locking but we have no issues with using vendor specific pessimistic locking. That said I still cannot figure out a good way to do something like this without using database level uniqueness constraints which is something we really want to avoid (we do not always have access to client databases and whether or not this field has to be unique is an application setting that varies across different deploys for us, so doing it on an app level is a lot easier for us). Is there a way to do this without table locks?

I hope someone has a good suggestions for us, thanks!
 
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Appropriate data access layer design depends on characteristics of the application like data volume, performance requirements (do you need fast inserts, selects, both ?), average number of concurrent requests, etc. Therefore, based on your information, it will be difficult to give proper suggestions.

However, the following came into my mind:
1. insert the item record
2. commit the transaction
3. count the number of records having the same name like the record that has just been inserted
4 if the number is greater than one, remove the record again

Remarks:
- It's possible that both records of two concurrent requests will be refused.
- Concerning uniquness of names, the database may be in inconsistent state during steps 3 and 4. However, if a timestamp is available and there is a request, that selects a record for the name in question, you know by timestamp that the older record is the correct one and the other only exists temporarily.
- If data volume is large it surely needs an index or some other database facility to make the select count work with good performance.

Anyway, it's an interesting problem. Please, let us know about your solution.
 
when your children are suffering from your punishment, tell your them it will help them write good poetry when they are older. Like this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic