JPA optional uniqueness check possible?

[R van Vliet]

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!

[Ralph Jaus]

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.