Groovy / Maven

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.

Radovan Radic

On this guide

In this section

Getting Started

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

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.

What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE (e.g. IntelliJ IDEA)
JDK 21 or greater installed with JAVA_HOME configured appropriately
Docker installed to run Oracle Database with Micronaut Test Resources.

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.

Download and unzip the source

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=groovy \
    --test=spock

Note	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.

Note	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.

Oracle Driver

Add also the Oracle Driver

pom.xml

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

Database Configuration

And the database configuration:

groovy/src/main/resources/application.properties

datasources.default.schema-generate=CREATE_DROP
datasources.default.driver-class-name=oracle.jdbc.OracleDriver 
datasources.default.db-type=oracle 
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.

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:

PRIORITY_TXNS_HIGH_WAIT_TARGET sets how many seconds a HIGH transaction waits before Oracle rolls back a lower-priority blocker.
PRIORITY_TXNS_MEDIUM_WAIT_TARGET does the same for MEDIUM transactions.
PRIORITY_TXNS_MODE set to ROLLBACK enables the rollback. The other modes only track or report the waits.

See Oracle’s Managing Transactions documentation for details.

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

groovy/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
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.
-- 
ALTER SESSION SET CONTAINER = FREEPDB1;

-- 
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;

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.

Tip	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.

Inventory Item

Create an entity for the inventory item:

groovy/src/main/groovy/example/micronaut/InventoryItem.groovy

imports

package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.serde.annotation.Serdeable

@CompileStatic
@Serdeable
@MappedEntity('inventory_item')
class InventoryItem {

    @Id
    Long id

    String name

    int availableQuantity

    Status status

    InventoryItem() {
    }

    InventoryItem(Long id, String name, int availableQuantity, Status status) {
        this.id = id
        this.name = name
        this.availableQuantity = availableQuantity
        this.status = status
    }

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

Create the item status:

groovy/src/main/groovy/example/micronaut/Status.groovy

imports

package example.micronaut

enum Status {
    AVAILABLE,
    RECONCILED,
    CHECKED_OUT
}

Repository

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

groovy/src/main/groovy/example/micronaut/InventoryItemRepository.groovy

imports

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

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

    Optional<InventoryItem> findByIdForUpdate(Long id) // 
}

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.

Transactions with Different Priorities

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

groovy/src/main/groovy/example/micronaut/InventoryService.groovy

imports

package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.transaction.annotation.OracleTransactional
import io.micronaut.transaction.annotation.Transactional
import jakarta.inject.Singleton

import java.time.Duration

@CompileStatic
@Singleton
class InventoryService {

    static final long DEMO_ITEM_ID = 1L

    private final InventoryItemRepository inventoryItemRepository

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

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

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

    @OracleTransactional(priority = OracleTransactional.Priority.LOW) // 
    InventoryItem reconcile(Duration countDuration) {
        InventoryItem item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow() // 
        countStock(countDuration) // 
        inventoryItemRepository.update(item.withStatus(Status.RECONCILED)) // 
    }

