Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "what is the principle of MVCC implementation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What on earth is MVCC?

MVCC is a multi-version controller, which is characterized by the fact that different transactions can read different versions of data at the same time, so as to solve the problem of dirty reading and unrepeatable reading.

You have seen this explanation no less than dozens of times! But do you really understand what a multiversion controller is?

Life case: moving

Recently, Xiao Q moved to a new home with his girlfriend, because he had to pay the property fee of that month when he left the community.

So Xiao Q and his girlfriend logged in to the property payment system provided by the community at the same time.

Pessimistic concurrency control

Suppose Xiao Q is checking the amount of fees that need to be paid in that month, and the data queried by Xiao Q has been locked.

Then Xiao Q's girlfriend cannot access the data until Xiao Q's payment is completed or the pessimistic lock is released when Xiao Q quits the system.

Pessimistic locks guarantee that only one thread can access at a time, and the default data conflicts during access, and then locks are added throughout the process.

Such a system has no sense of experience for users. If multiple people need to access a piece of information at the same time, they can only watch it on one device.

Optimistic concurrency control

While Xiao Q checks the arrears of property fees and pays, Xiao Q's girlfriend can also access the data.

Optimistic locks assume that conflicts will not occur even in a concurrent environment, so they will not do locking operations.

Instead, it is detected when the data is submitted, and if a conflict is found, the conflict information is returned.

Summary

Innodb's MVCC mechanism is an embodiment of optimistic locking, which can make multiple transactions read and write concurrently without locking, and solve the problem of read-write conflict, which greatly improves the concurrency of the system.

Pessimistic lock and optimistic lock

According to the granularity, locks are divided into table locks, row locks and page locks.

According to the mode of use, it can be divided into shared lock and exclusive lock.

According to the thought, it is divided into optimistic lock and pessimistic lock.

It must be clear that both optimistic and pessimistic locks are just an idea, not an actual locking mechanism.

1. Pessimistic lock (pessimistic concurrency control)

Pessimistic lock is actually pessimistic concurrency control, abbreviated PCC.

Pessimistic locking takes a negative attitude, believing that conflicts always occur every time the data is accessed, so each access must lock the data first and release the lock after completing the access.

Ensure that only a single thread can access it at the same time to achieve data exclusivity. At the same time, pessimistic lock is implemented by the lock mechanism of the database itself, which can solve the read-write, write-write conflict.

So in what scenarios can pessimistic locks be used?

Pessimistic lock is suitable for use in a concurrent environment with more writes and less readings. Although the concurrency efficiency is not high, it ensures the security of data.

two。 Optimistic lock (optimistic concurrency control)

Like pessimistic locks, optimistic locks are actually optimistic concurrency control, abbreviated as OCC.

Compared with the pessimistic lock, the optimistic lock thinks that even in the concurrent environment, the outside world will not conflict with the operation of the data, so it will not add the lock, but will formally detect the data conflict when the update is submitted.

If you find a conflict, either try again or switch to a pessimistic strategy.

Optimistic concurrency control is to solve write-write conflicts in database concurrency scenarios, which means to solve them in a lock-free way.

What problems have been solved by MVCC

The following problems arise in the case of concurrent transactions.

Dirty reading: reading data that has not been committed by other transactions.

Unrepeatable reading: when a transaction reads a piece of data, the data is inconsistent when it is read again because another transaction modifies the data and commits the transaction

Illusion: one transaction reads a range of data, while another transaction adds that range of data, and the results are inconsistent when read again.

The main purpose of the implementation of MVCC in the Innodb storage engine is to improve the concurrency ability of the database, to deal with read-write conflicts in a better way, and to achieve non-locking and non-blocking concurrent read and write.

Mvcc can solve dirty reading and non-repeatable reading. Mvcc uses snapshot reading to solve part of the phantom reading problem, but it still uses the current reading when it is modified, so there is still a phantom reading problem. The phantom reading problem is finally solved by using gap locks.

IV. Current reading and snapshot reading

Before you can understand how MVCC solves the problems caused by transaction concurrency, you need to understand two concepts: current read and snapshot read.

1. Current read

Add a shared lock, an exclusive lock to a read operation, and an exclusive lock to an DML operation, which is the current read.

Shared lock and exclusive lock are also called read lock and write lock.

Shared lock and shared lock coexist, but when you want to modify, add, and delete, you must wait until the shared lock is released.

