• 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 ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

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

- 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.
Bring me the box labeled "thinking cap" ... and then read this tiny ad:
the value of filler advertising in 2020
    Bookmark Topic Watch Topic
  • New Topic