Optimistic/Pessimistic Lock

Save commands support both optimistic lock and pessimistic lock.

Optimistic Lock

Jimmer supports optimistic lock via the @org.babyfish.jimmer.sql.Version annotation.

Modify Entity Types

Modify BookStore

Java
Kotlin

BookStore.java
@Entity
public interface BookStore {

    @Version        
    int version();

    ...other properties omitted...
}

BookStore.kt
@Entity
public interface BookStore {

    @Version
    val version: Int
    
    ...other properties omitted...
}

Modify Book

Java
Kotlin

Book.java
@Entity    
public interface Book {

    @Version
    int version();

    ...other properties omitted...
}

Book.kt
@Entity
public interface Book {

    @Version
    val version: Int

    ...other properties omitted...
}

Demo

Characteristics of optimistic lock:

When inserting objects (whether explicitly doing INSERT, or UPSERT determined to be INSERT), the version of the object will be inserted into the database.

For example:

Java
Kotlin

BookStore savedData = sqlClient.save(
        Immutables.createBookStore(draft -> {
            draft.setName("TURING");
            draft.addIntoBooks(book -> {
                book.setName("Introduction to Algorithms");
                book.setEdition(3);
                book.setPrice(new BigDecimal("44.99"));
            });
            draft.addIntoBooks(book -> {
                book.setName("The Pragmatic Programmer");
                book.setEdition(2);
                book.setPrice(new BigDecimal("39.99"));
            });
        })
).getModifiedEntity();
System.out.println(savedData);

val savedData = sqlClient.save(
    BookStore {
        name = "TURING"
        books().addBy {
            name = "Introduction to Algorithms"
            edition = 3;
            price = BigDecimal("44.99")
        }
        books().addBy {
            name = "The Pragmatic Programmer"
            edition = 2
            price = BigDecimal("39.99")
        }
    }
).modifiedEntity
println(savedData)

tip

For INSERT operations, if the version of the object is not assigned, Jimmer will automatically insert 0.

If you cannot ascertain whether a UPSERT save command will ultimately be determined as INSERT or UPDATE, you should insist on specifying the version property.

The following example assumes we know for sure the save will be determined as INSERT rather than UPDATE, so the version properties are not specified.

No ids are specified so jimmer determines their existence by their key properties.

Assuming none of the objects exist, 3 new records will be inserted.

No version properties are specified, so they are filled with 0 automatically.

The final printed result will be (formatted for readability):

{
    "id":100,
    "name":"TURING",
    "version":0,
    "books":[
        {
            "id":100,
            "name":"Introduction to Algorithms",
            "edition":3,
            "price":44.99,
            "version":0,
            "store":{
                "id":100
            }
        },
        {
            "id":101,
            "name":"The Pragmatic Programmer",
            "edition":2,
            "price":39.99,
            "version":0,
            "store":{
                "id":100
            }
        }
    ]
}

info

Of course, if the user specifies version for these objects, the specified values rather than 0 will be inserted.

When modifying objects (whether explicitly doing UPDATE, or UPSERT determined to be UPDATE), Jimmer will compare the version passed by the user and the existing version in the database for each object. If they differ, an exception will be thrown.

Let's modify the code a bit and execute again:

Java
Kotlin

BookStore savedData = sqlClient.save(
        Immutables.createBookStore(draft -> {
            draft.setName("TURING");
            draft.setVersion(0);
            draft.addIntoBooks(book -> {
                book.setName("Introduction to Algorithms");
                book.setEdition(3);
                book.setPrice(new BigDecimal("54.99"));
                book.setVersion(0);
            });
            draft.addIntoBooks(book -> {
                book.setName("The Pragmatic Programmer");
                book.setEdition(2);
                book.setPrice(new BigDecimal("39.99"));

                // illegal version
                book.setVersion(9999);
            });
        })
).getModifiedEntity();
System.out.println(savedData);

val savedData = sqlClient.save(
    BookStore {
        name = "TURING"
        version = 0
        books().addBy {
            name = "Introduction to Algorithms"
            edition = 3;
            price = BigDecimal("44.99")
            version = 0
        }
        books().addBy {
            name = "The Pragmatic Programmer"
            edition = 2
            price = BigDecimal("39.99")

            // illegal version
            version = 9999
        }
    }
).modifiedEntity
println(savedData)

caution

