In a previous article series on Spring Data JPA and CockroachDB, we look into different methods to avoid lengthy transaction execution times. Until recently, however, there's not been any way to specify the transaction execution timeout in CockroachDB only at the statement level.

This has changed since CockroachDB v23.1 where a new session variable for transaction timeouts was introduced, unsurprisingly called transaction_timeout:

New in v23.1: Aborts an explicit transaction when it runs longer than the configured duration. Stored in milliseconds; can be expressed in milliseconds or as an INTERVAL.

Overview

Transaction timeouts are helpful if you need to set a fixed upper limit for how long to wait for an explicit transaction to complete. If a transaction is not completed within that timeframe it's aborted and then you that any provisional writes did not complete.

In contrast, if you just wait for an arbitrary amount of time and then interrupt the calling thread, then you have an ambiguous result where you can't tell if an operation took place or not since the commit could have been completed or rolled back just before the cancellation. Ambiguous results for non-idempotent operations are typically not a good thing for safety.

Now let's see how to hook up transaction timeouts in a fully transparent way using Spring's @Transactional annotation and AspectJ. Similar to how we can deal with transaction retries.

Source Code

The code for this article is available on GitHub.

AOP Timeout Solution

We are going to set the attributes using AOP and AspectJ, which is a core concept in Spring Boot.

A small recap on basic AOP terminology:

• Aspect - An orthogonal cross-cutting concern that you wrap in a contained module or aspect. Like retries, logging, security or in our case setting session variables.

• Joinpoint - Points in the application code where to plugin the aspect, such as method execution or the handling of an exception.

• Pointcut - One or more join points where advice should be executed, often using pointcut expressions.

• Advice - The action to be performed either before or after method execution, akin to an interceptor.

To set setting attributes, we create a TransactionAttributesAspect with an around-advice:

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.annotation.Transactional;

@Aspect
@Order(Ordered.LOWEST_PRECEDENCE - 2)
public class TransactionAttributesAspect {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Pointcut("execution(public * *(..)) "
            + "&& @annotation(transactional)")
    public void anyTransactionalOperation(Transactional transactional) {
    }

    @Around(value = "anyTransactionalOperation(transactional)", argNames = "pjp,transactional")
    public Object doAroundTransactionalMethod(ProceedingJoinPoint pjp, Transactional transactional) throws Throwable {
        Assert.isTrue(TransactionSynchronizationManager.isActualTransactionActive(), "Explicit transaction required");
        applyVariables(transactional);
        return pjp.proceed();
    }

    private void applyVariables(Transactional transactional) {
        if (transactional.timeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
            jdbcTemplate.update("SET transaction_timeout=?", transactional.timeout() * 1000);
        }

        if (transactional.readOnly()) {
            jdbcTemplate.execute("SET transaction_read_only=true");
        }
    }
}

This weaves in the doAroundTransactionalMethod advice at runtime on all public methods annotated with Spring's @Transactional annotation. This is pretty much what the pointcut expression says:

@Pointcut("execution(public * *(..)) && @annotation(transactional))

Lastly, we look at the annotation properties and use a JDBC template to set the appropriate variables while assuming there's an open transaction in scope.

if (transactional.timeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
   jdbcTemplate.update("SET transaction_timeout=?", transactional.timeout() * 1000);
}

Testing Timeouts

To test this in action, let's create a simple service and a few repositories:

@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private ProductRepository productRepository;

    @Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true)
    public Product findProduct(String sku) {
        return productRepository.findBySku(sku)
                .orElseThrow(() -> new ObjectRetrievalFailureException(Product.class, sku));
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW, timeout = 5)
    public void placeOrderWithTimeout(Order order, long delayMillis) {
        placeOrderAndUpdateInventory(order);

        try {
            logger.info("Entering sleep for " + delayMillis);
            Thread.sleep(delayMillis);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        } finally {
            logger.info("Exited sleep for " + delayMillis);
        }
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void placeOrderWithoutTimeout(Order order) {
        placeOrderAndUpdateInventory(order);
    }

    private void placeOrderAndUpdateInventory(Order order) {
        Assert.isTrue(!TransactionSynchronizationManager.isCurrentTransactionReadOnly(), "Read-only");
        Assert.isTrue(TransactionSynchronizationManager.isActualTransactionActive(), "No tx");

        // Update product inventories
        order.getOrderItems().forEach(orderItem -> {
            Product product = orderItem.getProduct();
            product.addInventoryQuantity(-orderItem.getQuantity());
            productRepository.save(product); // product is in detached state
        });
        order.setStatus(ShipmentStatus.confirmed);

        orderRepository.save(order);
    }
}

In the placeOrderWithTimeout method, there's a fake delay that can last longer than the configured timeout to trigger an abort. Let's verify this in an integration test:

public class TimeoutsTest extends AbstractIntegrationTest {
    @Autowired
    private OrderService orderService;

    @Autowired
    private TestSetup testSetup;

    @BeforeAll
    public void setupTest() {
        testSetup.setupTestData();
    }

    @org.junit.jupiter.api.Order(1)
    @Test
    public void whenCreatingOrderWithTimeoutThatExpires_thenExpectRollback() {
        Product p1 = orderService.findProduct("p1");
        int inventory = p1.getInventory();

        JpaSystemException ex = Assertions.assertThrows(JpaSystemException.class, () -> {
            orderService.placeOrderWithTimeout(Order.builder()
                            .andOrderItem()
                            .withProduct(p1)
                            .withQuantity(1)
                            .withUnitPrice(p1.getPrice())
                            .then()
                            .build(),
                    7000);
        });

        Assertions.assertEquals("transaction timeout expired", ex.getMessage());
        Assertions.assertEquals(inventory, orderService.findProduct("p1").getInventory());

        logger.info("Exception thrown", ex);
    }

    @org.junit.jupiter.api.Order(2)
    @Test
    public void whenCreatingOrderWithTimeout_thenExpectCommit() {
        Product p1 = orderService.findProduct("p1");
        int inventory = p1.getInventory();

        orderService.placeOrderWithTimeout(Order.builder()
                        .andOrderItem()
                        .withProduct(p1)
                        .withQuantity(1)
                        .withUnitPrice(p1.getPrice())
                        .then()
                        .build(),
                2000);

        Assertions.assertEquals(inventory - 1, orderService.findProduct("p1").getInventory());
    }

    @org.junit.jupiter.api.Order(3)
    @Test
    public void whenCreatingOrderWithoutTimeout_thenExpectCommit() {
        Product p1 = orderService.findProduct("p1");
        int inventory = p1.getInventory();

        orderService.placeOrderWithoutTimeout(Order.builder()
                .andOrderItem()
                .withProduct(p1)
                .withQuantity(1)
                .withUnitPrice(p1.getPrice())
                .then()
                .build());

        Assertions.assertEquals(inventory - 1, orderService.findProduct("p1").getInventory());
    }
}

In this example if the transaction time out, it throws JpaSystemException.

Conclusion

This article explains how to use the new transaction_timeout session variable in CockroachDB v23.1 to set a fixed upper limit for how long to wait for an explicit transaction to complete. It also provides an example of a service and repositories to test the timeout in an integration test, which verifies that when the timeout expires, the transaction is rolled back and the inventory remains unchanged.