• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA optional uniqueness check possible?

 
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Paddy spent all of his days in the O'Furniture back yard with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic