Table of Contents

Prioritize Oracle Transactions with Micronaut Data JDBC

Build an inventory example where an Oracle HIGH-priority checkout can roll back conflicting LOW-priority background work.

Authors: Radovan Radic

Micronaut Version: 5.2.0

1. Getting Started

In this guide, we will create a Micronaut application written in Java.

In this guide, you will build a small inventory application with Micronaut Data JDBC and Oracle Database. A slow stock reconciliation is background work, so it runs with LOW priority. A customer checkout needs the same last item and runs with HIGH priority. When Oracle priority transactions are enabled, Oracle rolls back the reconciliation once the checkout has waited long enough, so the customer does not wait for background work.

You will use @OracleTransactional to declare the priority of each transaction, and handle OracleTransactionPriorityException, which Micronaut Data throws when Oracle rolls back a lower-priority transaction.

2. What you will need

To complete this guide, you will need the following:

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,oracle,serialization-jackson,validation \
    --build=maven \
    --lang=java \
    --test=junit
If you don’t specify the --build argument, Gradle with the Kotlin DSL is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.
If you don’t specify the --test argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

If you use Micronaut Launch, select Micronaut Application as application type and add data-jdbc, oracle, serialization-jackson, and validation features.

If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Diff

Setup IntelliJ IDEA to develop Micronaut Applications.

4.1. Oracle Driver

Add also the Oracle Driver

pom.xml
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <scope>runtime</scope>
</dependency>

4.2. Database Configuration

And the database configuration:

src/main/resources/application.properties
datasources.default.schema-generate=CREATE_DROP
(1)
datasources.default.driver-class-name=oracle.jdbc.OracleDriver
(2)
datasources.default.db-type=oracle
(3)
datasources.default.dialect=ORACLE
1 Use Oracle driver.
2 In order for the database to be properly detected by Micronaut Test Resources.
3 Configure the Oracle dialect.

5. Configure Oracle Priority Transactions

Transaction priority is an Oracle Database feature. A database administrator decides how long a higher-priority transaction waits for a lower-priority one that holds a row lock, and whether Oracle then rolls back the blocker:

See Oracle’s Managing Transactions documentation for details.

For this guide, Micronaut Test Resources copies a startup script into the Oracle Database Free container:

src/main/resources/application.properties
test-resources.containers.oracle.image-name=gvenzl/oracle-free
test-resources.containers.oracle.image-tag=latest
test-resources.containers.oracle.startup-timeout=600s
(1)
test-resources.containers.oracle.copy-to-container[0].src/main/resources/oracle/priority-txns.sql=/container-entrypoint-startdb.d/priority-txns.sql
1 Copy the script to the directory that the container runs on every database startup.
src/main/resources/oracle/priority-txns.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- Set the CDB defaults first. This also covers a startup where the PDB has
-- not yet been opened or has no explicit parameter override.
ALTER SYSTEM SET priority_txns_high_wait_target=5 SCOPE=MEMORY;
ALTER SYSTEM SET priority_txns_medium_wait_target=10 SCOPE=MEMORY;
ALTER SYSTEM SET priority_txns_mode=ROLLBACK SCOPE=MEMORY;

-- The application connects to the default Oracle Free PDB. Set the parameters
-- in that PDB so the JDBC sessions used by the demo inherit them.
(1)
ALTER SESSION SET CONTAINER = FREEPDB1;

(2)
ALTER SYSTEM SET priority_txns_high_wait_target=5 SCOPE=MEMORY;
ALTER SYSTEM SET priority_txns_medium_wait_target=10 SCOPE=MEMORY;
(3)
ALTER SYSTEM SET priority_txns_mode=ROLLBACK SCOPE=MEMORY;

EXIT;
1 Set the defaults for the container database, then switch to FREEPDB1, the pluggable database that the application connects to.
2 The container runs the script as SYSDBA, so the application user does not need privileges to change instance parameters.
3 SCOPE=MEMORY keeps the settings for the lifetime of the throwaway container.

In other environments, a database administrator applies the same ALTER SYSTEM statements.

If you change the script while the Test Resources service is running, stop it with ./gradlew stopTestResourcesService so the next run starts a new container.

6. Inventory Item

Create an entity for the inventory item:

src/main/java/example/micronaut/InventoryItem.java
package example.micronaut;

import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.serde.annotation.Serdeable;

