How to lock table between read and write operations

Question

I've a situation where each new record should contain a unique and readable value. This value has a business meaning for the user and will be handled as a natural id (next to primary key) in our database.

To give you an idea of the value's structure:

 - record 1 has business value 'INVOICE_AAA_001'
 - record 2 has business value 'INVOICE_AAA_002'
   ...
 - record 999  has business value 'INVOICE_AAA_999'
 - record 1000 has business value 'INVOICE_BAA_001'
 - record 1001 has business value 'INVOICE_BAA_002'
   ...

This business value is created by a factory:

class BusinessFactory {

    ...    

    public String createUniqueValue() {
        String lastValue = (String) getSession().createQuery("select businessValue " + 
                                                             "from Invoice as invoice " + 
                                                             "order by businessValue")
                                                .setMaxResults(1)
                                                .setReadOnly(true)
                                                .uniqueResult();    

       // generate new value 

       return newValue;
    }

}

The Service layer will call the factory and save a new Invoice:

  @Transactional
  public void saveNewInvoice() {
      final String newValue = businessFactory.createUniqueValue();        
      Invoice invoice = new Invoice(newValue);
      invoiceRepository.save(invoice);
  }

The problem here is that a situation might exist where trx1 and trx2 read business value 'INVOICE_BAA_002'. What happens next is that 2 trx's are working with the same value. The trx that first commits will succeed, the 2nd will fail due to a unique constraint exception.

Therefore I need to put a lock on Invoice table when reading out the latest Business value. I think this lock should be active until the new Invoice entity is saved to DB.

How should I do this in Hibernate?

Answer 1

Instead of using a conflict detection concurrency control mechanism, such a relying on unique constraints or optimistic locking, you can use pessimistic locking.

You need to have:

1. An InvoiceSequence table with an Entity mapped to it

2. This table has only one row, storing the latest invoice sequence value

3. You acquire an exclusive lock on the record:

   InvoiceSequence invoiceSequence = em.find(InvoiceSequence.class, 1L, LockModeType.PESSIMISTIC_WRITE)
   

4. You increment the sequence using your business logic and modify the entity to store the latest value:

   String currentSequence = invoiceSequence.getValue();
   String nextSequence = sequenceGenerator.nextValue(currentSequence);
   invoiceSequence.setValue(nextSequence);
   

The exclusive lock will prevent both concurrent read and writes too.

Answer 2

A sequence is a set of integers 1, 2, 3, ... that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.

One way you can do

class BusinessFactory {
public String createUniqueValue() {
    String valueNotUsed = (String) getSession().createSQLQuery("select nextval('hibernate_sequence')");    

   // generate new value 

   return newValue;
}}