Table of Contents

Oracle Lock-Free Reservation with Micronaut Data JDBC

Learn how to map Oracle reservable columns and update them concurrently with Micronaut Data JDBC reservation methods.

Authors: Radovan Radic

Micronaut Version: 5.2.0

1. Getting Started

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

In this guide, you will build a small account service with Micronaut Data JDBC and Oracle Database lock-free reservations. An account has a balance, and many deposits and withdrawals can run concurrently against the same account. You will map the balance to an Oracle RESERVABLE column with a CHECK constraint, and update it with derived reservation methods.

With lock-free reservation, Oracle does not lock the account row for each update. It journals each reservation, checks it against the column constraints, and applies it when the transaction commits. Concurrent transactions can therefore reserve amounts from the same row without waiting for each other, as long as the constraints hold.

Lock-free reservation requires Oracle Database 23.26.1 or later. Oracle documentation may refer to this release by its 26ai name. Micronaut Data does not detect the database version, so use @Reservable only with a compatible database; otherwise Oracle rejects the generated DDL and DML.

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

Micronaut Test Resources starts Oracle Database Free, which supports lock-free reservation, for tests and development:

src/main/resources/application.properties
(1)
test-resources.containers.oracle.image-name=gvenzl/oracle-free
test-resources.containers.oracle.image-tag=latest
test-resources.containers.oracle.startup-timeout=600s
1 Use the latest full Oracle Database Free image and allow the container enough time to start.

5. Account

Create an Account entity:

src/main/groovy/example/micronaut/domain/Account.groovy
package example.micronaut.domain

import groovy.transform.CompileStatic
import io.micronaut.core.annotation.Nullable
import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.data.annotation.Reservable
import io.micronaut.serde.annotation.Serdeable
import jakarta.validation.constraints.NotBlank
import jakarta.validation.constraints.PositiveOrZero

@CompileStatic
@Serdeable (1)
@MappedEntity('ACCOUNT') (2)
class Account {
    @Id
    @GeneratedValue
    @Nullable
    Long id (3)

    @NotBlank
    String name (4)

    @Reservable
    @PositiveOrZero
    Long balance (5)

    Account() {
    }

    Account(@Nullable Long id, String name, Long balance) {
        this.id = id
        this.name = name
        this.balance = balance
    }
}
1 Declare @Serdeable so the account can be serialized in HTTP requests and responses.
2 Map the entity to the ACCOUNT table.
3 The ID is generated by the database.
4 name is an ordinary column. Generated save and update operations can change it.
5 @Reservable generates a RESERVABLE column. @PositiveOrZero generates a column CHECK constraint, so Oracle rejects any reservation that would make the balance negative. A table can have up to 10 reservable columns.

Schema generation creates the table with the reservable column and its check constraint:

CREATE TABLE "ACCOUNT" (
    "ID" NUMBER(19) NOT NULL PRIMARY KEY,
    "NAME" VARCHAR(255) NOT NULL,
    "BALANCE" NUMBER(19) RESERVABLE CONSTRAINT "CK_ACCOUNT_BALANCE_GE_0" CHECK ("BALANCE" >= 0) NOT NULL
)

Other supported validation annotations are @Positive, @Negative, @NegativeOrZero, @Min, @Max, @DecimalMin, and @DecimalMax. Validation annotations generate constraints only on @Reservable properties.

6. Repository

Create an Oracle JDBC repository:

src/main/groovy/example/micronaut/AccountRepository.groovy
package example.micronaut

import example.micronaut.domain.Account
import io.micronaut.data.annotation.Id
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) (1)
interface AccountRepository extends CrudRepository<Account, Long> { (2)

    /**
     *
     * @param id Account unique identifier
     * @param balance balance increment
     * @return rows updated
     */
    long reserveIncrementBalance(@Id Long id, Long balance) (3)

    /**
     *
     * @param id Account unique identifier
     * @param balance balance decrement
     * @return rows updated
     */
    long reserveDecrementBalance(@Id Long id, Long balance) (4)
}
1 Micronaut Data generates SQL for the Oracle dialect at compilation time.
2 Account has an ordinary updatable property, name, so the repository can extend CrudRepository.
3 A derived reservation method starts with reserve, followed by Increment<Property> or Decrement<Property> operations. The @Id parameter identifies the row, and the delta parameter has the name of its reservable property. A deposit increments the balance. The long return value is the number of updated rows, not the new balance: Oracle applies the reservation when the transaction commits, so the new value is not available to the statement. Reservation methods can also return void.
4 A withdrawal decrements the balance. To update several reservable properties in one statement, join operations with And, for example, reserveIncrementBalanceAndDecrementCredit for an entity with two reservable properties, balance and credit.

