Testing Pessimistic Locking Handling with Spring Boot and JPA

September 25, 2020
by Andrey Zahariev Stoev

Testing pessimistic locking handling in JPA is tricky because of the lack of JPA support of LockTimeout by different RDBMS providers. Yet leaving a critical part of the code untested is against the software craftsmanship's principles. In this blog post, I will show you through the medium of 3 GitHub projects how you could test it within Spring Boot Application against Oracle, MySQL and PostgreSQL.

But before that, let's try to identify the user needs for a viable solution:

User Needs For Testing Pessimistic Locking Handling

Why Testing Pessimistic Locking Handling Is Important?

Bellow the native support of pessimistic locking by different RDBMS providers (source: first-hand testing):

LockTimeout Oracle MySQL PostgreSQL H2 Apache Derby
unit of measurement sec sec ms ms sec
nowait (or similar) 0 0 1 1 0
min 0 0 1 1 0
max 2.147.483 oo (no internal limit) 2.147.483.647 2.147.483.647 oo (no internal limit)
infinite wait -1 N/A 0 N/A N/A
default -1 50 0 4000 60

Moreover, when you tune the performance of the system you would need to define custom LockTimeout duration for different queries with pessimistic locking.

🔔 Therefore, if you don't set correct and customizable LockTimeout value for your queries, the overall performance of your system could seriously suffer!

🔔 Even if you consider, given the complexity of pessimistic locking testing, that "manual" testing for pessimistic locking handling is enough 😱 - please re-consider it: In the lifecycle of the project, sooner or later, other developers will make changes to this code. If the pessimistic locking handling implementation is not covered by well-written automated tests, the probability that something will go wrong will increase with time.

Challenges In Finding A Viable Solution

Bellow the JPA support of pessimistic locking by different RDBMS providers (source: first-hand testing):

jpa feature Oracle MySQL PostgreSQL H2 Apache Derby
LockModeType.PESSIMISTIC_WRITE x x x x x
javax.persistence.lock.timeout x
javax.persistence.lock.timeout=0 (nowait) x x
handling of LockTimeoutException x x x x

Adding to that...

... well, I decided to invest some time to verify first-hand the JPA and native pessimistic locking support by different RDBMS providers and to develop a RDBMS-agnostic solution for testing pessimistic locking within Spring Boot and JPA. I am happy to offer this solution to you and I hope you can make good use of it! 😃

GitHub Projects With Examples How To Test Pessimistic Locking Handling

If you are eager just to grab the code, here it is:

Pessimistic Locking Explained

You could find a detailed explanation of pessimistic locking handling here.

The Solution Explained

The Most Important Implementation Choices

UML Diagram Of The Solution

test-psm-locking-uml

One additional interface and four extra classes are required for every Spring Data's Repository to make its containing pessimistic locking queries testable and platform-agnostic:

🔔 CustomizedItemRepositoryContext is the only class which has different implementations between the 3 solutions for Oracle, PostgreSQL and MySQL.

🔔 Spring Data does not directly allow two different implementations of CustomizedItemRepository through @Profile annotation because this interface is part of the low-level ItemRepository interface. To avoid this limitation a CustomizedItemRepositoryContext has been added, which solves the problem!

🔔 To make it explicit that a method is obtaining a pessimistic locking instead of just naming it "getItemById" it is named "getItemAndObtainPessimisticWriteLockingOnItById". This way, the person who is using/changing this method will pay additional attention to it!

Comparison Between Different RDBMS Parameters In The Solution

parameter Oracle MySQL PostgreSQL Apache Derby
requiredToSetLockTimeoutForTestsAtStartup false false false true
requiredToSetLockTimeoutForEveryQuery false true true false
requiredToSetLockTimeoutQueryHint true false false false
delayAtTheEndOfTheQueryForPessimisticLockingTestingInMs 200 2000 0 0
minimalPossibleLockTimeOutInMs 0 1000 1 0
lockTimeOutInMsForQueryGetItem 5000 5000 5000 5000

Pessimistic Locking Handling Scenario Explained

case-two-req-psm-locking-timeout

🔔 Handling a LockTimeoutException through internal retry might not be the most adequate mechanism but for demonstration purposes it should be a good fit. For more information please read here!

The Handling Explained

ItemService

@RequiredArgsConstructor
@Slf4j
@Service
public class ItemService {

    private final ItemRepository itemRepository;

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public Item incrementAmount(UUID id, int amount) {
        Item item = itemRepository.getItemAndObtainPessimisticWriteLockingOnItById(id);
        item.setAmount(item.getAmount() + amount);
        return item;
    }
}

InventoryService

@RequiredArgsConstructor
@Slf4j
@Service
public class InventoryService {

    private static final long PESSIMISTIC_LOCKING_EXCEPTION_HANDLING_RETRY_AFTER_MS = 200;

    private final ItemService itemService;

