DataBase interviews: Briefly describe the difference between optimistic locks and pessimistic locks and the usage scenarios

Have you ever been asked a similar question in an interview? Or you will meet in the future, let’s explore and master it together

Thank you for reading this article. More Interview Questions here:
https://programmerscareer.com/software-interview-set/

Topic 1.1: Introduction to MySQL and Locking Mechanisms

Let’s embark on a new journey of learning about MySQL and Locking Mechanisms.

MySQL is one of the world’s most renowned open-source relational database management systems (RDBMS). It’s popular for web-based applications and online publishing and is a central component of the LAMP open-source web application software stack.

Now let’s talk a bit about why we need locking mechanisms in databases. Imagine a scenario where two individuals are attempting to withdraw money from the same bank account simultaneously. If there isn’t a mechanism to prevent it, they could both check the account balance at the same time, see that there are sufficient funds, and proceed to withdraw more money than is actually in the account. This is known as a race condition and could lead to serious data integrity issues. This is where locking mechanisms come into play!

Topic 1.2: Overview of Locking Mechanisms

So, let’s dive deeper into the world of Locking Mechanisms in MySQL. As mentioned before, locking contributes significantly to maintaining data integrity, especially in multi-user database environments.

Locking in databases essentially controls how transactions are accessed so that each transaction sees a consistent snapshot of the data it is accessing. The main types of locking used in MySQL are:

1. Shared Locks (Read Locks): Shared locks are used when executing a read operation on data. They allow concurrent transactions to read (select) a resource with the guarantee that no transactions will be able to write (update/delete) it.
2. Exclusive Locks (Write Locks): Exclusive locks are issued when executing a data modification operation. They make sure that the transaction that holds the lock is the only transaction that can read or write to the resource.

In the next chapters, we will venture into two popular types of locking mechanisms- Optimistic Locking and Pessimistic Locking. The choice of which to use generally depends on the specific requirements of your system, such as the probability of concurrent transactions conflicting.

Topic 1.3: Understanding Optimistic Locking

Time to take a closer look at Optimistic Locking. This is a strategy that can be implemented in multi-user databases to handle simultaneous updates.

Optimistic Locking works on the assumption that multiple transactions can complete without affecting each other; therefore, it allows multiple transactions to access the same record for edits. This method is useful in systems where there is low contention for data.

Here’s a simple way how Optimistic Locking works:

1. A record is fetched from the database for an update.
2. Just before the update, the application checks if another user has changed the record since it was last fetched.
3. If the record was not updated by others, the application can perform its update and everything proceeds smoothly.
4. If the record was updated by someone else, the application typically either informs the user and aborts the transaction or automatically retries the transaction.

The main advantage of optimistic locking is its higher efficiency. It avoids the overhead of acquiring and releasing locks and avoids having transactions wait for locks.

However bear in mind, nothing comes without a flip side! In an environment where contention for data is high and there are many updates to data, there could be many transaction collisions leading to a lot of rollbacks, which could lead to performance issues.

Topic 1.4: Understanding Pessimistic Locking

Let’s navigate into the sea of Pessimistic Locking now. This mechanism in MySQL is based on a completely different assumption from Optimistic Locking. It assumes that conflict is likely to happen and therefore enforces stringent controls to prevent this.

Here’s how Pessimistic Locking works:

1. When a record is fetched for updating, an exclusive lock is immediately acquired.
2. Until the lock is released, no other transaction can update this record.
3. The lock is released when the transaction is completed, and other transactions can then acquire the lock for this record.

Pessimistic Locking is a surefire way to prevent conflicts, as it does not allow another transaction to proceed if it can result in a conflict. It’s a good fit for environments where contention for data is high and records are frequently updated.

But remember, every coin has two sides! The downside of this approach is that it can lead to reduced concurrency and can impact system performance as transactions may be held waiting for locks for extended periods.

Topic 1.5: Comparing Optimistic and Pessimistic Locking.

You now are familiar with both Optimistic and Pessimistic Locking, and understanding when to use each one can significantly influence the performance and reliability of your applications.

Optimistic Locking assumes conflicts are rare and mostly avoids the need for acquiring and releasing locks. It can result in higher performance under low contention scenarios because it causes fewer blocks. However, for systems where contention is high, and conflicts are frequent, the cost and frequency of rollbacks can degrade performance.