For UPDATE operations, if the version of the object is not assigned, Jimmer will throw an exception.

If you cannot determine whether a UPSERT save command will ultimately be INSERT or UPDATE, you should insist on specifying the version property.

Execute, since data already exists, the 3 objects will be updated.

Obviously, the last book's version 9999 is illegal. The above code will result in the following exception:

Exception Type: org.babyfish.jimmer.sql.runtime.SaveException
Exception Code: org.babyfish.jimmer.sql.runtime.SaveErrorCode.ILLEGAL_VERSION
Exception Message:

Save error caused by the path: "<root>.books": Cannot update the entity whose type is "org.doc.j.model.Book", id is "101" and version is "9999"

Let's modify the code again to use the correct version for all objects:

Java
Kotlin

BookStore savedData = sqlClient.save(
        Immutables.createBookStore(draft -> {
            draft.setName("TURING");
            draft.setVersion(0);
            draft.addIntoBooks(book -> {
                book.setName("Introduction to Algorithms");
                book.setEdition(3);
                book.setPrice(new BigDecimal("54.99"));
                book.setVersion(0);
            });
            draft.addIntoBooks(book -> {
                book.setName("The Pragmatic Programmer");
                book.setEdition(2);
                book.setPrice(new BigDecimal("39.99"));
                book.setVersion(0);
            });
        })
).getModifiedEntity();
System.out.println(savedData);

val savedData = sqlClient.save(
    BookStore {
        name = "TURING"
        version = 0
        books().addBy {
            name = "Introduction to Algorithms"
            edition = 3;
            price = BigDecimal("44.99")
            version = 0
        }
        books().addBy {
            name = "The Pragmatic Programmer"
            edition = 2
            price = BigDecimal("39.99")
            version = 0
        }
    }
).modifiedEntity
println(savedData)

The final printed result will be (formatted for readability):

{
    "id":100,
    "name":"TURING",
    "version":1,
    "books":[
        {
            "id":100,
            "name":"Introduction to Algorithms",
            "edition":3,
            "price":54.99,
            "version":1,
            "store":{
                "id":100
            }
        },
        {
            "id":101,
            "name":"The Pragmatic Programmer",
            "edition":2,
            "price":39.99,
            "version":1,
            "store":{
                "id":100
            }
        }
    ]
}

info

As you can see, the version is incremented by 1 automatically after data modification.

In real projects, the version number of optimistic lock is often a hidden field in the form UI. If the form UI does not automatically redirect to other UI after saving, but keeps the UI unchanged to support multiple submissions, the hidden field should be updated with such return info after each successful save.

Pessimistic Lock

Unlike optimistic lock, pessimistic locks are very short-lived, only valid within a single jdbc transaction.

Normally, Jimmer generates some query SQL to aid save command execution, such as:

Determining whether a UPSERT should ultimately be INSERT or UPDATE
Determining which associated objects need to be dissociated

Next, we compare the differences in these query SQL between using and not using pessimistic lock.

info

In the previous examples, to demonstrate optimistic lock, we assumed the BookStore and Book types both defined a version property.

In the following examples to demonstrate pessimistic lock, we no longer make that assumption.

Without Pessimistic Lock

Java
Kotlin

sqlClient.save(
        Immutables.createBookStore(draft -> {
            draft.setName("TURING");
            draft.addIntoBooks(book -> {
                book.setName("Introduction to Algorithms");
                book.setEdition(3);
                book.setPrice(new BigDecimal("44.99"));
            });
            draft.addIntoBooks(book -> {
                book.setName("The Pragmatic Programmer");
                book.setEdition(2);
                book.setPrice(new BigDecimal("39.99"));
            });
        })
);

sqlClient.save(
    BookStore {
        name = "TURING"
        books().addBy {
            name = "Introduction to Algorithms"
            edition = 3;
            price = BigDecimal("44.99")
        }
        books().addBy {
            name = "The Pragmatic Programmer"
            edition = 2
            price = BigDecimal("39.99")
        }
    }
)

This generates 6 SQL statements:

Check if the book store exists

select
    tb_1_.ID,
    tb_1_.NAME
from BOOK_STORE tb_1_
where
    tb_1_.NAME = ? /* TURING */ 

Determine INSERT or UPDATE based on previous query

insert or update, omitted

