Multi-Version Concurrency Control

Imagine our OnlineStore during a massive holiday sale. Hundreds of customers are browsing products, adding items to their carts, and checking out, all at the same time! If customer A is trying to buy the last ‘Laptop’ in stock, and customer B is just trying to view its price, how does the database make sure both actions can happen smoothly without customer B having to wait for customer A to finish, or customer A’s purchase being messed up by customer B’s view? This is where a clever mechanism called multi-version concurrency control (MVCC) comes into play, acting like an efficient traffic controller for data. It’s a fundamental concept that allows many users to access and modify data simultaneously without tripping over each other, ensuring both speed and accuracy.

In this lesson, we’ll explore MVCC. By the end, we should be able to:

• Understand the core concept of Multi-version concurrency control (MVCC).

• Explain why MVCC is essential for good database performance and data consistency.

• Get a glimpse into how MVCC operates within MySQL, particularly with the InnoDB storage engine.

• Recognize the significant benefits and some considerations of using MVCC.

Let’s dive in and see how MVCC helps keep our database running efficiently, even under heavy load!

Understanding concurrency and its challenges

In almost any database application, especially one like our OnlineStore, it’s rare for only one person (or process) to be using the data at a time. We’ll often have many users and application processes trying to read and write data simultaneously. This is called concurrency.

Now, concurrency is great because it means more work can get done. However, it also brings challenges. If we don’t manage these simultaneous operations carefully, we can run into problems like:

• Dirty reads: One transaction reads data that another transaction has changed but not yet committed. If the changing transaction rolls back, the first transaction has read dirty or incorrect data.

• Non-repeatable reads: A transaction reads the same row twice but gets different data each time because another transaction modified that row and committed the change in between the reads.

• Phantom reads: A transaction runs a query twice, and the second time, it sees new rows that weren’t there the first time (or some rows are gone) because another transaction added (or removed) rows that match the query’s criteria and committed.

One way to handle these is by using locks. A lock on a piece of data can prevent other transactions from modifying it (or even reading it, in some cases) until the first transaction is done. However, extensive locking can create bottlenecks. If everyone has to wait in line for locks to be released, the database slows down, and users get frustrated. We need a way to allow high concurrency while still protecting the integrity and consistency of our data.

Introducing multi-version concurrency control (MVCC)

Multi-version concurrency control (MVCC) is a more sophisticated technique used by many modern database systems, including MySQL’s InnoDB storage engine, to handle concurrent access to data.

The core idea is that instead of making readers wait for writers, or writers wait for readers (by using locks extensively for read operations), MVCC allows each transaction to see a snapshot of the data as it existed at a certain point in time. This means that when a transaction reads data, it sees a consistent view, unaffected by ...