[Mysql] Some Understandings about MVCC in innodb,

[Mysql] Some Understandings about MVCC in innodb,
1. Introduction to MVCC

MVCC (Multiversion Concurrency Control), that is, multi-version Concurrency Control technology, which makes most transaction engines that support row locks no longer simply use row locks for database Concurrency Control, instead, the row locks of the database are combined with multiple versions of the row. The non-locked read can be implemented with only a small overhead, which greatly improves the concurrent performance of the database system.

Read lock:It is also called the shared lock and S lock. If transaction T adds the S lock to Data Object A, transaction T can read A but cannot modify A, and other transactions can only add the S lock to, instead, the X lock cannot be applied until T releases the S lock on. This ensures that other transactions can read A, but cannot modify A before T releases the S lock on.

Write lock:Exclusive lock and X lock. If transaction T adds an X lock to the Data Object a, transaction T can read A or modify A, and other transactions cannot apply any lock to auntil T releases the lock on. This ensures that other transactions cannot read or modify A before T releases the lock on.

Table lock:The operation object is a data table. Most Mysql lock policies are supported (Common mysql innodb). It is a lock policy with the lowest system overhead but the lowest concurrency. When transaction t adds a read lock to the entire table, other transactions are readable and cannot be written. If a write lock is applied, no additional, deletion, or modification operations can be performed for other transactions.

Row-Level Lock:The operation object is a row in the data table. MVCC technology is widely used, but it cannot be used in MYISAM. Row-level locks are implemented using the mysql storage engine instead of the mysql server. However, row-level locks have high system overhead and high concurrency.

Ii. MVCC implementation principle

Innodb MVCC is mainly implemented at the Repeatable-Read transaction isolation level. At this isolation level, the data shown in Client A and client B is isolated from each other and is updated and invisible to each other.

Understanding the row structure and Read-View Structure of innodb is of great significance to understanding the implementation of innodb mvcc.

The basic row stored in innodb contains some additional storage Information: DATA_TRX_ID, DATA_ROLL_PTR, DB_ROW_ID, and delete bit.

• The 6-byte DATA_TRX_ID indicates the transaction id of the last updated row record. The value of each transaction is automatically + 1

• The 7-byte DATA_ROLL_PTR points to the undo log record of the rollback segment of the current record item. This pointer is used to find the data of previous versions.

• 6-byte DB_ROW_ID. When innodb automatically generates a clustered index, the clustered index includes the value of this DB_ROW_ID. Otherwise, this value is not included in the clustered index. This is used in the index.

• The delete bit is used to identify whether the record is deleted. Here, it is not a real deletion of data, but a marked deletion. The true deletion is when the commit

Specific execution process

Begin-> lock the row with exclusive locks-> redo log-> record undo log-> modify the value of the current row, write the transaction number, roll back pointer to the row before modification in the undo log

The above process exactly describes the transaction process of UPDATE. In fact, the undo log is divided into insert and update undo log because the original data does not exist during insert, therefore, the insert undo log can be discarded during rollback, while the update undo log must follow the above process.

The following statements are described in the select, delete, insert, and update statements respectively.

SELECT

Innodb checks each row of data to ensure that they comply with two standards:

1. InnoDB only looks for data rows whose versions are earlier than the current transaction version (that is, the version of the data row must be earlier than or equal to the transaction version ), this ensures that all rows read by the current transaction exist before the transaction, or the rows created or modified by the current transaction.

2. The version of the row deletion operation must be undefined or later than the version number of the current transaction. Before the current transaction starts, the row does notDeleted

If the preceding two conditions are met, the query result is returned.

INSERT

InnoDB records the current system version number for each new row as the creation ID.

DELETE

InnoDB uses the current system version number of each deleted row as the row deletion ID.

UPDATE

InnoDB copies a row. The version number of this new line uses the system version number. It also uses the system version number as the version number for deleting rows.

Description

During the insert operation, "Creation Time" = DB_ROW_ID. In this case, "deletion time" is undefined;

During update, copy the "Creation Time" of the new row = DB_ROW_ID, the deletion time is undefined, the "Creation Time" of the old data row remains unchanged, and the deletion time = The DB_ROW_ID of the transaction;

Delete operation, the "Creation Time" of the corresponding data row remains unchanged, and the deletion time = DB_ROW_ID of the transaction;

The select operation does not modify either of the two and reads the corresponding data only.

Iii. Summary of MVCC the undo log is created before the above update. MVCC is used for non-blocking reading based on various policies, and the row in the undo log is multiple versions in MVCC, this may differ greatly from the MVCC we understand. We generally think that MVCC has the following features:

• Each row of data has a version, which is updated each time the data is updated.
• When the modification is made, the current version is copied and modified without interference between transactions.
• Compare the version number when saving. If it succeeds (commit), it overwrites the original record; if it fails, it discards copy (rollback)

That is, each row has a version number, which determines whether the lock is successful based on the version number. It sounds optimistic. the Innodb implementation method is as follows:

• The transaction modifies the original data in the form of exclusive locks.
• Store the data before modification in the undo log, and associate it with the master data through the rollback pointer.
• If the modification succeeds (commit), nothing is done. If the modification fails, the data in the undo log is restored (rollback)

The most essential difference between the two is: Do I have to lock the data when I modify it? Isn't it MVCC if I lock it? The implementation of Innodb is really not MVCC, because it does not implement multi-version coexistence of the core. The content in the undo log is only the result of serialization and records the process of multiple transactions, does not belong to multi-version coexistence. However, the ideal MVCC is hard to implement. When a transaction only modifies one row of records and uses the ideal MVCC mode, the transaction can be rolled back by comparing the version number; however, when a transaction affects multiple rows of data, the ideal MVCC data is powerless. For example, if Transaciton1 executes the desired MVCC and Row1 is modified successfully, but Row2 fails to be modified, Row1 needs to be rolled back. However, because Row1 is not locked, its data may be modified by transaction2, if the Row1 content is rolled back at this time, the modification result of Transaction2 will be damaged, resulting in Transaction2 violating ACID. The root cause of the difficulty in implementing the ideal MVCC is the attempt to replace the second commit with an optimistic lock. Two rows of data are modified, but to ensure consistency, there is no difference with modifying the data in the two distributed systems. Second commit is the only way to ensure consistency in this scenario. The essence of the second commit is locking. The essence of optimistic locks is to eliminate locks, which are in conflict. Therefore, the ideal MVCC cannot be applied in practice. Innodb only uses the MVCC name, read is not blocked.

References

Https://www.percona.com/blog/2014/12/17/innodbs-multi-versioning-handling-can-be-achilles-heel/
Http://www.xdata.me /? P = 289
Http://blogread.cn/it/article/5969
Http://blog.csdn.net/chen77716/article/details/6742128
Http://blog.chinaunix.net/link.php? Url = http://forge.mysql.com % 2 Fwiki % 2FMySQL_Internals