"MySQL" Some understanding of MVCC in InnoDB

I. Introduction of MVCC

MVCC (multiversion Concurrency control), a multi-version concurrency control technology that allows most of the transaction engines that support row locks, no longer simply use row locks for concurrency control of the database, instead of combining the database row lock with multiple versions of the row, Non-locking reads can be achieved with minimal overhead, which greatly improves the concurrency of the database system

read Lock: also known as a shared lock, S lock, if the transaction T to the data object a plus s lock, then the transaction T can read a but cannot modify a, the other transaction can only a plus s lock, and cannot add x lock, until T release S lock on A. This ensures that other transactions can read a, but cannot make any modifications to a before T releases the S lock on a.

Write Lock: also known as exclusive lock, X lock. If the transaction t has an X lock on the data object A, the transaction T can read a or modify a, and the other transaction cannot add any locks to a, until T releases the lock on A. This ensures that the other transaction cannot read and modify a until the lock on the A is released by T.

table Lock: The Action object is a data table. Most of MySQL's lock policies are supported (common MySQL InnoDB), which is the lowest system cost but the least concurrency lock policy. The transaction T reads the entire table lock, then other transactions can be read and not writable, if the write lock, then other transactions add or delete can not be changed.

row-level lock: An Action object is a row in a data table. MVCC technology is used more, but not in the MyISAM, row-level lock with the MySQL storage engine implementation instead of MySQL server. However, the row-level lock has higher overhead and better processing concurrency.

Second, the MVCC realization principle

InnoDB MVCC is primarily for the Repeatable-read transaction isolation level. At this isolation level, the data shown by the A and B clients are isolated from each other and are not visible to each other

It is important to understand the structure of InnoDB and Read-view to understand the realization of InnoDB MVCC.

The most basic ROW of InnoDB storage contains some additional storage information Data_trx_id,data_roll_ptr,db_row_id,delete BIT

• The 6-byte data_trx_id marks the latest update for the transaction ID of this row record, each processing a transaction whose value is automatically +1

• The 7-byte data_roll_ptr points to the undo log record for the rollback segment of the current record entry, and the data for the previous version is passed through this pointer

• 6-byte db_row_id, when the clustered index is automatically generated by InnoDB, the clustered index includes the value of this db_row_id, otherwise the value is not included in the clustered index. This is used in the index

• The delete bit bit is used to identify whether the record was deleted, and here is not the actual delete data, but the deletion of the flag. The true meaning of the deletion is at the time of the commit

The specific implementation process

Begin-> Lock the row with an exclusive lock record redo log-> record undo log-> Modify the value of the current row, write the transaction number, and the rollback pointer points to the pre-modified line in undo log

The above procedure is to describe the transaction process of update, in fact, undo log sub-insert and update undo log, because the original data does not exist when the insert, so the rollback when the insert undo log is discarded, and the update undo Log must follow the above procedure

The following are descriptions of SELECT, delete, INSERT, and UPDATE statements, respectively.

SELECT

InnoDB check each row of data to ensure they meet two criteria:

1. InnoDB only the data rows that have a version earlier than the current transaction version (that is, the version of the data row must be less than or equal to the transaction), which ensures that the current transaction reads the rows that existed before the transaction, or the rows created or modified by the current transaction

2. The version of the delete operation of the row must be undefined or greater than the version number of the current transaction, and the row is not deleted until the current transaction starts

The results of the query are returned in accordance with the above two points.

INSERT

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

DELETE

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

UPDATE

InnoDB copied a row. The version number of this new line uses the system version number. It also takes the system version number as the version of the deleted row.

Description

Insert operation "Creation time" =db_row_id, when "delete time" is undefined;

Update, copy the new row "Create Time" =db_row_id, delete time undefined, old data row "create Time" unchanged, delete time = db_row_id of the transaction;

Delete operation, the "creation time" of the corresponding data row is unchanged, delete time = db_row_id of the transaction;

Select operation is not modified for both, read-only corresponding data

Third, for the MVCC summary of the above update before the establishment of 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 greater discrepancy, generally we think 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 Modify, no interference between the various 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, when saving depends on the version number determines whether the success, sounds to contain the flavor of optimistic lock, and InnoDB implementation Way is:

• 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? InnoDB implementation is really not MVCC, because there is no multi-version of the core coexistence, undo log content is only the result of serialization, the process of recording multiple transactions, not part of the multi-version coexistence. But the ideal MVCC is difficult to implement, and when a transaction modifies only one row of records using the ideal MVCC pattern is no problem and 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 Row1 are rolled back at this time, Damage to the Transaction2 results, causing Transaction2 to violate acid. The fundamental reason why the ideal MVCC is difficult to achieve is the attempt to replace the two-paragraph submission with optimistic locking. Two rows of data are modified, but to ensure consistency, there is no difference between modifying data in two distributed systems, and two commits are the only means of ensuring consistency in this scenario at this time. Two paragraph of the nature of the submission is locked, the essence of the optimistic lock is to eliminate the lock, the contradiction between the two, so the ideal MVCC difficult to really be applied in practice, InnoDB just borrowed MVCC the name, provides read non-blocking only.

Reference articles

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%2Fwiki%2FMySQL_Internals

"MySQL" Some understanding of MVCC in InnoDB