Locking: Shared vs. Exclusive

Imagine our OnlineStore is having a massive sale, and two customers, Alex and Ben, are both eyeing the last Laptop in stock. They both click “Buy Now” at almost the exact same second! How does the database ensure that only one of them gets the laptop and the stock count is updated correctly, without selling the same laptop twice or, worse, showing a negative stock? Or, picture a scenario where a store manager is updating the price of a “Smartphone” while a customer is in the middle of their checkout process with that very phone. We wouldn’t want the customer to suddenly see a different price, right? These are the kinds of tricky situations that databases handle every day using a concept called locking. Locking is like a traffic control system for data, ensuring that when multiple people or processes try to access or change data at the same time, everything happens in an orderly way and the data remains accurate.

In this lesson, we’re going to dive into two fundamental types of locks: Shared Locks and Exclusive Locks. By the end of our session, we’ll be able to:

• Understand why locking is essential for maintaining data integrity in a multi-user database environment.

• Define and differentiate between Shared Locks and Exclusive Locks.

• Recognize scenarios where each type of lock is appropriately used.

• Appreciate how these locks help prevent common data conflict issues.

Let’s get started on understanding how MySQL keeps our data consistent even when things get busy!

The why: Importance of locking

Before we jump into the types of locks, let’s briefly touch upon why we need locking in the first place. In any database system like MySQL, it’s very common for multiple users or application processes to try and read or modify data at the same time. This is called concurrency. While concurrency is great for performance and responsiveness (imagine if only one customer could browse our OnlineStore at a time!), it also brings challenges.

If there are no controls in place, simultaneous operations can lead to problems like:

• One user overwrites another user’s changes without realizing it (lost updates).

• A user reading data that is in the middle of being changed and is not yet consistent (dirty reads).

• A user gets different results when they read the same data multiple times within a single transaction if another user changes it in between (non-repeatable reads).

Locking is one of the primary mechanisms databases use to manage concurrency and prevent these issues. By controlling who can access or modify data and when, locks ensure that transactions are processed in a way that maintains the accuracy, consistency, and integrity of our data. Think of locks as rules of courtesy: Excuse me, I’m currently working with this piece of information, could you please wait until I’m done before you change it?

Now, let’s explore the two main types of locks we’ll encounter.

Shared locks: Reading together

Imagine a library. Multiple people can read copies of the same popular book simultaneously without any issues. They aren’t changing the book’s content, just consuming its information. A Shared Lock (often called a read lock) in a database works on a similar principle.

A Shared Lock (S-lock) allows multiple transactions to read a specific piece of data concurrently. If a transaction holds a Shared Lock on a resource (like a row in a table), ...