Java / Gradle

Value-based optimistic locking with Micronaut Data JDBC and Oracle

Learn how to use Oracle SYS_ROW_ETAG values for optimistic locking with Micronaut Data JDBC.

Radovan Radic
On this guide
In this section

Getting Started

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

In this guide, you will use Oracle’s SYS_ROW_ETAG function with Micronaut Data JDBC to implement optimistic locking without a physical version column. The ETag is computed from selected values in the row and compared during updates and deletes.

What you will need

To complete this guide, you will need the following:

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.

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 \
    --build=gradle \
    --lang=java \
    --test=junit
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, and oracle 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

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

Database Configuration

And the database configuration:

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

The datasource uses the Oracle dialect and schema generation creates the sample tables. Test Resources supplies the JDBC URL, username, and password by starting an Oracle Free container.

Why use value-based optimistic locking

Without optimistic locking, two transactions can read the same row and later updates can overwrite one another without detecting that the data changed in between. An ETag gives each read a value representing the selected row values. Micronaut Data includes that value in the UPDATE and DELETE predicates, so a stale entity matches no row and results in an api:data.exceptions.OptimisticLockException[].

This approach is useful when you need to prevent lost updates but cannot add a dedicated version column, or when you want to use Oracle’s built-in row ETag instead of maintaining one yourself. Compared with an ordinary update, it detects stale data. The trade-off is that an entity must be read with its current ETag before an update or delete. You can choose whether all eligible properties or only explicitly selected properties contribute to the ETag.

Entity with an implicit ETag

Annotate a String property with @GeneratedETag. When the entity is annotated with @ETaggable, all eligible persistent scalar properties participate in the ETag calculation:

java/src/main/java/example/micronaut/Book.java
@MappedEntity
@ETaggable // 
public record Book(
    @Id @GeneratedValue Long id,
    String title,
    @Relation(Relation.Kind.EMBEDDED) BookDetails details,
    @GeneratedETag @Nullable String etag) { // 

    @Embeddable
    public record BookDetails(
        int pages,
        @ETagValue(exclude = true) int chapters) { // 
    }
}
1 @ETaggable includes eligible scalar properties such as id, title, and the embedded pages property.
2 @GeneratedETag marks etag as a generated, read-only version property. It is not a physical column.
3 @ETagValue(exclude = true) excludes chapters even though it is persisted.

The @ETagValue annotation can also explicitly select ETag inputs when @ETaggable is not used:

java/src/main/java/example/micronaut/Article.java
@MappedEntity
public record Article(
    @Id @GeneratedValue @ETagValue Long id,
    @ETagValue String title,
    String notes,
    @GeneratedETag(function = "SYS_ROW_ETAG") @Nullable String etag) {
}

In this example, only id and title participate. The notes property can change without changing the ETag. The explicit function value is the function name only; Micronaut Data supplies the input columns. If function is omitted, Oracle’s dialect default, SYS_ROW_ETAG, is used.

Repository

Declare an Oracle JDBC repository:

java/src/main/java/example/micronaut/BookRepository.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;

@JdbcRepository(dialect = Dialect.ORACLE) // 
public interface BookRepository extends CrudRepository<Book, Long> {
}
1 The repository dialect must match the database because Micronaut Data computes SQL at compilation time.

Optimistic locking

The ETag is projected when an entity is read. Micronaut Data then uses the same computed expression in the optimistic-lock predicate for UPDATE and DELETE operations.

Add a test that verifies a fresh ETag succeeds and a stale ETag fails:

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

import io.micronaut.data.exceptions.OptimisticLockException;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertThrows;

@MicronautTest(transactional = false)
class OptimisticLockingTest {

    @Inject
    BookRepository bookRepository;

    @Inject
    ArticleRepository articleRepository;

    @Test
    void staleEtagPreventsAnUpdate() {
        Book saved = bookRepository.save(new Book(null, "Initial", new Book.BookDetails(200, 10), null));
        Book fresh = bookRepository.findById(saved.id()).orElseThrow(); // 
        assertNotNull(fresh.etag());

        bookRepository.update(new Book(fresh.id(), "Updated", fresh.details(), fresh.etag())); // 

        Book stale = new Book(fresh.id(), "Stale", fresh.details(), fresh.etag()); // 
        assertThrows(OptimisticLockException.class, () -> bookRepository.update(stale));
    }

    @Test
    void changingAnExcludedFieldDoesNotChangeTheEtag() {
        Article saved = articleRepository.save(
            new Article(null, "Oracle", "Initial notes", null));
        Article fresh = articleRepository.findById(saved.id()).orElseThrow();

        articleRepository.update(new Article(
            fresh.id(), fresh.title(), "Updated notes", fresh.etag()));

        Article reloaded = articleRepository.findById(fresh.id()).orElseThrow();
        assertEquals("Updated notes", reloaded.notes());
        assertEquals(fresh.etag(), reloaded.etag());
    }
}
1 Reload the entity after saving it to obtain the computed ETag.
2 A successful update changes an ETag input, so reload the entity before another optimistic-locking write.
3 Reusing the old ETag causes api:data.exceptions.OptimisticLockException[] because the row no longer matches.

Changes to fields excluded with @ETagValue(exclude = true), or fields not selected with explicit @ETagValue, do not change the ETag. Always reload an entity after changing an ETag input before issuing another update.

Testing the Application

To run the tests:

./gradlew test

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

If you run the test, Micronaut Test Resources starts an Oracle Database container through Testcontainers.

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 optimistic locking in Micronaut Data.

Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

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