How to guarantee a row-level unique constraint in a transactional service method?

Question

I have some business checks in my service layer, where I check it with some queries and conditions.

they can not do in the database layer as a unique or checks. but in concurrency two request pass the check and make mistake in my data . the problem is : i take goods serial from user controls , and i check goods serial with database if it's not exists in the database let it to save the record otherwise throw a exception . the image help to understand the problem better : http://pasteboard.co/gPE0BRlRK.jpg

we can use pessimistic lock on hibernate but if we don't have any record in database with number A100 for example checks pass and two transaction commit in database .

the save Method is Here :

@Override
    public Long save(Product entity) {
        if (!isUniqueForSave(entity))
            throw new ApplicationException(saveUniqueError);
        return super.save(entity); 
    }

and the Method isUniqueForSave :

 private boolean isUniqueForSave(Product entity) {
            return iProductRepository.isUniqueForSave(entity);
        }

and the query in DAO layer is in this Method :

public boolean isUniqueForSave(Product entity) {

        Session session = getSession();
        String hql = "select  c from " + domainClass.getName() + " c " 
                    + " where c.ProductNumber      = :productNumber "
                    + " and   c.item.id = :itemId "
                    + " and   c.deleted        = 0 ";



        if (entity.getId() != null && entity.getId() > 0)
            hql += " and c.id != "+entity.getId();

        Query query = session.createQuery(hql);
        query.setParameter("productNumber ", entity.getProductNumber());
        query.setParameter("itemId ", entity.getItem().getId());

        List<Product> productsList = query.list();
        if(productsList .size() > 1)
            return false;
        else if(productsList .size() == 0)
            return true;

        Product product=  productsList .get(0);
        if(entity.getOldItem() != null && product.getId().equals(entity.getOldItem().getId()))
            return true;

        return false;

    }

Answer 1

You code is vulnerable to SQL Injection. You really want to get that fixed too!

The problem here is that you want to guarantee that no other transaction will sneak in and add a Product with the same filtering criteria.

Optimistic locking can only help you if there's already an existing row in the database. Therefore, in your case, optimistic locking works only when the row is marked as deleted.

However, you want this check to work even if there's no record in the database upon the query execution. In this case, you need to use SERIALIZABLE since it prevents Phantom Reads and also Lost Updates.

So, you need to annotate the current transaction as SERIALIZABLE.