Pessimistic and Optimistic Locking With MySQL, jOOQ, and Kotlin

Managing concurrent entry to shared information generally is a problem, however by utilizing the proper locking technique, you may be sure that your functions run easily and keep away from conflicts that might result in information corruption or inconsistent outcomes.

On this article, we’ll discover tips on how to implement pessimistic and optimistic locking utilizing Kotlin, Ktor, and jOOQ, and supply sensible examples that can assist you perceive when to make use of every strategy.

Whether or not you’re a newbie or an skilled developer, the thought is to stroll away with insights into the ideas of concurrency management and tips on how to apply them in apply.

Knowledge Mannequin

For example we’ve a desk referred to as customers in our MySQL database with the next schema:

CREATE TABLE customers (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id)
);

Pessimistic Locking

We wish to implement pessimistic locking when updating a person’s age, which suggests we wish to lock the row for that person once we learn it from the database and maintain the lock till we end the replace. This ensures that no different transaction can replace the identical row whereas we’re engaged on it.

First, we have to ask jOOQ to make use of pessimistic locking when querying the customers desk.

We will do that by setting the forUpdate() flag on the SELECT question:

val person = dslContext.selectFrom(USERS)
                     .the place(USERS.ID.eq(id))
                     .forUpdate()
                     .fetchOne()

This may lock the row for the person with the required ID once we execute the question.

Subsequent, we will replace the person’s age and commit the transaction:

dslContext.replace(USERS)
         .set(USERS.AGE, newAge)
         .the place(USERS.ID.eq(id))
         .execute()
transaction.commit()

Be aware that we have to carry out the replace throughout the identical transaction that we used to learn the person’s row and lock it. This ensures that the lock is launched when the transaction is dedicated. You may see how that is carried out within the subsequent part.

Ktor Endpoint

Lastly, here is an instance Ktor endpoint that demonstrates tips on how to use this code to replace a person’s age:

publish("/customers/id/age") 
    val id = name.parameters["id"]?.toInt() ?: throw BadRequestException("Invalid ID")
    val newAge = name.obtain<Int>()

    dslContext.transaction  transaction ->
        val person = dslContext.selectFrom(USERS)
                             .the place(USERS.ID.eq(id))
                             .forUpdate()
                             .fetchOne()

        if (person == null) 
            throw NotFoundException("Consumer not discovered")
        

        person.age = newAge
        dslContext.replace(USERS)
                 .set(USERS.AGE, newAge)
                 .the place(USERS.ID.eq(id))
                 .execute()
        transaction.commit()
    

    name.reply(HttpStatusCode.OK)

As you may see, we first learn the person’s row and lock it utilizing jOOQ’s forUpdate() methodology. Then we test if the person exists, replace their age, and commit the transaction. Lastly, we reply with an HTTP 200 OK standing code to point success.

Optimistic Model

Optimistic locking is a method the place we do not lock the row once we learn it, however as a substitute, add a model quantity to the row and test it once we replace it. If the model quantity has modified since we learn the row, it implies that another person has up to date it within the meantime, and we have to retry the operation with the up to date row.

To implement optimistic locking, we have to add a model column to our customers desk:

CREATE TABLE customers (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  model INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

We’ll use the model column to trace the model of every row.

Now, let’s replace our Ktor endpoint to make use of optimistic locking. First, we’ll learn the person’s row and test its model:

publish("/customers/id/age") 
    val id = name.parameters["id"]?.toInt() ?: throw BadRequestException("Invalid ID")
    val newAge = name.obtain<Int>()

    var up to date = false
    whereas (!up to date) 
        val person = dslContext.selectFrom(USERS)
                             .the place(USERS.ID.eq(id))
                             .fetchOne()

        if (person == null) 
            throw NotFoundException("Consumer not discovered")
        

        val oldVersion = person.model
        person.age = newAge
        person.model += 1

        val rowsUpdated = dslContext.replace(USERS)
                                    .set(USERS.AGE, newAge)
                                    .set(USERS.VERSION, person.model)
                                    .the place(USERS.ID.eq(id))
                                    .and(USERS.VERSION.eq(oldVersion))
                                    .execute()

        if (rowsUpdated == 1) 
            up to date = true
        
    

    name.reply(HttpStatusCode.OK)

On this instance, we use a whereas loop to retry the replace till we efficiently replace the row with the right model quantity. First, we learn the person’s row and get its present model quantity. Then we replace the person’s age and increment the model quantity. Lastly, we execute the replace question and test what number of rows have been up to date.

If the replace succeeded (i.e., one row was up to date), we set up to date to true and exit the loop. If the replace failed (i.e., no rows have been up to date as a result of the model quantity had modified), we repeat the loop and check out once more.

Be aware that we use the and(USERS.VERSION.eq(oldVersion)) situation within the WHERE clause to make sure that we solely replace the row if its model quantity remains to be the identical because the one we learn earlier.

Commerce-Offs

Optimistic and pessimistic locking are two important strategies utilized in concurrency management to make sure information consistency and correctness in multi-user environments.

Pessimistic locking prevents different customers from accessing a document whereas it’s being modified, whereas optimistic locking permits a number of customers to entry and modify information concurrently.

A financial institution utility that handles cash transfers between accounts is an effective instance of a situation the place pessimistic locking is a better option. On this situation, when a person initiates a switch, the system ought to be sure that the funds within the account can be found and that no different person is modifying the identical account’s steadiness concurrently.

On this case, it’s crucial to stop some other person from accessing the account whereas the transaction is in progress. The applying can use pessimistic locking to make sure unique entry to the account through the switch course of, stopping any concurrent updates and guaranteeing information consistency.

An internet buying utility that manages product stock is an instance of a situation the place optimistic locking is a better option.

On this situation, a number of customers can entry the identical product web page and make purchases concurrently. When a person provides a product to the cart and proceeds to checkout, the system ought to be sure that the product’s availability is updated and that no different person has bought the identical product.

It’s not essential to lock the product document because the system can deal with conflicts through the checkout course of. The applying can use optimistic locking, permitting concurrent entry to the product document and resolving conflicts through the transaction by checking the product’s availability and updating the stock accordingly.

Conclusion

When designing and implementing database techniques, it is necessary to concentrate on the advantages and limitations of each pessimistic and optimistic locking methods.

Whereas pessimistic locking is a dependable manner to make sure information consistency, it may result in decreased efficiency and scalability. Then again, optimistic locking gives higher efficiency and scalability, nevertheless it requires cautious consideration of concurrency points and error dealing with. 

In the end, choosing the proper locking technique is determined by the particular use case and trade-offs between information consistency and efficiency. Consciousness of each locking methods is crucial for good decision-making and for constructing sturdy and dependable backend techniques.