The reservation methods generate updates that change the column by a delta, instead of assigning a new value:

UPDATE "ACCOUNT" SET "BALANCE"=("BALANCE" + ?) WHERE ("ID" = ?)
UPDATE "ACCOUNT" SET "BALANCE"=("BALANCE" - ?) WHERE ("ID" = ?)

Oracle does not allow direct assignments to reservable columns, or a mix of reservable and ordinary columns in one update. Micronaut Data therefore leaves balance out of the generated save and update statements, which only change name.

If all updatable properties of an entity are reservable, Micronaut Data cannot generate save or update operations and reports an error at compilation time. Extend GenericRepository instead and declare insert, finder, delete, and reservation methods explicitly.

7. Controller

Expose account creation, deposits, and withdrawals:

src/main/groovy/example/micronaut/AccountController.groovy
package example.micronaut

import example.micronaut.domain.Account
import groovy.transform.CompileStatic
import io.micronaut.http.HttpStatus
import io.micronaut.http.annotation.Body
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.http.annotation.Status
import io.micronaut.http.exceptions.HttpStatusException
import io.micronaut.scheduling.TaskExecutors
import io.micronaut.scheduling.annotation.ExecuteOn

@CompileStatic
@ExecuteOn(TaskExecutors.BLOCKING) (1)
@Controller('/accounts') (2)
class AccountController {

    private final AccountRepository repository

    AccountController(AccountRepository repository) { (3)
        this.repository = repository
    }

    @Post
    @Status(HttpStatus.CREATED)
    Account create(@Body Account account) {
        repository.save(account)
    }

    @Get
    List<Account> findAll() {
        repository.findAll()
    }

    @Post('/{id}/deposit')
    Account deposit(Long id, @QueryValue Long amount) {
        if (repository.reserveIncrementBalance(id, amount) == 0L) { (4)
            throw new HttpStatusException(HttpStatus.NOT_FOUND, 'Account not found')
        }
        repository.findById(id).orElseThrow() (5)
    }

    @Post('/{id}/withdraw')
    Account withdraw(Long id, @QueryValue Long amount) {
        if (repository.reserveDecrementBalance(id, amount) == 0L) {
            throw new HttpStatusException(HttpStatus.NOT_FOUND, 'Account not found')
        }
        repository.findById(id).orElseThrow()
    }
}
1 Repository calls block while they wait for the database, so run them on the blocking executor instead of the event loop.
2 The class is defined as a controller mapped to the path /accounts.
3 Use constructor injection to inject the AccountRepository.
4 The reservation method returns the number of updated rows. Respond with 404 Not Found when there is no account with the given ID.
5 The reservation method runs in its own transaction, which has committed by now, so reading the account returns the new balance. Inside the transaction that made a reservation, a query still returns the previous value.

8. Handle a Constraint Violation

When a reservation would violate a column constraint, Oracle rejects it, and Micronaut Data throws DataIntegrityViolationException. Without a handler, this results in a 500 Internal Server Error response. Report it as a conflict instead:

src/main/groovy/example/micronaut/ReservationExceptionHandler.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.data.exceptions.DataIntegrityViolationException
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 jakarta.inject.Singleton

@CompileStatic
@Produces
@Singleton
class ReservationExceptionHandler
        implements ExceptionHandler<DataIntegrityViolationException, HttpResponse<?>> { (1)

    private final ErrorResponseProcessor<?> errorResponseProcessor

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

    @Override
    HttpResponse<?> handle(HttpRequest request, DataIntegrityViolationException exception) {
        ErrorContext errorContext = ErrorContext.builder(request)
                .cause(exception)
                .errorMessage('The operation violates an account constraint') (2)
                .build()
        errorResponseProcessor.processResponse(errorContext, HttpResponse.status(HttpStatus.CONFLICT)) (3)
    }
}
1 Handle DataIntegrityViolationException, which Micronaut Data throws for integrity constraint failures with JDBC and R2DBC.
2 Tell the client why the operation failed.
3 Respond with 409 Conflict.

DataIntegrityViolationException is a database error. It is different from a Bean Validation ConstraintViolationException, which Micronaut throws before any SQL runs when validation of a method argument fails.

9. Test

Add tests for deposits, withdrawals, and constraint failures, through the repository and over HTTP:

src/test/groovy/example/micronaut/AccountSpec.groovy
package example.micronaut

