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?

I'm not aware how to create a sequence. Could you give me some more information about this? Does a sequence prevent 2 concurrent transactions working with the same value? — user2054927, Nov 13 '15 at 19:40

Vlad Mihalcea · Accepted Answer · 2021-01-08T15:10:20.863

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:

An InvoiceSequence table with an Entity mapped to it
This table has only one row, storing the latest invoice sequence value

You acquire an exclusive lock on the record:

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

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.

Thx for your answer! I guess I'll have to define some sort of lock time-out to prevent another trx waiting indefinately and to ensure the lock will be released? Maybe I should also add an aspect that automatically retries a failing trx due to lock time-out? — user2054927, Nov 15 '15 at 16:55

adelmo00 · Answer 2 · 2015-11-13T19:48:01.587

1

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;
}}

edited Nov 13 '15 at 19:48

answered Nov 13 '15 at 19:41

adelmo00

147
2
9

The name `lastUniqueValue` is misleading here as the value which you got is already unused one. The `nextval` return next _unused_ value so you can use it directly. – Jan Zyka Nov 13 '15 at 19:46
Thanks for your reply. Is nextval('hibernate_sequence') something Hibernate or database specific? Is this function available on all databases regarding to prevent dialects? – user2054927 Nov 13 '15 at 19:46
Please see: http://stackoverflow.com/questions/1649102/what-is-a-sequence-database-when-would-we-need-it – Jan Zyka Nov 13 '15 at 19:47
Once again thanks for your reply. But I doubt if 'nextval' is useful for this situation because I also have to manage alphanumeric positions (AAA, BAA, BBA...) So I think that I really need to read the business value of the latest inserted record and apply a lock. – user2054927 Nov 13 '15 at 19:53
You can catch the last inserted to generate letters. And get a new value of the sequence and merge with the letters – adelmo00 Nov 13 '15 at 19:55

How to lock table between read and write operations

2 Answers2