Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand MVCC in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Relational database management systems use MVCC (Multiversion Concurrency Control multi-version concurrency control) to avoid the concurrency problem of write operations blocking read operations. MVCC is a mechanism that ensures non-conflict between read and write through the use of multiple versions of data. Different databases have different implementations, which is also a headache for database systems.

Two different ways to implement MVCC

The first implementation is to keep multiple versions of the data records in the database, and when these different versions of the data are no longer needed, the garbage collector collects the records. This method is adopted by PostgreSQL and Firebird/Interbase, and SQL Server uses a similar mechanism, except that the old version of the data is not saved in the database, but in another database, tempdb, which is different from the main database.

The second implementation saves only the latest version of the data in the database, but dynamically reconstructs the old version of the data when using undo, which is used by Oracle and MySQL/InnoDB.

What problem is MVCC trying to solve?

Most MYSQL transactional storage engines, such as InnoDB,Falcon and PBXT, do not use a simple row locking mechanism. In fact, they are all used with MVCC- multi-version concurrency control. Tragically, Falcon, a storage engine, died prematurely and was originally a strong competitor to InnoDB, but the result was sighing. See: gossip derived from the falcon storage engine in MySQL (R5 notes, day 23)

As we all know, the lock mechanism can control concurrent operations, but its system overhead is high, and MVCC can replace row-level locks in most cases. Using MVCC can reduce its system overhead.

MVCC implementation

MVCC is achieved by keeping a snapshot of the data at a certain point in time. MVCC of different storage engines. The MVCC implementation of different storage engines is different, such as optimistic concurrency control and pessimistic concurrency control.

InnoDB's MVCC is achieved by saving two hidden columns after each row of records, each of which holds the creation time of the row, and one saves the deletion time of the row. What is stored here is not the actual time value, but the system version number (which can be understood as the ID of the transaction). Before starting a new transaction, the system version number will be automatically incremented, and the system version number at the beginning of the transaction will be used as the ID of the transaction.

After making simple changes, let's do some simple examples to illustrate.

1) during the insert operation: the creation version number of the record is the transaction version number.

For example, if a record is inserted and the transaction id is assumed to be 1, the record is as follows: that is, the creation version number is the transaction version number.

Id name create version delete version 1test 1

2) during the update operation, the old row is marked as deleted, and the delete version number is the transaction version number, and then a new row of records is inserted. For example, for the above row of records, the transaction id is 2 to update the name field

Update table set name= 'new_value' where id=1

Id name create version delete version 1 test 12 1 new_value 2

3) when deleting the operation, the transaction version number is used as the deletion version number. such as

Delete from table where id=1

Id name create version delete version 1new_value23

4) query operation:

As you can see from the above description, only records that meet the following two conditions can be queried by a transaction:

(1) the delete version number is greater than the current transaction version number, that is, the delete operation is done after the current transaction starts.

(2) the creation version number is less than or equal to the current transaction version number, that is, the record creation is in the transaction (equal to the situation) or before the transaction starts.

This ensures that transactions do not affect each other. From here, we can also understand a way to improve the performance of the system, that is: through the version number to reduce lock contention.

In addition, only read-committed and repeatable-read transaction isolation levels can be used because MVCC,read-uncommited is read uncommitted, so there is no version problem, while serializable locks all read rows.

Of course, the above contents are secondary absorption, do some filtering and simple summary, the follow-up will continue to summarize and careful analysis, compare the similarities and differences between Oracle and MySQL MVCC, the defects of MVCC and so on.

On how to understand the MVCC in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.