import example.micronaut.domain.Account
import io.micronaut.data.exceptions.DataIntegrityViolationException
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
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 jakarta.inject.Inject
import spock.lang.Specification

@MicronautTest(transactional = false) (1)
class AccountSpec extends Specification {

    @Inject
    AccountRepository repository

    @Inject
    @Client('/')
    HttpClient httpClient

    void 'deposit increments balance'() {
        given:
        Account account = repository.save(new Account(null, 'Checking', 100L))

        when:
        long updated = repository.reserveIncrementBalance(account.id, 25L) (2)

        then:
        updated == 1L
        Account found = repository.findById(account.id).orElseThrow()
        found.balance == 125L
    }

    void 'withdrawal beyond balance is rejected'() {
        given:
        Account account = repository.save(new Account(null, 'Checking', 100L))

        when:
        repository.reserveDecrementBalance(account.id, 1000L) (3)

        then:
        thrown(DataIntegrityViolationException)
        Account found = repository.findById(account.id).orElseThrow()
        found.balance == 100L (4)
    }

    void 'withdrawal beyond balance responds with conflict'() {
        given:
        Account account = repository.save(new Account(null, 'Checking', 100L))

        when:
        httpClient.toBlocking().exchange(
                HttpRequest.POST("/accounts/${account.id}/withdraw?amount=1000", null))

        then:
        HttpClientResponseException e = thrown()
        e.status == HttpStatus.CONFLICT (5)
        e.response.getBody(String).orElse('').contains('The operation violates an account constraint') (6)
    }

    void 'account is created, deposited and withdrawn over HTTP'() {
        when:
        HttpResponse<Account> created = httpClient.toBlocking().exchange(
                HttpRequest.POST('/accounts', new Account(null, 'Savings', 100L)), Account) (7)

        then:
        created.status == HttpStatus.CREATED

        when:
        Account deposited = httpClient.toBlocking().retrieve(
                HttpRequest.POST("/accounts/${created.body().id}/deposit?amount=25", null), Account) (8)

        then:
        deposited.balance == 125L

        when:
        Account withdrawn = httpClient.toBlocking().retrieve(
                HttpRequest.POST("/accounts/${created.body().id}/withdraw?amount=50", null), Account)

        then:
        withdrawn.balance == 75L
    }

    void 'deposit for unknown account responds with not found'() {
        when:
        httpClient.toBlocking().exchange(HttpRequest.POST('/accounts/-1/deposit?amount=1', null))

        then:
        HttpClientResponseException e = thrown()
        e.status == HttpStatus.NOT_FOUND (9)
    }
}
1 Disable the test transaction. Oracle applies reservations when the transaction commits, so each repository call must commit its own transaction for the test to read the result.
2 Deposit 25: the reservation increments balance by the given delta and returns the number of updated rows.
3 A withdrawal larger than the balance would make balance negative, so it violates the generated CHECK constraint.
4 The failed reservation leaves balance unchanged.
5 Over HTTP, the exception handler responds with 409 Conflict.
6 The response body contains the message from the exception handler.
7 Create an account over HTTP. The controller responds with 201 Created.
8 Deposit and withdraw over HTTP. Each response contains the committed balance.
9 A reservation for an unknown account updates no rows, so the controller responds with 404 Not Found.

10. 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 before the application connects.

11. Inspect the Generated Schema

To inspect the generated columns and constraints, connect to the database, for example, to the Test Resources container while the application runs. The RESERVABLE_COLUMN column of USER_TAB_COLS shows which columns are reservable:

SELECT column_name, data_type, reservable_column
FROM user_tab_cols
WHERE table_name = 'ACCOUNT'
ORDER BY column_id;

List the generated check constraints:

SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name = 'ACCOUNT'
  AND constraint_type = 'C'
  AND constraint_name LIKE 'CK_%';

12. Run the Application

Start the application with ./gradlew run or ./mvnw mn:run, and create an account:

curl -X POST http://localhost:8080/accounts \
     -H 'Content-Type: application/json' \
     -d '{"name":"Checking","balance":100}'

Deposit 25:

curl -X POST 'http://localhost:8080/accounts/1/deposit?amount=25'

The response shows a balance of 125.

Withdraw 50:

curl -X POST 'http://localhost:8080/accounts/1/withdraw?amount=50'

The response shows a balance of 75.

Attempt to withdraw more than the balance:

curl -i -X POST 'http://localhost:8080/accounts/1/withdraw?amount=1000'

The response is 409 Conflict, and the account keeps its previous balance.

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

13. Next Steps

Read more about Oracle Reservable Columns in Micronaut Data and Oracle lock-free reservation.

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