Pessimistic and Optimistic Locking With MySQL, jOOQ, and Kotlin

Managing concurrent access to shared data can be a challenge, but by using the right locking strategy, you can ensure that your applications run smoothly and avoid conflicts that could lead to data corruption or inconsistent results.

In this article, we'll explore how to implement pessimistic and optimistic locking using Kotlin, Ktor, and jOOQ, and provide practical examples to help you understand when to use each approach.

Whether you are a beginner or an experienced developer, the idea is to walk away with insights into the principles of concurrency control and how to apply them in practice.

Data Model

Let's say we have a table called users in our MySQL database with the following schema:

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

Pessimistic Locking

We want to implement pessimistic locking when updating a user's age, which means we want to lock the row for that user when we read it from the database and hold the lock until we finish the update. This ensures that no other transaction can update the same row while we're working on it.

First, we need to ask jOOQ to use pessimistic locking when querying the users table.

We can do this by setting the forUpdate() flag on the SELECT query:

val user = dslContext.selectFrom(USERS)
                     .where(USERS.ID.eq(id))
                     .forUpdate()
                     .fetchOne()

This will lock the row for the user with the specified ID when we execute the query.

Next, we can update the user's age and commit the transaction:

dslContext.update(USERS)
         .set(USERS.AGE, newAge)
         .where(USERS.ID.eq(id))
         .execute()
transaction.commit()

Note that we need to perform the update within the same transaction that we used to read the user's row and lock it. This ensures that the lock is released when the transaction is committed. You can see how this is done in the next section.

Ktor Endpoint

Finally, here's an example Ktor endpoint that demonstrates how to use this code to update a user's age:

post("/users/{id}/age") {
    val id = call.parameters["id"]?.toInt() ?: throw BadRequestException("Invalid ID")
    val newAge = call.receive<Int>()

    dslContext.transaction { transaction ->
        val user = dslContext.selectFrom(USERS)
                             .where(USERS.ID.eq(id))
                             .forUpdate()
                             .fetchOne()

        if (user == null) {
            throw NotFoundException("User not found")
        }

        user.age = newAge
        dslContext.update(USERS)
                 .set(USERS.AGE, newAge)
                 .where(USERS.ID.eq(id))
                 .execute()
        transaction.commit()
    }

    call.respond(HttpStatusCode.OK)
}

As you can see, we first read the user's row and lock it using jOOQ's forUpdate() method. Then we check if the user exists, update their age, and commit the transaction. Finally, we respond with an HTTP 200 OK status code to indicate success.

Optimistic Version

Optimistic locking is a technique where we don't lock the row when we read it, but instead, add a version number to the row and check it when we update it. If the version number has changed since we read the row, it means that someone else has updated it in the meantime, and we need to retry the operation with the updated row.

To implement optimistic locking, we need to add a version column to our users table:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  version INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

We'll use the version column to track the version of each row.

Now, let's update our Ktor endpoint to use optimistic locking. First, we'll read the user's row and check its version:

post("/users/{id}/age") {
    val id = call.parameters["id"]?.toInt() ?: throw BadRequestException("Invalid ID")
    val newAge = call.receive<Int>()

    var updated = false
    while (!updated) {
        val user = dslContext.selectFrom(USERS)
                             .where(USERS.ID.eq(id))
                             .fetchOne()

        if (user == null) {
            throw NotFoundException("User not found")
        }

        val oldVersion = user.version
        user.age = newAge
        user.version += 1

        val rowsUpdated = dslContext.update(USERS)
                                    .set(USERS.AGE, newAge)
                                    .set(USERS.VERSION, user.version)
                                    .where(USERS.ID.eq(id))
                                    .and(USERS.VERSION.eq(oldVersion))
                                    .execute()

        if (rowsUpdated == 1) {
            updated = true
        }
    }

    call.respond(HttpStatusCode.OK)
}

In this example, we use a while loop to retry the update until we successfully update the row with the correct version number. First, we read the user's row and get its current version number. Then we update the user's age and increment the version number. Finally, we execute the update query and check how many rows were updated.

If the update succeeded (i.e., one row was updated), we set updated to true and exit the loop. If the update failed (i.e., no rows were updated because the version number had changed), we repeat the loop and try again.

Note that we use the and(USERS.VERSION.eq(oldVersion)) condition in the WHERE clause to ensure that we only update the row if its version number is still the same as the one we read earlier.

Trade-Offs

Optimistic and pessimistic locking are two essential techniques used in concurrency control to ensure data consistency and correctness in multi-user environments.

Pessimistic locking prevents other users from accessing a record while it is being modified, while optimistic locking allows multiple users to access and modify data concurrently.

A bank application that handles money transfers between accounts is a good example of a scenario where pessimistic locking is a better choice. In this scenario, when a user initiates a transfer, the system should ensure that the funds in the account are available and that no other user is modifying the same account's balance concurrently.

In this case, it is critical to prevent any other user from accessing the account while the transaction is in progress. The application can use pessimistic locking to ensure exclusive access to the account during the transfer process, preventing any concurrent updates and ensuring data consistency.

An online shopping application that manages product inventory is an example of a scenario where optimistic locking is a better choice.

In this scenario, multiple users can access the same product page and make purchases concurrently. When a user adds a product to the cart and proceeds to checkout, the system should ensure that the product's availability is up to date and that no other user has purchased the same product.

It is not necessary to lock the product record as the system can handle conflicts during the checkout process. The application can use optimistic locking, allowing concurrent access to the product record and resolving conflicts during the transaction by checking the product's availability and updating the inventory accordingly.

Conclusion

When designing and implementing database systems, it's important to be aware of the benefits and limitations of both pessimistic and optimistic locking strategies.

While pessimistic locking is a reliable way to ensure data consistency, it can lead to decreased performance and scalability. On the other hand, optimistic locking provides better performance and scalability, but it requires careful consideration of concurrency issues and error handling. 

Ultimately, choosing the right locking strategy depends on the specific use case and trade-offs between data consistency and performance. Awareness of both locking strategies is essential for good decision-making and for building robust and reliable backend systems.