MySQL database transaction ISOLATION LEVEL lock condition--read committed && MVCC

This article turns from https://m.imooc.com/article/details?article_id=17290 thanks to the author

The previous article recorded my understanding of the MySQL transaction isolation level READ UNCOMMITTED.
This article records my understanding of the MySQL transaction ISOLATION level Read Committed & MVCC.

Objective

Can be very responsible to tell you that this isolation level in MySQL is not only through the lock implementation, in fact there is repeatable read isolation level, in fact, the implementation of the two isolation level effect also requires an auxiliary, which is mvcc-multi-version concurrency control, But in fact it is not a strict sense of multi-version concurrency control, is not very ignorant, it doesn't matter, we analyze each.

Directory

1. How to implement Read committed by simply locking?
2. What does the actual presentation look like?
3.MVCC implementation principle?
4. Reflections on the realization Principle of InnoDB MVCC

1. How to implement Read committed by simply locking?

Start with the isolation level effect: Transactions can read only records that have been committed by other transactions.
The implementation of the database transaction isolation level, InnoDB supports row-level locks, and writes with row-level exclusive locks (X-lock), and when other transactions are accessing the same record that another transaction is in the update (except that the select operation is essentially a write operation), the read operation of the transaction is blocked. It is only possible to wait until the exclusive lock on the record (which is actually the lock on the index) is released before it can be accessed, that is, when the transaction is committed. This does enable the read Commited isolation level effect.
The database does this to make it possible for a transaction to read only the results of records that have been committed by other transactions, but this is a very inefficient approach. Because for most applications, read operations are more than write operations, when the write operation is locked, then the read operation is blocked, which will lead to the corresponding ability of the application of the database to be pinned.

2. What does the actual presentation look like?

Look at the following actions:
1. Open two client instances, set the transaction isolation level to read Committed, and open the transaction separately.

set transaction isolation level read committed; set autocommit = 0; begin；

2. Client 1 Does the update operation:

update test set name = ‘测试‘ where id =32;

The results are as follows:

3. Client 2 To do the query operation:

select name from test where id = 32;

The results are as follows:

It is estimated that you are in doubt, is the client 1 upate record, client 2 can also be read without blocking, and read the data before the change.
That is InnoDB the auxiliary play well, because the internal use of the MVCC mechanism, to achieve a consistent non-blocking read, greatly improve the concurrent read and write efficiency, write does not affect the reading, and read the record of the image version.

The MVCC principle is described below.

3.MVCC Implementation principle

Online on the MVCC realization of the principle of a variety of, mixed.
Including "High-performance MySQL" On the MVCC of the explanation just stay in the appearance, and did not combine the source to analyze. Of course, the vast majority of people still believe this book, has never carried out deep-cut, thinking.
The following is a description of the MVCC implementation principle of high-performance MySQL:

"InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。"

This is the book that blinds the Truth and Hairenbujian.

Let's still look at the source code:

1. Hidden columns of the record
Actually, there are three columns.

  mysql mvccinnodb storage engine supported, innodb three hidden fields are implemented for each row of records: Span class= "PLN" > 6id (db_trx_id)  7 bytes of the rollback pointer ( Span class= "PLN" >db_roll_ptr)   hidden 6id7innodb  

Dependencies implemented by 2.MVCC
The implementation of MVCC in MySQL relies on the undo log and read view.

1.undo log: undo log中记录的是数据表记录行的多个版本，也就是事务执行过程中的回滚段,其实就是MVCC 中的一行原始数据的多个版本镜像数据。 2.read view: 主要用来判断当前版本数据的可见性。

3.undo Log

Undo log is used for rollback, the content of the database before the copy transaction (row) to undo buffer, at the appropriate time, the content in the undo buffer to disk. Undo buffer, like redo buffer, is also a ring buffer, but when the buffer is full, the contents of undo buffer will also be flushed to disk, and unlike the redo log, there is no separate undo log file on the disk, all undo Log is stored in the main IBD data file (tablespace), even if the client sets one data file per table.

Let's look at how undo log is formed through the update process.

3.1-ROW update process
The following shows the update process for a row of records under a transaction:

1. Initial data row
   
   F1~f6 is the name of a row, 1~6 is its corresponding data. The following three hidden fields correspond to the transaction number and the rollback pointer of the row, if the data is just insert, you can think of ID 1, and the other two fields are empty.
   2. Transaction 1 Change the value of each field in the row
   
   When transaction 1 changes the value of the row, the following actions are performed:
   Lock the row with an exclusive lock
   Log Redo Log
   Copy the value of the row before the change to undo log, which is the line below
   Modify the value of the current row, fill in the transaction number, and point the rollback pointer to the pre-modified line in undo log
   3. Transaction 2 Modify the value of the row
   
   As with transaction 1, at this point, undo log has two rows of records and is linked together by a rollback pointer.

4.read view determines whether the current version data item is visible

In InnoDB, when a new transaction is created, InnoDB creates a copy of the active transaction list (trx_sys->trx_list) in the current system (read view), and the copy holds a list of other transaction IDs that the system should not currently see in this transaction. When the user reads the row record in this transaction, InnoDB compares the current version number of the row with the Read view.
The specific algorithm is as follows:

