MySQL multi-version concurrency control

MVCC

By adding two hidden columns to each table, one saves the creation time of the row, a time to save the row's expiration (or delete time), and the time corresponding to the version

Creates a new transaction that creates a new version number

Pre-conditions

A. InnoDB only data rows with versions older than the current transaction version are found

B. The deleted version of the row is either undefined or larger than the current transaction version

Processing mechanism

INSERT

InnoDB Save the current system version number as the row version number for each newly inserted row

DELETE

InnoDB Save the current system version for each row deleted good as delete identity

UPDATE

InnoDB to insert a new record, save the current system version number and save the current system version to the deleted identity of the original row

Take the update as an example:

A, b two transactions

A Transaction

1. Create a transaction version number V1

2. Query when the current line version number is V1

3. Update first delete operation version number V3 then update line Delete version number is V3

b Business

1. Create the B transaction version number 2

2. The version number of the current line at query time is 2

3. Update first delete operation version number V4 then update line Delete version number is V4

Two transactions simultaneous update creates 4 temporary records

Then select one of them as the result.

MySQL multi-version concurrency control