    @OracleTransactional(priority = OracleTransactional.Priority.HIGH) // 
    InventoryItem checkout() {
        InventoryItem item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow() // 
        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.

Controller

Expose the operations as HTTP endpoints:

groovy/src/main/groovy/example/micronaut/InventoryController.groovy

imports

package example.micronaut

import groovy.transform.CompileStatic
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

@CompileStatic
@ExecuteOn(TaskExecutors.BLOCKING) // 
@Controller('/inventory')
class InventoryController {

    private final InventoryService inventoryService

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

    @Get
    InventoryItem item() {
        inventoryService.find()
    }

    @Post('/reset')
    InventoryItem reset() {
        inventoryService.reset()
    }

    @Post('/reconcile')
    InventoryItem reconcile(@QueryValue(defaultValue = '20') @Min(1L) @Max(120L) int countSeconds) { // 
        inventoryService.reconcile(Duration.ofSeconds(countSeconds))
    }

    @Post('/checkout')
    InventoryItem checkout() {
        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.

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:

groovy/src/main/groovy/example/micronaut/TransactionPriorityExceptionHandler.groovy

imports

package example.micronaut

import groovy.transform.CompileStatic
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

@CompileStatic
@Produces
@Singleton
class TransactionPriorityExceptionHandler
        implements ExceptionHandler<OracleTransactionPriorityException, HttpResponse<?>> { // 

    private final ErrorResponseProcessor<?> errorResponseProcessor

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

    @Override
    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') // 
                .build()
        errorResponseProcessor.processResponse(errorContext, HttpResponse.status(HttpStatus.CONFLICT)) // 
    }
}

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.

Test

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

groovy/src/test/groovy/example/micronaut/InventoryControllerSpec.groovy

imports

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.spock.annotation.MicronautTest
import io.micronaut.transaction.TransactionOperations
import jakarta.inject.Inject
import spock.lang.Specification

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

@MicronautTest(transactional = false) // 
class InventoryControllerSpec extends Specification {

    private static final int ORA_RESOURCE_BUSY = 54

    @Inject
    @Client('/')
    HttpClient httpClient

    @Inject
    TransactionOperations<Connection> transactionOperations

    BlockingHttpClient client

    void setup() {
        client = httpClient.toBlocking()
        client.retrieve(HttpRequest.POST('/inventory/reset', null), InventoryItem)
    }

    void 'reconciliation commits without contention'() {
        when:
        InventoryItem item = client.retrieve(HttpRequest.POST('/inventory/reconcile?countSeconds=1', null), InventoryItem)

        then:
        item.status == Status.RECONCILED
        item.availableQuantity == 1
    }

    void 'high priority checkout rolls back low priority reconciliation'() {
        given:
        CompletableFuture<HttpClientResponseException> reconciliation = CompletableFuture.supplyAsync {
            try {
                client.exchange(HttpRequest.POST('/inventory/reconcile?countSeconds=8', null)) // 
                return null
            } catch (HttpClientResponseException e) {
                return e
            }
        }
        awaitItemLocked() // 

        when:
        InventoryItem checkedOut = client.retrieve(HttpRequest.POST('/inventory/checkout', null), InventoryItem) // 

        then:
        checkedOut.status == Status.CHECKED_OUT

        when:
        HttpClientResponseException rolledBack = reconciliation.get(15, TimeUnit.SECONDS)

        then:
        rolledBack != null
        rolledBack.status == HttpStatus.CONFLICT // 
        rolledBack.response.getBody(String).orElse('')
                .contains('Oracle rolled back this operation in favor of a higher-priority transaction') // 

        when:
        InventoryItem item = client.retrieve(HttpRequest.GET('/inventory'), InventoryItem)

        then:
        item.status == Status.CHECKED_OUT // 
        item.availableQuantity == 0
    }

    void 'reconciliation rejects invalid count duration'() {
        when:
        client.exchange(HttpRequest.POST('/inventory/reconcile?countSeconds=0', null))

        then:
        HttpClientResponseException e = thrown()
        e.status == HttpStatus.BAD_REQUEST // 
    }

    private void awaitItemLocked() {
        long deadline = System.nanoTime() + Duration.ofSeconds(10).toNanos()
        while (System.nanoTime() < deadline) {
            if (isItemLocked()) {
                return
            }
            Thread.sleep(100)
        }
        throw new AssertionError('The reconciliation did not lock the inventory item')
    }

    private boolean isItemLocked() {
        transactionOperations.executeWrite { status ->
            String sql = 'SELECT id FROM inventory_item WHERE id = ? FOR UPDATE NOWAIT'
            PreparedStatement statement = status.connection.prepareStatement(sql)
            try {
                statement.setLong(1, InventoryService.DEMO_ITEM_ID)
                statement.executeQuery().close()
                return false
            } catch (SQLException e) {
                if (e.errorCode == ORA_RESOURCE_BUSY) {
                    return true
                }
                throw new IllegalStateException(e)
            } finally {
                statement.close()
            }
        }
    }
}

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.

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.

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.

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

Next Steps

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

License

Note	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).