@Serdeable
@MappedEntity("inventory_item")
public record InventoryItem(
    @Id Long id,
    String name,
    int availableQuantity,
    Status status
) {

    InventoryItem withStatus(Status status) {
        return new InventoryItem(id, name, availableQuantity, status);
    }
}

Create the item status:

src/main/java/example/micronaut/Status.java
package example.micronaut;

public enum Status {
    AVAILABLE,
    RECONCILED,
    CHECKED_OUT
}

7. Repository

Create a repository that locks the item while a transaction works with it:

src/main/java/example/micronaut/InventoryItemRepository.java
package example.micronaut;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.Optional;

@JdbcRepository(dialect = Dialect.ORACLE) (1)
public interface InventoryItemRepository extends CrudRepository<InventoryItem, Long> {

    Optional<InventoryItem> findByIdForUpdate(Long id); (2)
}
1 Micronaut Data generates SQL for the Oracle dialect at compilation time.
2 The ForUpdate suffix generates a SELECT …​ FOR UPDATE query. The row stays locked until the surrounding transaction ends.

8. Transactions with Different Priorities

Create a service with a LOW-priority reconciliation and a HIGH-priority checkout:

src/main/java/example/micronaut/InventoryService.java
package example.micronaut;

import io.micronaut.transaction.annotation.OracleTransactional;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Singleton;

import java.time.Duration;

@Singleton
public class InventoryService {

    static final long DEMO_ITEM_ID = 1L;

    private final InventoryItemRepository inventoryItemRepository;

    InventoryService(InventoryItemRepository inventoryItemRepository) {
        this.inventoryItemRepository = inventoryItemRepository;
    }

    @Transactional
    public InventoryItem reset() {
        InventoryItem item = new InventoryItem(DEMO_ITEM_ID, "Last available item", 1, Status.AVAILABLE);
        return inventoryItemRepository.existsById(DEMO_ITEM_ID)
            ? inventoryItemRepository.update(item)
            : inventoryItemRepository.save(item);
    }

    @Transactional(readOnly = true)
    public InventoryItem find() {
        return inventoryItemRepository.findById(DEMO_ITEM_ID).orElseThrow();
    }

    @OracleTransactional(priority = OracleTransactional.Priority.LOW) (1)
    public InventoryItem reconcile(Duration countDuration) {
        InventoryItem item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow(); (2)
        countStock(countDuration); (3)
        return inventoryItemRepository.update(item.withStatus(Status.RECONCILED)); (4)
    }

    @OracleTransactional(priority = OracleTransactional.Priority.HIGH) (5)
    public InventoryItem checkout() {
        InventoryItem item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow(); (6)
        return inventoryItemRepository.update(new InventoryItem(item.id(), item.name(), 0, Status.CHECKED_OUT));
    }

    private static void countStock(Duration countDuration) {
        try {
            Thread.sleep(countDuration); // Simulates a slow count in an external system.
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            throw new IllegalStateException("The stock count was interrupted", e);
        }
    }
}
1 @OracleTransactional starts a transaction and sets the Oracle transaction priority for it. Micronaut Data restores the session priority when the transaction completes, so pooled connections do not keep it.
2 Lock the item. The lock alone makes the reconciliation a blocker for any other transaction that needs the row.
3 Simulate a slow count in an external system while the row is locked.
4 If Oracle rolled back the reconciliation while it was counting, this write is the first statement to find out. Micronaut Data rolls back the transaction and throws OracleTransactionPriorityException.
5 Checkout is the critical operation, so it runs with HIGH priority. HIGH is also the default for @OracleTransactional.
6 Checkout waits for the row lock. After the configured wait target, Oracle rolls back the reconciliation and checkout acquires the lock.

A priority rollback is a failed transaction; it is not retried automatically. The reconciliation read the item before checkout changed it, so replaying the same work could overwrite the checkout. To retry, run the operation in a new transaction that reads the item again and decides whether the work is still needed.

9. Controller

Expose the operations as HTTP endpoints:

src/main/java/example/micronaut/InventoryController.java
package example.micronaut;

import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.Post;
import io.micronaut.http.annotation.QueryValue;
import io.micronaut.scheduling.TaskExecutors;
import io.micronaut.scheduling.annotation.ExecuteOn;
import jakarta.validation.constraints.Max;
import jakarta.validation.constraints.Min;

import java.time.Duration;

@ExecuteOn(TaskExecutors.BLOCKING) (1)
@Controller("/inventory")
public class InventoryController {

    private final InventoryService inventoryService;

    InventoryController(InventoryService inventoryService) {
        this.inventoryService = inventoryService;
    }

