Pessimistic and Optimistic Locking With MySQL, jOOQ, and Kotlin
Managing concurrent entry to shared information is usually a problem, however through the use of the correct locking technique, you’ll be able to be sure that your purposes run easily and keep away from conflicts that would result in information corruption or inconsistent outcomes.
On this article, we’ll discover how you can 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 method.
Whether or not you’re a newbie or an skilled developer, the concept is to stroll away with insights into the ideas of concurrency management and how you can apply them in follow.
Information Mannequin
To illustrate we have now a desk known as customers in our MySQL database with the next schema:
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
identify VARCHAR(255) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
);Pessimistic Locking
We need to implement pessimistic locking when updating a person’s age, which implies we need to lock the row for that person after 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 are able to 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 can lock the row for the person with the required ID after we execute the question.
Subsequent, we are able to 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()Notice that we have to carry out the replace throughout the similar 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 accomplished within the subsequent part.
Ktor Endpoint
Lastly, here is an instance Ktor endpoint that demonstrates how you can use this code to replace a person’s age:
submit("/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("Person 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’ll be able to see, we first learn the person’s row and lock it utilizing jOOQ’s forUpdate() methodology. Then we examine 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 after we learn it, however as an alternative, add a model quantity to the row and examine it after 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,
identify 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 examine its model:
submit("/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("Person 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 examine what number of rows had 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 had been up to date as a result of the model quantity had modified), we repeat the loop and take a look at once more.
Notice 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 methods utilized in concurrency management to make sure information consistency and correctness in multi-user environments.
Pessimistic locking prevents different customers from accessing a report 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 efficient 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 vital to stop every 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 in the course of the switch course of, stopping any concurrent updates and guaranteeing information consistency.
A web-based purchasing 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 isn’t essential to lock the product report because the system can deal with conflicts in the course of the checkout course of. The applying can use optimistic locking, permitting concurrent entry to the product report and resolving conflicts in the course of the transaction by checking the product’s availability and updating the stock accordingly.
Conclusion
When designing and implementing database programs, it is necessary to pay attention to the advantages and limitations of each pessimistic and optimistic locking methods.
Whereas pessimistic locking is a dependable means to make sure information consistency, it will probably result in decreased efficiency and scalability. Alternatively, optimistic locking supplies higher efficiency and scalability, however it requires cautious consideration of concurrency points and error dealing with.
In the end, selecting the best locking technique will depend on the precise use case and trade-offs between information consistency and efficiency. Consciousness of each locking methods is crucial for good decision-making and for constructing strong and dependable backend programs.
