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

Today, the editor will share with you the relevant knowledge about what the MVCC principle of MySQL InnoDB is. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

The full name of MVCC is Multi-Version Concurrency Control, that is, multi-version concurrency control, mainly to improve the concurrency performance of the database. When a read and write request occurs for the same row of data, the lock will be blocked. But MVCC handles read-write requests in a better way, so that there is no lock in the event of a read-write request conflict. This read refers to the snapshot read, not the current read, which is a locking operation and a pessimistic lock. So how on earth does it read-write without locking? what does snapshot read and current read mean? We'll all learn later.

MySQL can avoid the problem of phantom reading to a great extent under the REPEATABLE READ isolation level. how does MySQL do it?

Edition chain

We know that for tables that use the InnoDB storage engine, its clustered index records contain two necessary hidden columns (row_id is not necessary, and the tables we create do not contain row_id columns when they have a primary key or a non-NULL unique key):

Trx_id: each time a transaction makes changes to a clustered index record, the transaction id of that transaction is assigned to the trx_id hidden column.

Roll_pointer: every time a change is made to a clustered index record, the old version is written to the undo log, and the hidden column acts as a pointer to find the pre-modified information of the record.

To illustrate this, let's create a demo table:

CREATE TABLE `teacher` (`number` int (11) NOT NULL, `name` varchar (100th) DEFAULT NULL, `domain` varchar (100th) DEFAULT NULL, PRIMARY KEY (`number`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then insert a piece of data into the table:

Mysql > insert into teacher values (1, 'Jacks,' Java'); Query OK, 1 row affected (0.01sec)

Now the data looks like this:

Mysql > select * from teacher;+-+ | number | name | domain | +-+ | 1 | J | Java | +-+ 1 row in set (0.00 sec)

Assuming that the transaction id that inserted the record is 60, the schematic diagram of the record is shown below:

Suppose the next two transactions with id 80,120 perform UPDATE operations on this record. The operation process is as follows:

Trx80Trx120begin

Beginupdate teacher set name='S' where number=1

Update teacher set name='T' where number=1

Commit

Update teacher set name='K' where number=1

Update teacher set name='F' where number=1

Commit

Every time a change is made to the record, a undo log is recorded, and each undo log also has a roll_pointer attribute (the undo log corresponding to the INSERT operation does not have this attribute because there is no earlier version of the record). These undo logs can be connected and linked into a linked list, so the situation is like the following figure:

After each update to the record, the old value will be put in a undo log. Even for an old version of the record, with the increase in the number of updates, all versions will be linked into a linked list by the roll_pointer attribute. We call this linked list as the version chain, and the header node of the version chain is the latest value of the current record. In addition, each version contains the corresponding transaction id when the version was generated. Therefore, the version chain of this record can be used to control the behavior of concurrent transactions accessing the same record, and this mechanism is called multi-version concurrency control (Mulit-Version Concurrency Control MVCC).

ReadView

For transactions that use the READ UNCOMMITTED isolation level, since records modified by uncommitted transactions can be read, it is fine to read the latest version of the record directly.

For transactions that use the SERIALIZABLE isolation level, InnoDB uses locking to access records.

For transactions that use READ COMMITTED and REPEATABLE READ isolation levels, you must be sure to read the records modified by the committed transactions, that is, if another transaction has modified the records but not yet committed, you cannot directly read the latest version of the records. The core problem is: the difference between READ COMMITTED and REPEATABLE READ isolation levels in non-repeatable and phantom readings. The key to these two isolation levels is to determine which version in the version chain is visible to the current transaction.

To this end, InnoDB put forward a concept of ReadView, which mainly contains four more important contents:

M_ids: represents the id list of active read and write transactions in the current system when the ReadView is generated.

Min_trx_id: represents the smallest transaction id of the active read and write transactions in the current system when the ReadView is generated, that is, the minimum value in m_ids.

Max_trx_id: represents the id value that should be assigned to the next transaction in the system when the ReadView is generated. Note that max_trx_id is not the maximum value in m_ids, and the transaction id is allocated incrementally. For example, there are now three transactions in which id is 1 and id 2 is 3, and then transactions with 3 are committed. So when a new read transaction generates ReadView, the m_ids includes 1 and 2. The value of mingling trxroomid is 1 and the value of maxim trxroomid is 4.

Creator_trx_id: represents the transaction id of the transaction that generated the ReadView.

With this ReadView, when accessing a record, you only need to follow the following steps to determine whether a version of the record is visible:

If the trx_id attribute value of the accessed version is the same as the creator_trx_ id value in ReadView, it means that the current transaction is accessing its own modified record, so that version can be accessed by the current transaction.

If the trx_id attribute value of the accessed version is less than the min_trx_ id value in ReadView, the transaction that generated the version was committed before the current transaction generated ReadView, so the version can be accessed by the current transaction.

If the trx_id attribute value of the accessed version is greater than or equal to the max_trx_ id value in ReadView, the transaction that generated the version does not open until after the current transaction generates ReadView, so the version cannot be accessed by the current transaction.

If the value of the trx_id attribute of the accessed version is between min_trx_id and max_trx_id of ReadView (min_trx_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.