    @Get
    public InventoryItem item() {
        return inventoryService.find();
    }

    @Post("/reset")
    public InventoryItem reset() {
        return inventoryService.reset();
    }

    @Post("/reconcile")
    public InventoryItem reconcile(@QueryValue(defaultValue = "20") @Min(1) @Max(120) int countSeconds) { (2)
        return inventoryService.reconcile(Duration.ofSeconds(countSeconds));
    }

    @Post("/checkout")
    public InventoryItem checkout() {
        return inventoryService.checkout();
    }
}
1 The operations block while they wait for row locks, so run them on the blocking executor instead of the event loop.
2 The reconciliation request stays open while its transaction holds the lock, so you can send a checkout from another request.

10. Handle a Priority Rollback

Without a handler, an OracleTransactionPriorityException results in a 500 Internal Server Error response. A priority rollback is an expected outcome of contention, so report it as a conflict instead:

src/main/java/example/micronaut/TransactionPriorityExceptionHandler.java
package example.micronaut;

import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.annotation.Produces;
import io.micronaut.http.server.exceptions.ExceptionHandler;
import io.micronaut.http.server.exceptions.response.ErrorContext;
import io.micronaut.http.server.exceptions.response.ErrorResponseProcessor;
import io.micronaut.transaction.exceptions.OracleTransactionPriorityException;
import jakarta.inject.Singleton;

@Produces
@Singleton
public class TransactionPriorityExceptionHandler
    implements ExceptionHandler<OracleTransactionPriorityException, HttpResponse<?>> { (1)

    private final ErrorResponseProcessor<?> errorResponseProcessor;

    public TransactionPriorityExceptionHandler(ErrorResponseProcessor<?> errorResponseProcessor) {
        this.errorResponseProcessor = errorResponseProcessor;
    }

    @Override
    public HttpResponse<?> handle(HttpRequest request, OracleTransactionPriorityException exception) {
        ErrorContext errorContext = ErrorContext.builder(request)
            .cause(exception)
            .errorMessage("Oracle rolled back this operation in favor of a higher-priority transaction") (2)
            .build();
        return errorResponseProcessor.processResponse(errorContext, HttpResponse.status(HttpStatus.CONFLICT)); (3)
    }
}
1 Handle OracleTransactionPriorityException. Micronaut Data throws it for both Oracle errors that report a priority rollback: ORA-63300 for the statement that was running when Oracle rolled back the transaction, and ORA-63302 for a later statement or commit.
2 Tell the client why the operation failed.
3 Respond with 409 Conflict, so the client can re-read the item and retry if the operation is still needed.

11. Test

Add a test that runs a reconciliation and a checkout against the same item:

src/test/java/example/micronaut/InventoryControllerTest.java
package example.micronaut;

import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.client.BlockingHttpClient;
import io.micronaut.http.client.HttpClient;
import io.micronaut.http.client.annotation.Client;
import io.micronaut.http.client.exceptions.HttpClientResponseException;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import io.micronaut.transaction.TransactionOperations;
import jakarta.inject.Inject;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Duration;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.TimeUnit;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;

@MicronautTest(transactional = false) (1)
class InventoryControllerTest {

    private static final int ORA_RESOURCE_BUSY = 54;

    @Inject
    @Client("/")
    HttpClient httpClient;

    @Inject
    TransactionOperations<Connection> transactionOperations;

    BlockingHttpClient client;

    @BeforeEach
    void reset() {
        client = httpClient.toBlocking();
        client.retrieve(HttpRequest.POST("/inventory/reset", null), InventoryItem.class);
    }

    @Test
    void reconciliationCommitsWithoutContention() {
        InventoryItem item = client.retrieve(HttpRequest.POST("/inventory/reconcile?countSeconds=1", null), InventoryItem.class);

        assertEquals(Status.RECONCILED, item.status());
        assertEquals(1, item.availableQuantity());
    }

