Custom serial based on user configuration's on database concurrency issue

Question

In our project we created a table(i.e. serial_conf) that has some configuration that user can set. When we need to generate a serial(i.e. 28_12_2019_0001) for a specific entity we call this serial's service class which has @Transactional on it.

The service is doing the following:

1. Get the current serial config from the database
2. Use the returned object to generate a serial number
3. update serial's counter so next time we can generate the correct serial with increasing counter (i.e. 0002)
4. return the result String to the caller of the service

Now the problem is that when I used Jmeter to stress test an API(10 threads at the same time). This API is doing alot of operations and one of them is to generate a serial number. Out of 10 threads only 2 or 4 passes and the other threads threw OptimisticLockingFailureException on step 1 of serial's service.

I've read Spring @Transactional with synchronized keyword doesn't work when i tried using synchronized but it didn't work. I even created a singleton class that calls my service with synchronized signature and all APIs that uses the serial service now calls this singleton so they can line after each one other, but it didn't work too.

Now my question is: What is the correct way to handle this issue? should I make the database do a Lock on the table? or should I use database sequence which does not support custom styles(i.e. 28_12_2019_0001)?

(I'm using spring-boot 1.5, hibernate and postgresql)

EDIT 1: lets say the service is something like this:

@Service("SerialService")
@Transactional(readOnly = false)
public class SerialService{

    @Autowired
    private SerialRepo repo;

    public String generate(Long userId){
            Serial serial = repo.findByUserId(userId).get();
            serial.setCounter(serial.getCounter() + 1);
            serial = repo.save(serial);
            return "custom_serial_"+serial.getCounter().toString();
    }
}

Answer 1

I guess that's what's happening:

1. Thread A: enters the generate method, calls repo.findByUserId and blocks, waiting for the result.
2. Thread B: enters the generate method, calls repo.findByUserId and blocks, waiting for the result.
3. Thread A: repo.findByUserId call completes, returning Serial with a version = 1.
4. Thread B: repo.findByUserId call completes, returning Serial with a version = 1.
5. Thread A: updates the counter and saves the entity. The version gets updated to 2.
6. Thread B: updates the counter and tries to save the entity. OptimisticLockingFailureException is thrown due to the versions mismatch.

Synchronizing the generate method does not help, because transactions are opened before entering this method (in a proxy generated by Spring), so both threads see the same initial version.

In general, it is pretty normal to handle an OptimisticLockingFailureException by retrying the operation, assuming that conflicts happen seldom (hence the name: 'optimistic locking'). In your case, they are pretty frequent though.

The question is if your tests reflect the real application usage. I assume that they perform 10 parallel requests with the same userId. I'm guessing further, that there are separate Serial instances per user, so they are versioned separately. That would mean, that an OptimisticLockingFailureException will be thrown only if one user sends two requests in parallel. It might happen, but it shouldn't be that frequent I guess, so retrying would be acceptable.

I would advise you to repeat your tests with different user ids.