    @Transactional(readOnly = true)
    public void incrementAmount(UUID id, int amount) {
        try {
            itemService.incrementAmount(id, amount);
        } catch (PessimisticLockingFailureException e) {
            log.error("Found pessimistic lock exception!", e);
            sleep(PESSIMISTIC_LOCKING_EXCEPTION_HANDLING_RETRY_AFTER_MS);
            itemService.incrementAmount(id, amount);
        }
    }

    private void sleep(long timeout) {
        try {
            TimeUnit.MILLISECONDS.sleep(timeout);
        } catch (InterruptedException ie) {
            Thread.currentThread().interrupt();
        }
    }
}

🔔 Having two locked transactions instead of one for every concurrent thread only for the sake of integration tests may not seem so palatable at first; Nevertheless, this is only valid for "retry" pessimistic locking handling strategy, where you may want to "save" the highest level transaction. For the most "production-ready" pessimistic locking handling strategies you will block only one transaction. For more info read here.

The Test Explained

@SpringBootTest
@ActiveProfiles("test")
//@ActiveProfiles("test-oracle")
@Slf4j
class InventoryServicePessimisticLockingTest {

    @Autowired
    private ItemRepository itemRepository;

    @Autowired
    private InventoryService inventoryService;

    @SpyBean
    private ItemService itemService;

    @SpyBean
    private CustomizedItemRepositoryContext customizedItemRepositoryContext;

    @Autowired
    private PlatformTransactionManager transactionManager;

    @AfterEach
    void tearDown() {
        itemRepository.deleteAll();
    }

    /*
     * TEST PESSIMISTIC LOCKING
     */

    @Test
    void shouldIncrementItemAmount_withoutConcurrency() throws Exception {
        assertIncrementItemAmountWithPessimisticLocking(false, false, 2);
    }

    @Test
    void shouldIncrementItemAmount_withinPessimisticLockingConcurrencyWithMinimalLockTimeout() throws Exception {
        assertIncrementItemAmountWithPessimisticLocking(true, true, 3);
    }

    @Test
    void shouldIncrementItemAmount_withinPessimisticLockingConcurrencyWithDefaultLockTimeout() throws Exception {
        assertIncrementItemAmountWithPessimisticLocking(true, false, 2);
    }

    private void assertIncrementItemAmountWithPessimisticLocking(
            boolean simulatePessimisticLocking,
            boolean hasToSetMinimalLockTimeOut,
            int expectedNumberOfItemServiceInvocations) throws Exception {

        // given
        if (hasToSetMinimalLockTimeOut) {
            long lockTimeOutInMs = customizedItemRepositoryContext.getMinimalPossibleLockTimeOutInMs();
            when(customizedItemRepositoryContext.getLockTimeOutInMsForQueryGetItem()).thenReturn(lockTimeOutInMs);
        }

        if (hasToSetMinimalLockTimeOut && customizedItemRepositoryContext.isRequiredToSetLockTimeoutForTestsAtStartup()) {
            log.info("... set lockTimeOut {} ms through native query at startup ...", customizedItemRepositoryContext.getMinimalPossibleLockTimeOutInMs());
            TransactionStatus tx = transactionManager.getTransaction(new DefaultTransactionDefinition());
            itemRepository.setLockTimeout(customizedItemRepositoryContext.getMinimalPossibleLockTimeOutInMs());
            transactionManager.commit(tx);
        }

        if (simulatePessimisticLocking) {
            insertDelayAtTheEndOfPessimisticLockingSection();
        }

        final Item srcItem = itemRepository.save(new Item());

        // when
        final List<Integer> itemAmounts = Arrays.asList(10, 5);

        if (simulatePessimisticLocking) {
            final ExecutorService executor = Executors.newFixedThreadPool(itemAmounts.size());

            for (final int amount : itemAmounts) {
                executor.execute(() -> inventoryService.incrementAmount(srcItem.getId(), amount));
            }

            executor.shutdown();
            executor.awaitTermination(1, TimeUnit.MINUTES);
        } else {
            for (final int amount : itemAmounts) {
                inventoryService.incrementAmount(srcItem.getId(), amount);
            }
        }

        // then
        final Item item = itemRepository.findById(srcItem.getId()).get();

        assertAll(
                () -> assertEquals(15, item.getAmount()),
                () -> verify(itemService, times(expectedNumberOfItemServiceInvocations)).incrementAmount(any(UUID.class), anyInt())
        );
    }

    private void insertDelayAtTheEndOfPessimisticLockingSection() {
        long delay = customizedItemRepositoryContext.getDelayAtTheEndOfTheQueryForPessimisticLockingTestingInMs();
        doAnswer(invocation -> {
            try {
                TimeUnit.MILLISECONDS.sleep(delay);
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
            }
            return null;
        }).when(customizedItemRepositoryContext).insertArtificialDealyAtTheEndOfTheQueryForTestsOnly();
    }

