Generate non-conflicting non-primary-key unique "composite ids" in Hibernate

Question

I'm not sure if my question title is correct, if not, please correct it.

Anyway, long story short, I have sellers, each seller belongs to a company, each seller has an ID as a primary key which is auto-incrementing and a seller-number which is unique per company.

id seller-number company-id
0   0              1
1   1              1
2   2              1
3   0              2
4   1              2
4   2              2

Here's my Seller entity:

@Entity
@Configurable
@Table(name="Seller", uniqueConstraints = {@UniqueConstraint(columnNames= {"company", "sellerNumber"})})
public class Seller implements Serializable {

    @PersistenceContext
    transient EntityManager entityManager;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Version
    @Column(name = "version")
    private Integer version;

    @ManyToOne
    private Company company;

    private Long sellerNumber;

    ...

Now when creating a seller, I do the following:

    @Transactional
    private void createSeller(SellerRequest request, SellerResponse response, Session session) {

        Seller seller = new Seller();

        // generate seller number
        TypedQuery<Long> query = Seller.entityManager().createQuery("SELECT max(o.sellerNumber) + 1 FROM Seller AS o WHERE o.company=:company", Long.class);
        query.setParameter("company", session.getCompany());
        Long sellerNumber = query.getSingleResult();
        seller.setSellerNumber(sellerNumber == null ? 1 : sellerNumber);

        ... 

        seller.setCompany(session.getCompany());

        // persist
        seller.persist();

        ...

The seller numbers I'm getting back is fine, until I start doing a lot of concurrent creates. If two creates happen at the exact same moment, I get a org.hibernate.exception.ConstraintViolationException

The requirements are that I only use an ID as a primary key, no composite primary keys. So taking these constraints into account, how should I be creating these entities so that they have unique seller numbers inside their companies and avoid ConstraintViolationExceptions ? Is using max(o.sellerNumber) + 1 the right way to go or is there a better way to do this?

The following link might help. http://stackoverflow.com/questions/9845575/nested-embeddable-attributeoverride-for-embeddable-within-embeddable — Vasu, Sep 24 '15 at 16:38
I've never used `@Embedded`, care to explain how you envision I use `@Embedded` ? — Jan Vladimir Mostert, Sep 25 '15 at 11:29

score 0 · Accepted Answer · answered Nov 02 '15 at 16:44

The hackish way to accomplish this was to simply catch the ConstraintViolationException and recursively retry the create, increment a retry counter in the request so that it's possible to bail if the number of recursive retries becomes too much.

        try {
            createSeller(...);
        } catch(org.springframework.orm.jpa.JpaSystemException e){
            if (e.contains(org.hibernate.exception.ConstraintViolationException.class)){
                return thisMethodThatCallsCreateSeller(...);
            } else {
                throw e;
            }
        }

On small loads, there's almost no contention, when throwing heavy load at it, there's a little bit of a slowdown as contention causes multiple calls to try and create the seller with multiple ConstraintViolations being caught. Gets the job done though.

Generate non-conflicting non-primary-key unique "composite ids" in Hibernate

1 Answers1