Spring @Transactional one transaction of method at a time

Question

Required Solution:

Every year accounts registers to DataBase and gets unique number from 1 to n. After year finish next year will be repeater from 1 to n when accounts register.

I have table which stores (example):

id     , available_number, current_year
someId0, 8000            , 2000
someId1, 2500            , 2001

Method snippet:

@Transactional
public AccountNumber getAndIncreaseCorrectAccountNumber(String current_year) {
    AccountNumber accountNumber;
    Optional<AccountNumber> foundAccountNumber = Optional.ofNullable(AccountNumberRepository.findByYear(current_year));
    if(!foundAccountNumber .isPresent()) {
        accountNumber = new AccountNumber();
        accountNumber .setAvailableNumber(1L);
        accountNumber .setCurrentYear(current_year);
    } else {
        accountNumber = foundAccountNumber.get();
        accountNumber.setAvailableNumber(accountNumber.getAvailableNumber() + 1);
    }
    return accountRepository.save(accountNumber);
}

Problem:

As multiple accounts register for the last day (last minute), accounts get same available_number. Noticed 4 accounts registering with same available_number in 1 second (with ~0.1 second difference between registrations)

I think it is related to the thing that one transaction has started then another breaks in and while one is not finished with getting and saving another gets the same available_number

Thinking how to solve:

1. Have read about Isolation and SERIALIZABLE(TransactionDefinition.ISOLATION_SERIALIZABLE); does prevent interactions of transactions with eatch other but It does not prevent from two transactions getting the same available_number. Also read about Propagation and REQUIRES_NEW(TransactionDefinition.PROPAGATION_REQUIRES_NEW) what id does is if new transaction appears while the previous one is not finished then previous freezes, then new is finished and after that first unfreezes and finishes. What is not clear is that in my case first transaction might get value, then it freezes, then after new transaction is done with the same available_number, first transaction unfeezes and finishes with the same available_number (or am I misunderstanding here?)
2. Another option is somehow transform this method in one SQL call. To write though nativeQuery update select which has the logic and does the storing and returning object (any suggestions?). This way only @Transactional should be enough?
3. Is there a way to store method Transactions in a stack and do one at a time (after which first transaction is closed and the new one is opened)?

Sub-question: is there a way to simulate multiple request @test and check whenever the new solution potentially works? (new account registrations will happen only next year)

Answer 1

As @JBNizet wrote and I read in other articles, OptimisticLock solves the problem because it allows only one transaction which modifies the row to be successful. Other transactions are thrown with ObjectOptimisticLockingFailureException. Then I used try{}catch{} to make a recursion on ObjectOptimisticLockingFailureException to redirect back to the same method

Tricky think was that the logic of getAndIncreaseCorrectAccountNumber was in deep layer where in the same transaction a lot of other CRUD logic happened so I needed to pull my try{}catch{} just above the whole @Transaction top layer (else the main object was modified and not rolledback, so try{}catch{} seems to be working when it is above most upper @Transaction)

In code what I did:

1. In AccountNumber added PostgreSQL version of row for OptimisticLock

@Column(name = "version_num") @Version private int version;

2. In repository layer AccountNumberRepository added @Lock(LockModeType.OPTIMISTIC) just above the method

3. Added try{}catch{} for catching OptimisticLock and enabling recurse to the same method

public String submitSomeObjectWithRecursionHandling(@NotNull SomeObject someObject, @NotBlank String accountId){
        try {
        return submitSomeObject(someObject, accountId);
        } catch (ObjectOptimisticLockingFailureException | OptimisticLockException e) {
            e.printStackTrace();
            return submitSomeObjectWithRecursionHandling(someObject, accountId);
        }
    }

submitSomeObject(someObject, accountId) started @Transaction

What I have not managed to achieve was to write integration test. So I created 10 of prepared for submission SomeObects and from Front-End send there request at once to be submitted and it reproduced the bug at first with dublicated accountNumber and later after my fix it showed the recursion is handling the issue

In case you have some ways to test this solution in integration test, let me know.