Table of Contents

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.

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

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 \
    --build=maven \
    --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, and oracle 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.
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.

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

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

src/main/groovy/example/micronaut/Book.groovy
@CompileStatic
@MappedEntity
@ETaggable (1)
class Book {
    @Id
    @GeneratedValue
    Long id
    String title
    @Relation(Relation.Kind.EMBEDDED)
    BookDetails details
    @GeneratedETag
    String etag (2)

    Book() {
    }

    Book(Long id, String title, BookDetails details, String etag) {
        this.id = id
        this.title = title
        this.details = details
        this.etag = etag
    }

    @CompileStatic
    @Embeddable
    static class BookDetails {
        int pages
        @ETagValue(exclude = true) (3)
        int chapters

        BookDetails() {
        }

        BookDetails(int pages, int chapters) {
            this.pages = pages
            this.chapters = 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:

src/main/groovy/example/micronaut/Article.groovy
@CompileStatic
@MappedEntity
class Article {
    @Id
    @GeneratedValue
    @ETagValue
    Long id
    @ETagValue
    String title
    String notes
    @GeneratedETag(function = 'SYS_ROW_ETAG')
    String etag

    Article() {
    }

    Article(Long id, String title, String notes, String etag) {
        this.id = id
        this.title = title
        this.notes = notes
        this.etag = 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.

7. Repository

Declare an Oracle JDBC repository:

src/main/groovy/example/micronaut/BookRepository.groovy
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) (1)
interface BookRepository extends CrudRepository<Book, Long> {
}
1 The repository dialect must match the database because Micronaut Data computes SQL at compilation time.

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

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

import io.micronaut.data.exceptions.OptimisticLockException
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import jakarta.inject.Inject
import spock.lang.Specification

@MicronautTest(transactional = false)
class OptimisticLockingSpec extends Specification {

    @Inject
    BookRepository bookRepository

    @Inject
    ArticleRepository articleRepository

    void 'stale etag prevents an update'() {
        when:
        def saved = bookRepository.save(new Book(null, 'Initial', new Book.BookDetails(200, 10), null))
        def fresh = bookRepository.findById(saved.id).orElseThrow() (1)

        then:
        fresh.etag != null

        when:
        bookRepository.update(new Book(fresh.id, 'Updated', fresh.details, fresh.etag)) (2)
        bookRepository.update(new Book(fresh.id, 'Stale', fresh.details, fresh.etag)) (3)

        then:
        thrown(OptimisticLockException)
    }

    void 'changing an excluded field does not change the etag'() {
        given:
        def saved = articleRepository.save(new Article(null, 'Oracle', 'Initial notes', null))
        def fresh = articleRepository.findById(saved.id).orElseThrow()

        when:
        articleRepository.update(new Article(fresh.id, fresh.title, 'Updated notes', fresh.etag))
        def reloaded = articleRepository.findById(fresh.id).orElseThrow()

        then:
        reloaded.notes == 'Updated notes'
        reloaded.etag == fresh.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.

9. Testing the Application

To run the tests:

./mvnw test

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

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

10. Next Steps

Read more about optimistic locking in Micronaut Data.

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