Because in the Innodb storage engine, DML operations implicitly add exclusive locks.

Therefore, the record read by the current read is the latest record, and the lock is added when reading the data to ensure that other transactions cannot modify the current record.

two。 Snapshot read

If you see here, by default you know something about the isolation level!

The premise of snapshot read is that the isolation level is not serial level, and snapshot read at serial level will degenerate to current read.

The emergence of snapshot reading aims to improve transaction concurrency, and its implementation is based on the protagonist MVCC of this article, that is, multi-version controller.

MVCC can be thought of as a variant of row locks, but it avoids locking operations in many cases.

So the data read by the snapshot may not be the latest, but the previous version of the data.

Why mention snapshot reading? Because read-view is generated by snapshot reading, it is explained here in order to prevent the following concepts from being ambiguous.

3. How to distinguish between current reading and snapshot reading

Simple unlocked select belongs to snapshot reading.

Select id name user where id = 1

Corresponding to it is the current read, adding a shared lock and exclusive lock to the select.

Select id name from user where id = 1 lock in share mode;select id name from user where id = 1 for update; 5, MVCC implementation of three elements

Finally came to the most important part of this article, the previous description is to lay the groundwork for the principle.

Before that, you need to know that MVCC only applies under two isolation levels: REPEATABLE READ (repeatable) and READ COMMITTED (read commit).

The principle of MVCC implementation is realized by two implicit fields, undo log and Read view.

1. Implicit field

In the Innodb storage engine, two fields are hidden in each row of records when there is a clustered index, and if there is no clustered index, there is a hidden primary key for 6byte.

These two hidden columns show when a record was created and when a record was deleted.

This should not be understood as recording time, but storing transaction ID.

The two implicit fields are DB_TRX_ID,DB_ROLL_PTR, and there will be a DB_ROW_ID field without a clustered index.

DB_TRX_ID: record the transaction ID that created this data and last modified it

DB_ROLL_PTR: rollback the pointer to the previous version of this record

There is actually a delete flag field in the implicit field, that is, the record is updated or deleted. The deletion here does not mean the actual deletion, but changes the delete flag of this record to true. (this foreshadowing, is the deletion of the database really deleted? )

2. Undo log (rollback log)

Previously, the role of undo log only mentioned the rollback operation to achieve atomicity, but now the other role you need to know is to implement the MVCC multi-version controller.

Undo log is subdivided into two types, undo log produced by insert and undo log produced by update,delete.

The undo log generated by insert in Innodb is deleted after the transaction is committed, because the newly inserted data has no historical version, so there is no need to maintain undo log.

The undo log generated by both update and delete operations is of the same type and is needed for transaction rollback and snapshot reading, so multiple version information needs to be maintained. Only when the snapshot read and transaction rollback do not involve the log, the corresponding log will be deleted by the purge thread.

The purge thread cleans up the historical version of undo log, as well as the records of del flag tags.

The role of undo log in mvcc

So far, it is estimated that the role of undo log in mvcc is still a hoop.

What undo log holds is a version chain, which is connected using the DB_ROLL_PTR field.

A consistent view read view is generated when the database executes a select statement.

Then this read view is an array of all uncommitted transactions ID when querying. The smallest transaction ID in the array is min_id and the largest transaction ID created is max_id. The query data results need to be compared with read-view to get snapshot results.

So the role of undo log in mvcc is to compare the stored transaction ID with the consistency view to get the snapshot results.

3. Undo log underlying implementation

Suppose the initial data is the following figure

When an updated SQL statement update user set name = 'niuniu where id = 1' is executed, the record of undo log will be changed

That is, when an update statement is executed, the previous original data is copied to the undo log log.

At the same time, you can see that the latest record is connected to a line at the end, which means that DB_ROLL_PTR records the pointer address stored in the undo log log.

It may eventually be necessary to find historical data through pointers.

4. Read-view

A consistent view, read-view, is generated when a SQL statement query is executed, which consists of an array of all uncommitted transaction ID at the time of the query and the largest transaction ID that has been created.

The smallest transaction ID in this array is called min_id, and the maximum transaction ID is called max_id. The query data results are compared according to read-view to get snapshot results.

This leads to the following comparison rule, which uses the trx_id of the current record to compare with read-view. The comparison rule is as follows.

5. Version chain comparison rules

If it falls on trx_idmax_id, it means that this version is generated by a transaction started in the future and is definitely invisible.

If you are in min_id

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.