Check if the 1st book exists

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION
from BOOK tb_1_
where
        tb_1_.NAME = ? /* Introduction to Algorithms */
    and
        tb_1_.EDITION = ? /* 3 */

Determine INSERT or UPDATE based on previous query

insert or update, omitted

Check if the 2nd book exists

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION
from BOOK tb_1_
where
        tb_1_.NAME = ? /* The Pragmatic Programmer */
    and
        tb_1_.EDITION = ? /* 2 */

Determine INSERT or UPDATE based on previous query

insert or update, omitted

info

These query statements are used to make conditional judgments to determine how to generate subsequent SQL.

However, without pessimistic lock, the conditions and assumptions made via these queries can potentially be invalidated by concurrent operations, leading to exceptions when executing subsequent SQL.

To avoid such concurrency issues, pessimistic lock can be enabled. Next we discuss how to implement pessimistic lock.

Enabling Pessimistic Lock

There are two ways to enable pessimistic lock:

Global configuration

There are two ways to enable pessimistic lock globally:
- Via Spring Boot Starter configuration
  
  Modify application.yml (or application.properties):
```
jimmer:
    default-lock-mode: PESSIMISTIC
```
- Via low-level API configuration
  - Java
  - Kotlin
  JSqlClient sqlClient = JSqlClient .newBuilder() .setDefaultLockMode(LockMode.PESSIMISTIC) ...other configurations omitted... .build();
  val sqlClient = newKSqlClient { setDefaultLockMode(LockMode.PESSIMISTIC) }
warning
This change modifies the global setting, and the original default value OPTIMISTIC is compromised. This means that unless a specific save command is set to optimistic locking mode, the optimistic locking functionality described earlier disappears.
Therefore, in most cases, global configuration is not recommended, and the command-level configuration that will be introduced later is more recommended.

Command-level configuration

Unlike global configuration affecting all save commands, command-level configuration only affects the current save command.

info

If pessimistic lock is already enabled globally, no need for command-level configuration.

Calling setLockMode(LockMode) on the save command enables pessimistic lock:

Java
Kotlin

sqlClient
        .getEntities()
        .saveCommand(
                Immutables.createBookStore(draft -> {
                    draft.setName("TURING");
                    draft.addIntoBooks(book -> {
                        book.setName("Introduction to Algorithms");
                        book.setEdition(3);
                        book.setPrice(new BigDecimal("44.99"));
                    });
                    draft.addIntoBooks(book -> {
                        book.setName("The Pragmatic Programmer");
                        book.setEdition(2);
                        book.setPrice(new BigDecimal("39.99"));
                    });
                })
        )
        .setLockMode(LockMode.PESSIMISTIC)
        .execute();

sqlClient.save(
    BookStore {
        name = "TURING"
        books().addBy {
            name = "Introduction to Algorithms"
            edition = 3;
            price = BigDecimal("44.99")
        }
        books().addBy {
            name = "The Pragmatic Programmer"
            edition = 2
            price = BigDecimal("39.99")
        }
    }
) {
    setLockMode(LockMode.PESSIMISTIC)
}

Once pessimistic lock is enabled, the generated query statements will change significantly:

Check if the book store exists

select
    tb_1_.ID,
    tb_1_.NAME 
from BOOK_STORE tb_1_
where
    tb_1_.NAME = ? /* TURING */
/* highlight-next-line */    
for update

Determine INSERT or UPDATE based on previous query

insert or update, omitted

Check if the 1st book exists

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION
from BOOK tb_1_
where
        tb_1_.NAME = ? /* Introduction to Algorithms */
    and
        tb_1_.EDITION = ? /* 3 */
/* highlight-next-line */
for update

Determine INSERT or UPDATE based on previous query

insert or update, omitted

Check if the 2nd book exists

select
    tb_1_.ID, 
    tb_1_.NAME,
    tb_1_.EDITION
from BOOK tb_1_
where
        tb_1_.NAME = ? /* The Pragmatic Programmer */
    and 
        tb_1_.EDITION = ? /* 2 */
/* highlight-next-line */
for update

Determine INSERT or UPDATE based on previous query

insert or update, omitted

Optimistic Lock​

Modify Entity Types​

Demo​

Pessimistic Lock​

Without Pessimistic Lock​

Enabling Pessimistic Lock​

Optimistic Lock

Modify Entity Types

Demo

Pessimistic Lock

Without Pessimistic Lock

Enabling Pessimistic Lock