Concurrency handling is an essential aspect of any multi-user application. It helps developers avoid data inconsistencies by maintaining a proper order for database writes. For example, suppose two users are trying to update the same data fields in an application. In such instances, you must decide which update is valid and which one should be discarded, which is a pretty challenging task.

This article discusses how optimistic and pessimistic concurrency handling concepts can be used in such situations to maintain data consistency to give you a better understanding.

Optimistic Concurrency

As the name implies, optimistic concurrency considers the best scenario when handling concurrency. It assumes that conflicts between transactions will happen infrequently and allows transactions to happen in an unsynchronised manner without any interference. So, no user will be blocked when performing transactions. However, it will also check for any conflicts between transactions when right before the changes are committed to the database. If there are any conflicts, the user will have to interfere and complete the transactions manually.

The optimistic concurrency approach allows developers to scale the application and serve multiple users simultaneously due to its non-locking behaviour. Most importantly, it does not have any performance impact on the application.

That's the absolute basics of optimistic concurrency. Next, let's see how we can implement it.

How to Implement Optimistic Concurrency

You can easily implement optimistic concurrency in any database by using a timestamp or a version number. Then, when a user is trying to commit a change to the database, you can compare the version number or the timestamp and decide which record to keep.

Using MySQL

As mentioned, you just need to check the version number or the timestamp to ensure optimistic concurrency. In MySQL, you can easily implement this using a WHERE condition.

For example, assume you have a table named users with three columns.

create table users( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(100) NOT NULL, versionINT NOT NULL, PRIMARY KEY ( user_id ) );

Initially, the version number of each record will be equal to 1. When you write the update query, you need to bump the version of the record. For example, if the version was 1 before the update, you need to change it to 2 with the update query.

Also, you need to ensure that any other transaction hasn't changed the version number while you update the record. For example, if you edit the record when the version number is 1, it should remain in 1 until you make the update. Otherwise, your transaction will be discarded.

SELECT * from users WHERE user_id = 1; UPDATE users SET user_name = 'Chameera', version = version + 1

WHERE user_id = 1AND vrsion = 1;

Advantages of Optimistic Concurrency

• Does not require locks

• Offers support to scale applications

• Can serve multiple users at once

• No deadlock situations

• Does not affect performance

Disadvantages of Optimistic Concurrency

• Requires maintenance of versions or timestamps

• Requires manual implementation of concurrency handling logic

When to Use Optimistic Concurrency

Usually, the optimistic concurrency approach is suitable for applications with fewer conflicts. So, when you do not have many data conflicts, it will reduce the number of rollbacks required and the total cost of rollbacks.

It also works well with application scaling. So, if your application has requirements to scale or serve multiple users simultaneously, optimistic concurrency is the best option.

Pessimistic Concurrency

Pessimistic concurrency is the opposite of optimistic concurrency. It assumes that conflicts between transactions can happen often and blocks data records when a user starts to update. So, other users will not be able to update that data until the lock is released.

The pessimistic concurrency approach has three lock modes, and users will be able to read the locked record based on the applied lock.

• Shared

   - Allows other users to read the data record, but they can't update it.

• Exclusive

   - Only the user who applied the lock can read or update the data record. No other locks can be applied until the user releases the lock.

• Update

   - Similar to the exclusive approach, only the user who applied the lock can read or update the data record. However, users can apply update locks when another user already has a shared lock.

Compared to the optimistic approach, the pessimistic concurrency approach is not a good option for applications with scaling requirements. Also, it keeps the lock durations to a minimum to avoid any performance issues.

Now, let's see how we can implement pessimistic concurrency.

How to Implement Pessimistic Concurrency

In pessimistic concurrency handling, you need to lock the data records when you update them in order to prevent others from updating or reading them simultaneously. Usually this is implemented using a lock mechanism provided by the database.

Using MySQL

MySQL provides locks to apply when you read a data record for update. For example, let's consider the same user table explained above:

create table users( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(100) NOT NULL, PRIMARY KEY ( user_id ) );

If you want to update a record, you need to read that record from the database and apply a lock to that record. In MySQL, you can apply an exclusive lock by using FOR UPDATE.

SELECT * FROM users WHERE user_id = 1 FOR UPDATE;

Now, the record you selected has an exclusive lock. This means other users will not be able to read or update it until you release the lock by executing the update.

UPDATE users SET user_name = 'Chameera' WHERE user_id = 1;

However, you need to disable the automatic submission function of MySQL by setting AUTOCOMMIT = 0, when using the pessimistic concurrency approach. The below example shows the finalised SQL script.

SET AUTOCOMMIT =0; BEGIN; SELECT * FROM users WHERE user_id =1 FOR UPDATE; UPDATE users SET user_name ='Chameera' WHERE user_id =1;

Advantages of Pessimistic Concurrency

• Built-in database support

• Prevents conflicts from the moment the transaction starts

Disadvantages of Pessimistic Concurrency

• There can be performance issues if the lock duration is high

• Deadlock situations can happen

• Affects application scalability

• Is not supported by all the databases

• High resource consumption due to locking and waiting

When to Use Pessimistic Concurrency

The pessimistic concurrency handling approach is suitable for applications with heavy data conflicts because it will block conflicts before processing transactions, meaning you won't need to roll back transactions later.

On the other hand, the pessimistic concurrency approach does not support scaling, so you need to make sure that your application does not have a critical scaling requirement before using it.

Optimistic vs. Pessimistic

In the end, optimistic and pessimistic concurrency handling share plenty of similarities. However, you should clearly understand the key differences before choosing the best one for your project.

The below table provides a summary of the key differences between optimistic and pessimistic concurrency.

Wrapping Up

Data consistency is one of the most important aspects of any application. I hope now you have a good understanding of their advantages, disadvantages and use cases to choose the best approach for your project.

Thank you for reading.