1. The current transaction ID for this row is trx_id_1 for the oldest transaction ID in Trx_id_0,read view and the latest transaction ID is trx_id_2.
2. If trx_id_0< trx_id_1, then the transaction that indicates the row record is committed before the new transaction is created, so the current value of the row record is visible. Skip to step 6.
3. If trx_id_0>trx_id_2, then the transaction that indicates the row record is open after the new transaction is created, so the current value of the row record is not visible. Skip to step 5.
4. If trx_id_1<=trx_id_0<=trx_id_2, then indicates that the transaction of the row is active at the time the new transaction was created, traversing from trx_id_1 to Trx_id_2, if trx_id_ 0 is equal to one of their transaction IDs, then not visible. Skip to step 5.
5. Remove the latest Undo-log version number from the rollback segment pointed to by the db_roll_ptr pointer that the row records, assign it to the Trx_id_0, and then skip to step 2.
6. Returns the value of the visible row.

Note that the new transaction (the current transaction) and the transaction that is in memory commit are not in the active transaction list.

The corresponding code is as follows:

Function: read_view_sees_trx_id.
The scope of the current global transaction is saved in Read_view:
"Low_limit_id, up_limit_id"
1. When the transaction ID of a row record is less than the minimum active ID of the current system, it is visible.
if (trx_id < view->up_limit_id) {
return (TRUE);
}
2. When the transaction ID of a row record is greater than the maximum activity ID of the current system, it is not visible.
if (trx_id >= view->low_limit_id) {
return (FALSE);
}
3. When the transaction ID of a row record is within the active range, determine if it is visible in the active linked list if it is not visible if it is not.
for (i = 0; i < n_ids; i++) {
trx_id_t view_trx_id
= read_view_get_nth_trx_id (view, n_ids-i-1);
if (trx_id <= view_trx_id) {
Return (trx_id! = view_trx_id);
}
}

5 Impact of the transaction isolation level

However: for two different transaction isolation levels
Tx_isolation= ' read-committed ': statement-level consistency: As long as the data submitted before the current statement is executed is visible.
tx_isolation= ' Repeatable-read '; Statement-level consistency: As long as the data submitted before the current transaction is executed is visible.
For the isolation level of the two transactions, how can the same visibility be used to determine how the logic achieves different visibility?

6. The generation principle of Read view under different isolation levels

Here's a look at the Read_view generation mechanism:
1. READ-COMMITED:
Function: ha_innobase::external_lock
if (Trx->isolation_level <= trx_iso_read_ COMMITTED
&& Trx->global_read_view) {
/  At low transaction isolation levels we LET
All consistent read set its own snapshot  /
Read_view_close_for_mysql (TRX);
This allows you to create a Read_view transaction interval based on the current global transaction list, implementing the Read Committed isolation level.
2. Repeatable read:
at the isolation level of repeatable read, when creating the transaction TRX structure, The current global read view is generated.
is created using the Trx_assign_read_view function and is maintained until the end of the transaction, thus implementing the REPEATABLE read isolation level.

It is because of the read view generation principle in 6 that, under different isolation levels (), Read committed always reads the latest snapshot data and repeatable the row data version at the beginning of the read transaction.

A deep reflection on the realization principle of 4.InnoDB MVCC

Prior to the above update to establish the undo log, according to the various policies read non-blocking is the Mvcc,undo log line is the MVCC in the multi-version, this may be with our understanding of the MVCC have a large discrepancy.

Generally we think that MVCC has the following features:

Each row of data has a version that is updated every time the data is updated
Copy out the current version at random modification, no interference between transactions
Compare version number on save, overwrite original record if successful (commit), failure to discard copy (rollback)
That is, each line has a version number, save it depends on the version number of success, it sounds like an optimistic lock flavor ... , while

The InnoDB is implemented in the following ways:

Transaction modifies raw data as an exclusive lock
Store the pre-modified data in undo log and associate it with the master data by rolling back the pointer
Modify success (commit) do nothing, failure restores data in undo log (rollback)

The most essential difference between the two is, if you want to modify the data is exclusive locking, if the lock is not considered MVCC?

The implementation of InnoDB is really not MVCC, because there is no multi-version coexistence of the core, undo
The content in log is only a serialized result, and the process of recording multiple transactions is not part of multi-version coexistence. But the ideal MVCC is difficult to implement when a transaction modifies only one row of records using the ideal MVCC mode
There is no problem, it can be rolled back by comparing the version number, but when the transaction affects multiple rows of data, the ideal MVCC is powerless.

For example, if Transaciton1 executes the desired MVCC, the modification Row1 succeeds, and the modification Row2 fails, the Row1 is rolled back, but because Row1 is not
Locked, the data may be modified by Transaction2, and if the contents of the Row1 are rolled back at this time, the Transaction2 modification results will be destroyed, causing
Transaction2 violates acid.

The fundamental reason why the ideal MVCC is difficult to achieve is the attempt to replace the two-paragraph submission with optimistic locking. Modifies two rows of data, but to ensure consistency, there is no difference between modifying data in two distributed systems,
The second submission is the only way to ensure consistency in this scenario at this time. The essence of the two-paragraph submission is locking, the essence of the optimistic lock is to eliminate the lock, the contradiction between them, so the ideal MVCC is difficult to actually be
, InnoDB just borrowed the name of MVCC and provided a non-blocking read.

MySQL database transaction ISOLATION LEVEL lock condition--read committed && MVCC (RPM)