On the other hand, Pessimistic Locking assumes conflicts will commonly occur and uses locks to prevent them. This strategy can be advantageous in high contention scenarios because it avoids the need for conflict-resolution related rollbacks. However, the wait time associated with acquiring locks can degrade performance.

So, the golden rule is:

Opt for Optimistic Locking when conflicts are rare.
Opt for Pessimistic Locking when conflicts are expected.

That’s a brief comparison of Optimistic and Pessimistic Locking in MySQL.

Topic: 1.6 Use Case Scenarios for Locking Mechanisms

Fantastic! We now understand the primary locking mechanisms let’s look at some real-world scenarios where these mechanisms could be beneficial.

1. A banking system:
   Imagine a banking application where transactions happen regularly. These transactions need to be consistent and secure. In such cases, a Pessimistic Locking mechanism is favourable as it ensures that once a user starts a transaction, no one else can modify the data, ensuring data integrity.
2. A ticket booking application:
   Consider an online ticket booking system where multiple users are trying to book a limited number of tickets. Here, Optimistic Locking could be beneficial as it would allow multiple users to access the ticket booking function concurrently.
3. A content management system:
   If you’re working on a Content Management System where users are updating their blog posts/articles, Optimistic Locking can be a good choice. Since the odds of two users trying to edit the same article at the same time are comparatively low, the system can handle those occasional conflicts.
4. A Stock trading application:
   In a Stock trading application where a split-second delay could mean significant financial loss, Pessimistic Locking could help by instantly blocking other transactions until one completes.

Remember, the decision to use Optimistic or Pessimistic Locking depends greatly on the nature of the application, the likelihood of conflicts, the level of concurrency required, and the tolerance for delay.

Topic: 1.7 Review and Assessments

1. Introduction to MySQL and Locking Mechanisms: We discussed how MySQL functions, the importance of locking mechanisms, and how it helps with concurrent database accesses.
2. Overview of Locking Mechanisms: We looked at a variety of locking mechanisms, their importance in maintaining data integrity, and handling concurrent access.
3. Understanding Optimistic Locking: We did a deep dive into the concept of optimistic locking, its pros and cons, and its implementation in MySQL.
4. Understanding Pessimistic Locking: We explored pessimistic locking, including its strengths, weaknesses, and how it can be implemented in MySQL.
5. Comparing Optimistic and Pessimistic Locking: We compared these two locking mechanisms and concluded that the choice highly depends on the particular use-cases and system requirements.
6. Use Case Scenarios for Locking Mechanisms: We looked through possible real-life application scenarios where these locking mechanisms can offer benefits.

Example Problem:

Let’s take an example: you have a banking database in MySQL with a table named ‘Account’ storing users’ account balance. Two different financial transactions are trying to deduct money from the same account simultaneously. How would you handle this situation using both optimistic and pessimistic locking mechanisms in MySQL?

Answer:

For Optimistic Locking, you can handle this by using a version column in the Account table. Here’s how:

• Transactions first read the account balance and note down the version. Before they update the account, they check if the version is still the same. If the version had changed, that would mean that another transaction has updated the account balance in the meantime, and therefore, the current transaction is rolled back. This way, we avoid inconsistent deductions.

For Pessimistic Locking, you would lock the account for every financial transaction. Here’s how:

• Transactions lock the account immediately upon reading the account balance. Only one transaction can hold the lock at a time, and it holds it until it finishes updating the account balance. All other transactions that attempt to read the account balance while the lock is held will be blocked until the lock is released.

Now let’s test your knowledge.

Question 1 (Easy):

In what scenario would you choose Optimistic Locking over Pessimistic Locking?

Question 2 (Intermediate):

What could be a potential downside of using Pessimistic Locking in a high throughput system, and how could this downside be mitigated?

Question 3 (Hard):

Can you describe a scenario where neither Optimistic nor Pessimistic Locking is suitable, and a different locking or concurrency control mechanism would be required?

中文文章: https://programmerscareer.com/zh-cn/mysql-interview5/
Author: Wesley Wei – Twitter Wesley Wei – Medium
Note: If you choose to repost or use this article, please cite the original source.