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

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=gradle \
    --lang=kotlin \
    --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

build.gradle
runtimeOnly("com.oracle.database.jdbc:ojdbc11")

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/kotlin/example/micronaut/InventoryItem.kt
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")
data class InventoryItem(
    @field:Id val id: Long,
    val name: String,
    val availableQuantity: Int,
    val status: Status
)

Create the item status:

src/main/kotlin/example/micronaut/Status.kt
package example.micronaut

enum class Status {
    AVAILABLE,
    RECONCILED,
    CHECKED_OUT
}

7. Repository

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

src/main/kotlin/example/micronaut/InventoryItemRepository.kt
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)
interface InventoryItemRepository : CrudRepository<InventoryItem, Long> {

    fun findByIdForUpdate(id: Long): Optional<InventoryItem> (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/kotlin/example/micronaut/InventoryService.kt
package example.micronaut

import io.micronaut.transaction.annotation.OracleTransactional
import io.micronaut.transaction.annotation.Transactional
import jakarta.inject.Singleton
import java.time.Duration

@Singleton
open class InventoryService(private val inventoryItemRepository: InventoryItemRepository) {

    @Transactional
    open fun reset(): InventoryItem {
        val item = InventoryItem(DEMO_ITEM_ID, "Last available item", 1, Status.AVAILABLE)
        return if (inventoryItemRepository.existsById(DEMO_ITEM_ID)) {
            inventoryItemRepository.update(item)
        } else {
            inventoryItemRepository.save(item)
        }
    }

    @Transactional(readOnly = true)
    open fun find(): InventoryItem = inventoryItemRepository.findById(DEMO_ITEM_ID).orElseThrow()

    @OracleTransactional(priority = OracleTransactional.Priority.LOW) (1)
    open fun reconcile(countDuration: Duration): InventoryItem {
        val item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow() (2)
        countStock(countDuration) (3)
        return inventoryItemRepository.update(item.copy(status = Status.RECONCILED)) (4)
    }

    @OracleTransactional(priority = OracleTransactional.Priority.HIGH) (5)
    open fun checkout(): InventoryItem {
        val item = inventoryItemRepository.findByIdForUpdate(DEMO_ITEM_ID).orElseThrow() (6)
        return inventoryItemRepository.update(item.copy(availableQuantity = 0, status = Status.CHECKED_OUT))
    }

    private fun countStock(countDuration: Duration) {
        try {
            Thread.sleep(countDuration) // Simulates a slow count in an external system.
        } catch (e: InterruptedException) {
            Thread.currentThread().interrupt()
            throw IllegalStateException("The stock count was interrupted", e)
        }
    }

    companion object {
        const val DEMO_ITEM_ID = 1L
    }
}
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/kotlin/example/micronaut/InventoryController.kt
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")
open class InventoryController(private val inventoryService: InventoryService) {

    @Get
    open fun item(): InventoryItem = inventoryService.find()

    @Post("/reset")
    open fun reset(): InventoryItem = inventoryService.reset()

    @Post("/reconcile")
    open fun reconcile(@QueryValue(defaultValue = "20") @Min(1) @Max(120) countSeconds: Int): InventoryItem = (2)
        inventoryService.reconcile(Duration.ofSeconds(countSeconds.toLong()))

    @Post("/checkout")
    open fun checkout(): InventoryItem = 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/kotlin/example/micronaut/TransactionPriorityExceptionHandler.kt
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
class TransactionPriorityExceptionHandler(
    private val errorResponseProcessor: ErrorResponseProcessor<*>
) : ExceptionHandler<OracleTransactionPriorityException, HttpResponse<*>> { (1)

    override fun handle(request: HttpRequest<*>, exception: OracleTransactionPriorityException): HttpResponse<*> {
        val 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<Any>(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/kotlin/example/micronaut/InventoryControllerTest.kt
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.Assertions.assertEquals
import org.junit.jupiter.api.Assertions.assertThrows
import org.junit.jupiter.api.Assertions.assertTrue
import org.junit.jupiter.api.Assertions.fail
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.Test
import java.sql.Connection
import java.sql.SQLException
import java.time.Duration
import java.util.concurrent.CompletableFuture
import java.util.concurrent.TimeUnit

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

    @Inject
    @field:Client("/")
    lateinit var httpClient: HttpClient

    @Inject
    lateinit var transactionOperations: TransactionOperations<Connection>

    lateinit var client: BlockingHttpClient

    @BeforeEach
    fun reset() {
        client = httpClient.toBlocking()
        client.retrieve(HttpRequest.POST("/inventory/reset", ""), InventoryItem::class.java)
    }

    @Test
    fun reconciliationCommitsWithoutContention() {
        val item = client.retrieve(HttpRequest.POST("/inventory/reconcile?countSeconds=1", ""), InventoryItem::class.java)

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

    @Test
    fun highPriorityCheckoutRollsBackLowPriorityReconciliation() {
        val reconciliation = CompletableFuture.supplyAsync {
            assertThrows(HttpClientResponseException::class.java) {
                client.exchange<Any, Any>(HttpRequest.POST("/inventory/reconcile?countSeconds=8", "")) (2)
            }
        }
        awaitItemLocked() (3)

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

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

        val item = client.retrieve(HttpRequest.GET<Any>("/inventory"), InventoryItem::class.java)
        assertEquals(Status.CHECKED_OUT, item.status) (7)
        assertEquals(0, item.availableQuantity)
    }

    @Test
    fun reconciliationRejectsInvalidCountDuration() {
        val e = assertThrows(HttpClientResponseException::class.java) {
            client.exchange<Any, Any>(HttpRequest.POST("/inventory/reconcile?countSeconds=0", ""))
        }

        assertEquals(HttpStatus.BAD_REQUEST, e.status) (8)
    }

    private fun awaitItemLocked() {
        val deadline = System.nanoTime() + Duration.ofSeconds(10).toNanos()
        while (System.nanoTime() < deadline) {
            if (isItemLocked()) {
                return
            }
            Thread.sleep(100)
        }
        fail<Unit>("The reconciliation did not lock the inventory item")
    }

    private fun isItemLocked(): Boolean = transactionOperations.executeWrite { status ->
        val sql = "SELECT id FROM inventory_item WHERE id = ? FOR UPDATE NOWAIT"
        try {
            status.connection.prepareStatement(sql).use { statement ->
                statement.setLong(1, InventoryService.DEMO_ITEM_ID)
                statement.executeQuery().close()
                false
            }
        } catch (e: SQLException) {
            if (e.errorCode != ORA_RESOURCE_BUSY) {
                throw IllegalStateException(e)
            }
            true
        }
    }

    companion object {
        private const val ORA_RESOURCE_BUSY = 54
    }
}
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:

./gradlew test

Then open build/reports/tests/test/index.html in a browser to see the results.

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