Pessimistic Locking

Preparation

The pessimistic lock discussed in this article is not the ordinary query pessimistic lock, but rather the pessimistic lock within save commands.

When facing a save operation, the save command will adopt two distinctly different behaviors depending on the situation:

1. Directly utilize the database's capability to perform upsert operations

2. First check if the data to be saved exists through a select operation, then decide whether the subsequent operation should be insert or update based on the query results

Jimmer will try to execute 1 whenever possible. If it cannot do so, it will execute 2 and report the QueryReason to the user.

When Jimmer has to execute 2, there is an issue: there exists a time window between the initial select operation and the subsequent insert or update operation, during which other transactions might squeeze into this window and modify the same data.

To solve this problem, the save command allows users to enable pessimistic locking, adding the for update option when executing the select operation, which remains in effect until the transaction is committed.

Before demonstrating pessimistic locks, we need to make the save command execute 2 above rather than 1. An effective way is to enable in-transaction triggers:

In-transaction triggers are a type of trigger supported by Jimmer. Jimmer always queries the old data before modification through select operations to simulate the trigger effect.

note

In-transaction triggers are a feature that hasn't been introduced yet, however, readers don't need to pay too much attention to it. Here, we're just using it to ensure Jimmer adopts the query-before-modify strategy.

Use either of the following methods to enable in-transaction triggers

• Using Jimmer's Spring Boot Starter

  Modify the application.yml or application.properties file to complete the configuration

  application.yml

  jimmer:
      trigger-type: TRANSACTION_ONLY

• Using Jimmer's core API

  Java

  JSqlClient sqlClient = JSqlClient
      .newBuilder()
      .setTriggerType(TriggerType.TRANSACTION_ONLY)
      ...other configurations omitted...
      .build();

  Kotlin

  val sqlClient = sqlClient {
      setTriggerType(TriggerType.TRANSACTION_ONLY)
      ...other configurations omitted...
  }

Through this configuration, we force Jimmer to always query before modifying, to facilitate the demonstration of pessimistic locks.

Demonstrating Pessimistic Locks

We can enable pessimistic locks for save commands using the setPessimisticLock method

Java

List<Book> books = Arrays.asList(
    Immutables.createBook(draft -> {
        draft.setName("GraphQL in Action");
        draft.setEdition(3);
        draft.setPrice(new BigDecimal("59.9"));
    }),
    Immutables.createBook(draft -> {
        draft.setName("GraphQL in Action");
        draft.setEdition(4);
        draft.setPrice(new BigDecimal("59.9"));
    })
);
sqlClient
        .saveEntitiesCommand(books)
        .setPessimisticLock(Book.class)
        .execute();

Kotlin

val books = listOf(
    Book { 
        name = "SQL in Action"
        edition = 3
        price = BigDecimal("59.9")
    },
    Book {
        name = "SQL in Action"
        edition = 4
        price = BigDecimal("59.9")
    }
)

sqlClient.saveEntities(books) {
    setPessimisticLock(Book::class)
}

Here, setPessimisticLock enables pessimistic locking for the Book entity. If the save command needs to query Book type objects, the related select statement will use the for update option.

info

In fact, you can also call the setPessimisticLockAll method to blindly enable pessimistic locks for all entity types

Finally, three SQL statements are generated

1. Query whether the objects to be saved exist, with the for update option

   Purpose: COMMAND(TRIGGER)
   SQL: select
       tb_1_.ID,
       tb_1_.NAME,
       tb_1_.EDITION,
       tb_1_.PRICE,
       tb_1_.STORE_ID
   from BOOK tb_1_
   where
       (tb_1_.NAME, tb_1_.EDITION) in (
           (? /* GraphQL in Action */, ? /* 3 */), 
           (? /* GraphQL in Action */, ? /* 4 */)
       ) for update
   info

   Here, the query statement includes the for update option, indicating that the pessimistic lock is in effect.

2. Insert non-existent data (not the focus of this article, collapsed by default)

   insert into BOOK(
       NAME, EDITION, PRICE
   ) values(
       ? /* GraphQL in Action */, 
       ? /* 4 */, 
       ? /* 59.9 */
   )
3. Update existing data (not the focus of this article, collapsed by default)

   update BOOK
   set
       PRICE = ? /* 59.9 */
   where
       ID = ? /* 12 */