directory

• background
• The premise to review
  • What is a MVCC
  • What are current and snapshot reads
  • Relationship between current read, snapshot read and MVCC
  • MVCC, optimistic lock, pessimistic lock relationship
• MVCC implementation principle
• The whole process
• eggs
• What is the difference between InnoDB snapshot reads at RR and RC isolation levels
• Small talk
• Welcome to join my public number pk factory together

background

Write << school recruitment MySQL those things >> series of articles, on the one hand to help college students can know the interview process of big factory in advance, understand what kind of talent big factory needs, how to prepare, go all out; On the other hand, it is also self-consolidation of knowledge, because I was recruited from the school, I also experienced the process of pain and happiness, and I hope to share my own experience.

Next I will write a lot of articles about school enrollment, such as << school enrollment Redis those things >>, << school enrollment go those things >>, << school enrollment kafka those things >> and so on, stay tuned.

The premise to review

What is a MVCC

Multi-version concurrency control (MVCC) is a lockless concurrency control used to resolve read-write conflicts. This means that transactions are assigned a time-stamp of one-way growth, a version is saved for each change, the version is associated with the transaction stamp, and the read operation reads only the snapshot of the database before the transaction started. In this way, the read operation does not block the write operation, and the write operation does not block the read operation. In addition, the dirty read and unrepeatable read are avoided.

What are current and snapshot reads

Mysql InnoDB: Current read and snapshot read

• 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.

• 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 the basic 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.”
• 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 to be an unsafe operation and locks it to ensure security, but the data read each time is the latest data
• The concrete implementation of MCC model in mysql is mainly by hidden fields, undolog, read-view, etc. The concrete implementation logic can be seen from the following implementation principle of MVCC

MVCC, optimistic lock, pessimistic lock relationship

• MVCC + optimistic locking

MVCC resolves read/write conflicts, optimistic locking resolves write conflicts

• MVCC + pessimistic locking

MVCC resolves read/write conflicts, pessimistic locking resolves write conflicts

This combination maximizes the concurrency performance of the database and solves the problems caused by read/write conflicts and write conflicts.

MVCC implementation principle

MVCC is multi-version concurrency control to resolve read/write conflicts. It consists of hidden fields, undolog logs, and read-view. So take a look at these attributes first, understand their meaning, easy to understand the implementation of MVCC principle.

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 rolls back the pointer to the previous version of the record trx_ID transaction ID, record the transaction ID that was created/modified for version comparison to find the snapshot

As shown in the table, the name and age attributes are user-defined, while trx_id and roll_pointer represent hidden attributes that are added by default to the database. In this table, trx_id represents the transaction ID that operates on this record, and roll_pointer represents the rollback pointer that points to the previous version. This is usually used with undolog logs

Undolog log

Undolog logs store all operations on a record, concatenating versions in a linked list

For example,

The first step:For example, transaction 1 inserts a data entry into the person table with name maimo, age 22, transaction ID 1, and rollback pointer assumed to be NULL, as shown in the figure below

The second step:Add the operation record of transaction 1 to the undolog log, take the operation record of transaction 2 as the latest record of data, and point the hidden field roll_pointer(rollback pointer) in transaction 2 to transaction 1 in series

Step 3:The operation record of transaction 2 is first migrated to undolog log. The operation record of transaction 3 is taken as the latest record of data. The hidden field roll_pointer(rollback pointer) in transaction 3 points to transaction 2 and is connected in series

As you can see from the above figure, whenever a transaction operates on this data, the most recent data before the operation is first migrated to the Undolog log, with the record of the current transaction operation as the most recent data, and the hidden field ROLL_pointer pointing to the previous version

Read-view (consistency view)

When a transaction executes a SQL query for the first time, a read-view is generated. The read-view consists of an array of uncommitted transaction ids (min_id) and max_id (max_id). The result of the query must be compared with the read-view to obtain the snapshot result

Version chain alignment rules:

• If it falls in green (trx_id
• If it falls in red (trx_id> Max), it indicates that the version was generated by a future transaction and is definitely not visible
• If it falls in yellow (min_id<=trx_id<=max_id), there are two cases
  1. If the row’s trx_id is in the array, it means that the version was generated by a transaction that has not yet committed and is not visible, and the current transaction is visible
  2. If the trx_ID of a row is not in the array, it indicates that the version was generated by a committed transaction and is visible

The whole process

The premise condition

As shown in the diagram, there are four transactions, namely translation2, translation3, translation4, translation5; Translation2, translation3, and translation4 modify the database respectively, and TRANSLation5 performs the query operation.

Implementation process

1. When translation2 modiates the data with ID 1 in the Persion table, the record operation is first treated as the latest record and roll_pointer points to the previous version, but translation2 is still uncommitted because translation2 is not committed

2. When translation3 modiates the data with ID 1 in the Persion table, the record operation is first treated as the latest record and roll_pointer points to the previous version, but translation3 is still uncommitted, especially since translation3 was not committed

3. When translation4 modiates the data with ID 1 in the Persion table, the record operation is first described as the latest record and roll_pointer points to the previous version, but translation4 is committed because translation4 is committed

4. A read-view consistency view is created when translation5 performs a SELECT statement query and since it is the first time the query is created. Translation2 and translation3 are both uncommitted transactions and translation4 is committed transaction when translation5 is queried. The array of uncommitted transaction ids in read-view consists of translation2 and translation3. The maximum transaction ID is translation4, so max_id is translation5 and min_id is translation2

5. Next, we need to do a version chain comparison (if you don’t remember the version chain comparison rules, go back and review them), because the read-view consists of an array of uncommitted transactions [1,2] and the maximum transaction ID3. As the transaction ID of the latest record is 4,4 is greater than the maximum transaction ID3, so it cannot be viewed. In the undolog log, transaction 3 is in the array of uncommitted transactions, that is, the middle segment. Since transaction 3 is not in the array of uncommitted transactions, transaction 3 is visible, and the final result is name=”memolei”.

To this point, the whole process of MVCC is finished, once born, twice cooked, review the old and know the new.

eggs

What is the difference between InnoDB snapshot reads at RR and RC isolation levels

• In RR isolation, the read-view is created only once when a transaction makes a snapshot read for the first time. Therefore, the array of uncommitted transactions and the maximum transaction ID in the read-view remain unchanged. Therefore, only the data before the transaction is read is read each time
• At the RC isolation level, a read-view is generated every time a transaction makes a snapshot read. As a result, a transaction at the RC isolation level can see the data modified by other transactions, which is also the cause of non-repeatability

At the RC isolation level, each snapshot Read generates and retrieves the latest Read View. In RR isolation, a Read View is created only for the first snapshot Read in the same transaction, and all subsequent snapshot reads obtain the same Read View.

Small talk

• After reading the article, consider whether your CP rate and MVCC’s has increased again
• I’m Maimo, welcome to talk to me

Welcome to join my public number pk factory together

• Welcome to maimocoding