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

  BookStore.java

  @Entity
  public interface BookStore {
  
      @Version        
      int version();
  
      ...other properties omitted...
  }

  Kotlin

  BookStore.kt

  @Entity
  public interface BookStore {
  
      @Version
      val version: Int
      
      ...other properties omitted...
  }

• Modify Book

  Java

  Book.java

  @Entity    
  public interface Book {
  
      @Version
      int version();
  
      ...other properties omitted...
  }

  Kotlin

  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

  BookStore savedData = sqlClient.save(
          Objects.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);

  Kotlin

  val savedData = sqlClient.save(
      new(BookStore::class).by {
          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

  BookStore savedData = sqlClient.save(
          Objects.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);

  Kotlin

  val savedData = sqlClient.save(
      new(BookStore::class).by {
          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

  BookStore savedData = sqlClient.save(
          Objects.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);

  Kotlin

  val savedData = sqlClient.save(
      new(BookStore::class).by {
          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

sqlClient.save(
        Objects.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"));
            });
        })
);

Kotlin

sqlClient.save(
    new(BookStore::class).by {
        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:

1. Check if the book store exists

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

2. Determine INSERT or UPDATE based on previous query

   insert or update, omitted

3. 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 */

4. Determine INSERT or UPDATE based on previous query

   insert or update, omitted

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

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

    JSqlClient sqlClient = JSqlClient
        .newBuilder()
        .setDefaultLockMode(LockMode.PESSIMISTIC)
        ...other configurations omitted...
        .build();

    Kotlin

    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

  sqlClient
          .getEntities()
          .saveCommand(
                  Objects.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();

  Kotlin

  sqlClient.save(
      new(BookStore::class).by {
          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:

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

2. Determine INSERT or UPDATE based on previous query

   insert or update, omitted

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

4. Determine INSERT or UPDATE based on previous query

   insert or update, omitted

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

6. Determine INSERT or UPDATE based on previous query

   insert or update, omitted