Read Innodb MVCC implementation principle

Database transaction tips

What is database isolation level for?

Database concurrency can cause dirty reads, unrepeatable reads, phantom reads, etc., so the isolation level of things is used to solve the problem.

Let’s take a look at dirty reading, non-repeatable reading and magic reading.

• Dirty read: Object A reads uncommitted data from object B.

• Non-repeatable read: the same query condition of object A is queried several times, but the read data is different. The different emphasis lies in update and DELETE

• Illusionary reading: the same query condition of thing A, query many times, read different data, different emphasis lies in insert

Transaction isolation level

Read uncommitted

Do not solve the problem of dirty read, unrepeatable read, unreal read, do not add any lock to all data.

Reading has been submitted

Does not solve the problem of unrepeatable read and illusory read, but resolves the problem of dirty read and reads the submitted data. SQL > alter table lock (); SQL > alter table lock (); SQL > alter table lock ()

Repeatable read

The isolation level does not solve the illusion problem, but it does solve the dirty and unrepeatable read problem. In fact, in MySQL, the isolation level also solves the illusion problem.

serialization

Solve all problems caused by concurrent objects. Through read and share lock, write and exclusive lock control, read and write mutual exclusion, pessimistic lock theory.

What is a MVCC

Multi-version concurrency control (MVCC) is a lockless concurrency control used to resolve read-write conflicts. In popular terms, MVCC saves historical versions of data and processes whether data is displayed according to the comparison version number. In this way, read operations do not block write operations, write operations do not block read operations, and avoid dirty and unrepeatable reads. MySQL’s repeatable read isolation level is implemented with this idea.

What are current and snapshot reads

The current reading

Current read means that it is reading the latest version of the record. Because the version of the record it is reading is the latest version, it must ensure that other transactions cannot modify the current record while reading, so it needs to lock the read record. Update TB set fd = fd + 1

Read the snapshot

Snapshot reads can be interpreted as snapshot reads. The prerequisite for snapshot reads is that the isolation level is not serial, because at the serial isolation level, snapshot reads can be understood as current reads. The appearance of snapshot reading mainly solves the problem of reading without locking and reduces the lock cost. Its implementation is based on multi-version concurrency control, namely MVCC; Because it is based on multi-version concurrency control, records read using snapshot reads are not necessarily the latest records.

Relationship between current read, snapshot read and MVCC

Specifically, MVCC is based on “maintaining multiple versions of a piece of data so that read operations do not block write operations and write operations do not block read operations to fulfill the requirement of repeatable reads.

Snapshot read is actually a manifestation of MVCC, non-blocking read. In contrast, the current read is a pessimistic lock. Every time the query operation is performed, mysql considers it an unsafe operation and locks it to ensure security. However, the data read each time is the latest data

MVCC implementation principle

MVCC model is implemented in mysql mainly by hidden fields, undolog, read-view and so on.

Hidden fields

In addition to the user-defined fields, hidden fields also contain other attribute fields, which are added by default by the system, such as roll_pointer, trx_ID and other fields.

roll_pointer

Roll back the pointer to the previous version of this record

trx_id

The transaction ID, or version number, that records the transaction that operates on the data, is used for version comparisons to find snapshots

db_row_id

Hide ID, when creating a table without proper index as a clustered index, will use the hidden ID created clustered index, studied mysql index of knowledge should be able to understand

Undo log

The Undo log is used to record the logs before data modification. Before table information modification, data is copied to the Undo log. When a transaction is rolled back, data can be restored using the logs in the Undo log. In MVCC multi-version control, different transaction versions can have their own independent snapshot data versions by reading historical version data of undo log

Read view

Innodb gets a read_view, or consistent view, before every SQL statement is executed. The result of our query is then matched against several important properties of the Read-view to get the correct snapshot result.

Read View has several important properties:

Trx_ids: set of active (uncommitted) transaction version numbers for the current system; Low_limit_id: maximum transaction version number + when the current read view is created1"; Up_limit_id: "the system was in the minimum version of an active transaction" when the current Read view was created. Creator_trx_id: transaction version number for creating the current Read View;Copy the code

Matching rules with Read View:

1. If the data transaction ID <up_limit_id is displayed

If the data transaction ID is less than the minimum active transaction ID in the Read View, you can be sure that the data existed before the current transaction started and therefore can be displayed.

2. If data transaction ID>= LOW_limit_id is not displayed

The data transaction ID is greater than the maximum transaction ID of the current system in the Read View, indicating that the data was generated after the current Read View was created, so the data is not displayed. (It doesn’t matter if you don’t understand it, you will be enlightened by my case analysis later)

3. Up_limit_id < data transaction ID< low_limit_ID matches active transaction set TRX_IDS

If the transaction ID of the data is greater than the minimum active transaction ID and less than or equal to the maximum transaction ID of the system, it is possible that the data was not committed when the current transaction started. So we need to match the transaction ID of the data to the active transaction set trx_IDS in the current read View:

Case 1: If the transaction ID does not exist in the trx_IDS set (which means that the transaction was committed when the Read view was generated), the data in this case can be displayed.

Situation 2: If transaction ID exists trx_ids shows the read view generated when the data has not been submitted, but if the data transaction ID equals creator_trx_id, it means that the data is generated by the current transaction oneself, generated data itself, of course, can see, So the data can be displayed in this case.

Case 3: If the transaction ID has trx_IDS that are not equal to creator_trx_id, then the data was not committed at the time the read view was created and was not generated, so the data cannot be displayed in this case.

When the transaction ID of the data does not meet the above three conditions of Read View, the historical version data is obtained according to the undo log and then matched with the Read View condition until a historical data that meets the conditions is found, or no result is returned.

Case analysis

Insert a row of user data into the table user

+------+-------+-------+
| id | name | trx_id  |
+------+-------+-------+
| 1 |Side dishes| 101 |
Copy the code

Objects A, B, and C do the following simultaneously

Update user set name = ‘A’;

Select * from user where id = 1;

Update user set name = ‘C’;

The execution flow chart is as follows:

Flowchart description:

(1), things A and C perform update operation first, and undo log is generated

(2), object B generates a log read view according to the undo log when executing the SQL query

(3), the undo log snapshot result is continuously matched with the condition of read View until the data is matched, and then the result is returned.

extends

How Read View works at transaction isolation level

Each query that reads the same transaction at the committed level gets a new copy of the Read View. This can cause problems with inconsistent reads in the same transaction.

At the repeat read level, a copy of the Read View is fetched only once in a transaction to ensure that the data is the same in each query.

Original link :4m.cn/01SFw