    @Test
    void highPriorityCheckoutRollsBackLowPriorityReconciliation() throws Exception {
        CompletableFuture<HttpClientResponseException> reconciliation = CompletableFuture.supplyAsync(() ->
            assertThrows(HttpClientResponseException.class, () ->
                client.exchange(HttpRequest.POST("/inventory/reconcile?countSeconds=8", null)))); (2)
        awaitItemLocked(); (3)

        InventoryItem checkedOut = client.retrieve(HttpRequest.POST("/inventory/checkout", null), InventoryItem.class); (4)
        assertEquals(Status.CHECKED_OUT, checkedOut.status());

        HttpClientResponseException rolledBack = reconciliation.get(15, TimeUnit.SECONDS);
        assertEquals(HttpStatus.CONFLICT, rolledBack.getStatus()); (5)
        assertTrue(rolledBack.getResponse().getBody(String.class).orElse("")
            .contains("Oracle rolled back this operation in favor of a higher-priority transaction")); (6)

        InventoryItem item = client.retrieve(HttpRequest.GET("/inventory"), InventoryItem.class);
        assertEquals(Status.CHECKED_OUT, item.status()); (7)
        assertEquals(0, item.availableQuantity());
    }

    @Test
    void reconciliationRejectsInvalidCountDuration() {
        HttpClientResponseException e = assertThrows(HttpClientResponseException.class, () ->
            client.exchange(HttpRequest.POST("/inventory/reconcile?countSeconds=0", null)));

        assertEquals(HttpStatus.BAD_REQUEST, e.getStatus()); (8)
    }

    private void awaitItemLocked() throws InterruptedException {
        long deadline = System.nanoTime() + Duration.ofSeconds(10).toNanos();
        while (System.nanoTime() < deadline) {
            if (isItemLocked()) {
                return;
            }
            Thread.sleep(100);
        }
        fail("The reconciliation did not lock the inventory item");
    }

    private boolean isItemLocked() {
        return transactionOperations.executeWrite(status -> {
            String sql = "SELECT id FROM inventory_item WHERE id = ? FOR UPDATE NOWAIT";
            try (PreparedStatement statement = status.getConnection().prepareStatement(sql)) {
                statement.setLong(1, InventoryService.DEMO_ITEM_ID);
                statement.executeQuery().close();
                return false;
            } catch (SQLException e) {
                if (e.getErrorCode() == ORA_RESOURCE_BUSY) {
                    return true;
                }
                throw new IllegalStateException(e);
            }
        });
    }
}
1 Disable the test transaction so that each request runs its own transaction.
2 Send the reconciliation from another thread, because the request stays open while it holds the lock. The count takes eight seconds, longer than the five-second HIGH wait target in priority-txns.sql.
3 Wait until the reconciliation holds the row lock. The check uses NOWAIT, so it fails immediately with ORA-00054 instead of waiting for the lock.
4 Checkout waits for the lock, and Oracle rolls back the reconciliation after five seconds.
5 The reconciliation request fails with 409 Conflict from the exception handler.
6 The response body contains the message from the exception handler.
7 Only the checkout is committed.
8 Bean Validation rejects a countSeconds value outside the allowed range with 400 Bad Request, before any transaction starts.

12. Testing the Application

To run the tests:

./mvnw test

When you run the tests, Micronaut Test Resources starts an Oracle Database Free container and runs priority-txns.sql before the application connects.

13. Run the Application

Start the application with ./gradlew run or ./mvnw mn:run, and reset the item:

curl -X POST http://localhost:8080/inventory/reset

In one terminal, start a reconciliation. The request stays open for 20 seconds while it holds the item lock:

curl -i -X POST http://localhost:8080/inventory/reconcile

Within those 20 seconds, check out the item from a second terminal:

curl -X POST http://localhost:8080/inventory/checkout

After about five seconds, the checkout responds with the item in the CHECKED_OUT status, and the reconciliation responds with 409 Conflict.

If you start the checkout without a running reconciliation, it completes immediately. If you run only the reconciliation, it commits after the count and responds with the RECONCILED status.

13.1. Micronaut Test Resources Goals

Micronaut Test Resources aims to achieve the following goals:

  • zero-configuration: without adding any configuration, test resources should be spawned and the application configured to use them. Configuration is only required for advanced use cases.

  • classpath isolation: use of test resources shouldn’t leak into your application classpath, nor your test classpath

  • compatible with GraalVM native: if you build a native binary, or run tests in native mode, test resources should be available

  • easy to use: the Micronaut build plugins for Gradle and Maven should handle the complexity of figuring out the dependencies for you

  • extensible: you can implement your own test resources, in case the built-in ones do not cover your use case

  • technology agnostic: while lots of test resources use Testcontainers under the hood, you can use any other technology to create resources

14. Next Steps

Read more about transactions in Micronaut Data and Oracle priority transactions.

15. License

All guides are released with an Apache License 2.0 for the code and a Creative Commons Attribution 4.0 license for the writing and media (images).