    /*
     * TEST SET&GET LOCK TIMEOUT
     */

    @Test
    void shouldSetAndGetLockTimeOut() {
        if (customizedItemRepositoryContext.isRequiredToSetLockTimeoutQueryHint()) {
            assertThrows(UnsupportedOperationException.class, () -> itemRepository.setLockTimeout(0));
            assertThrows(UnsupportedOperationException.class, () -> itemRepository.getLockTimeout());
            return;
        }

        assertSetLockTimeOut(customizedItemRepositoryContext.getMinimalPossibleLockTimeOutInMs());
        assertSetLockTimeOut(TimeUnit.SECONDS.toMillis(2));
        assertSetLockTimeOut(TimeUnit.MINUTES.toMillis(2));
        assertSetLockTimeOut(TimeUnit.HOURS.toMillis(2));
        assertSetLockTimeOut(TimeUnit.DAYS.toMillis(2));
    }

    private void assertSetLockTimeOut(long expectedMilliseconds) {
        TransactionStatus tx = transactionManager.getTransaction(new DefaultTransactionDefinition());
        itemRepository.setLockTimeout(expectedMilliseconds);
        assertEquals(expectedMilliseconds, itemRepository.getLockTimeout());
        transactionManager.commit(tx);
    }
}

There are 3 tests for testing the pessimistic locking handling mechanism which assert the amount of invocations of method itemService.incrementAmount(...):

There is an additional test as well which asserts if the setLockTimeout and getLockTimeout are working correctly. Note that if your provider implements JPA correctly (like Oracle) these methods would throw if invoked UnsupportedOperationException.

🔔 Notice that the test inserts some provider-specific delay through the method "insertDelayAtTheEndOfPessimisticLockingSection" at the end of the query. You need this especially for Oracle and MySQL to simulate the "long" running query which causes the LockTimeoutException. For example in the case of MySQL the minimal possible LockTimeout duration is 1000ms and there is no other choice than to wait long enough - and in this case, the parameter "delayAtTheEndOfTheQueryForPessimisticLockingTestingInMs" has to be set to 2000ms!

🔔 The method "customizedItemRepositoryContext.insertArtificialDealyAtTheEndOfTheQueryForTestsOnly()" in the production code is empty. Only in the test environment will a delay be injected. It is a good practice as well to interrupt the current thread when handling the InterruptedException.

🔔 Notice, as well, that if the test runs longer than 1 minute the Thread Executor will abort it with failure!

executor.awaitTermination(1, TimeUnit.MINUTES);

🔔 Take a look at the usage of @SpyBean annotation in the code. This annotation indirectly uses the real spring component but a Mockito wrapper class allows you to do some verifications and to mock part of it if you need it.

@SpyBean
private ItemService itemService;
...
() -> verify(itemService, times(expectedNumberOfItemServiceInvocations)).incrementAmount(any(UUID.class), anyInt())
...

Test Execution Against The In-Memory Database Apache Derby

@SpringBootTest
@ActiveProfiles("test")
//@ActiveProfiles("test-oracle")

Using the default "test" profile for your integration tests will allow you to run all pessimistic locking handling tests in-memory.

🔔 This is usually the most suitable format for your integration tests because you don't need additional infrastructure for running them in your CI pipeline!

🔔 As explained earlier, you need an in-memory database with good pessimistic locking support (like Apache Derby) to run these tests. If you have used another in-memory database provider (like H2 or HSQLDB), you don't need to migrate these tests to Apache Derby. Just use two different profiles!

Test Execution Against The Production Database Oracle, MySQL or PostgreSQL

@SpringBootTest
//@ActiveProfiles("test")
@ActiveProfiles("test-oracle")

Using the "test-oracle", "test-mysql" or "test-postgresql" profile for your integration tests will allow you to run all pessimistic locking handling tests against your production database.

🔔 This format is suitable for the local environment when you do changes to your pessimistic locking handling. You could check this profile periodically to be sure that all is going to work as you expect. You need this additional "guarantee" because every RDBMS handles pessimistic locking differently!

🔔 If you use "test-oracle", "test-mysql" or "test-postgresql" profile you have to first set up a corresponding database locally. In every GitHub example you will find bash scripts and docker files for starting Oracle, MySQL or PostgreSQL database!

How To Start Oracle, MySQL Or PostgreSQL locally

Be sure that you have docker up&running. After that run the following bash commands to start the corresponding database and to check that the docker container is working:

cd docker
./db-up.sh
docker ps

Summary

🔔 The source code is available here:

About the author: Andrey Zahariev Stoev

Loves software craftsmanship and systems thinking. Passionate about travel, languages and cultural diversity exploration.

Comments
Join us
Are you creative and passionate about software development? Do you think unconventionally and act with initiative? Do you want to achieve great things within our team?