Illustration: pixabay

What is MVCC?

Concurrency Control for Multi Version Concurrency Control. Represents multi-version concurrency control.

What does MVCC do?

Avoid concurrent blocking of read data due to write lock blocking. A logical (logical, not physical) read/write separation is done at the engine layer.

It saves multiple historical versions of the data and determines whether the data is visible to the transaction based on the version number. Inside InnoDB, a global array of active read and write transaction ids is recorded, which is used to determine the visibility of transactions based on the transaction ID size.

Therefore, it can achieve transaction isolation without locking data.

The sequence

  1. The transaction log
  2. Version + Model
  3. Consistency view

What kinds of transaction logs?

Binlog. Logs generated by the Mysql service layer are used for data replication, synchronization and fault recovery.

Redo log. Mysql data log: Write logs first and then disks. This log is called the redo log. This is WAL (write-Ahead Logging) for Mysql (Redis writes in the same way). In particular, InnoDB writes data to the redo log and then updates the change buffer. The data will be in due time (what is in due time? To brush the disk.

The undo log. As opposed to redo log, playback log. It is generated like this:

  • insert => delete

    insert into t(a) values(1);   => id = 1
---
delete t where id = 1;
Copy the code

  • delete => insert

    delete t where id = 1;       => id => 1, a => 1
---
insert into t(id, a) values(1.1);
Copy the code

  • update => update

    update t set a = 2 where id = 1;    => id => 1, a => 1
---
update t set a = 1 where id = 1;
Copy the code

Version of the model

Transaction version number

Under InnoDB, each transaction has a unique transaction ID (Transaction ID), which is requested from the transaction system (global ID generator) at the beginning of the transaction.

Data version

Each insert and update is treated as a version. The architecture of this release is as follows:

  • Trx_id. Transaction ID
  • Roll the PTR. A pointer to the previous data version used to roll back the version

According to these two fields, a single linked list can be formed to make the data traceable. When querying, you can be selective about which version of the data to display. So how does this selective presentation work?

Consistency view

InnoDB maintains an array of transaction ids for each transaction that is currently active at the moment the transaction is started. There are two water levels in this array:

  • Low water: minimum transaction ID
  • High watermark: maximum transaction ID + 1

These two water levels form a read-view of the current transaction.

When the current transaction (S1) is started, an array of active transaction ids is generated. The trx_ID of a data version. There will be the following situations:

【【 1,2,5 】, 【 4,6 】, 【 7,8 】

  • (trx_id == 2&& trx_id < low woter) falls in the green zone ([1,2,5]). Indicates that the transaction is committed or is its own. So this version of the data is visible to S1
  • It falls in the yellow area ([4,6]).
    • Trx_id == 4 && trx_id >= Low Woter && trx_id < hign Woter) If trx_id is in active array. Indicates that the transaction for this data version has not been committed. Not visible to S1
    • Trx_id == 5&& trx_id not in [Low woter, hign Woter]) If trx_id is not in active array. Visible for S1. Why is this the case?
      • The yellow, green and blue areas are separated by water levels
      • Because everything below the low water level and below the high water level is in the yellow area
      • The current transaction is 6, transaction 4 is not committed, and transaction 5 is committed
  • (trx_id == 8&& trx_id >= hign woter) falls in the red zone (7,8). When S1 started, my version hadn’t been built yet. Not visible to S1

Let’s do an example

create table t(name varchar, age int, sex int, address varchar) engine=InnoDB;

insert into t(name, age, sex, address) values('tom'.23.2.'beijing');
Copy the code
trx_id = 1 trx_id = 2 trx_id = 3 trx_id = 4 trx_id = 5
begin; update t set age = 24, address = ‘nanjing’; commit;
begin;
begin; Active = > [2, 3]
begin; Active = > [4] 2
begin; Active = > 5-tetrafluorobenzoic [2]
update t set age = 5; commit;
update t set age = 6; commit
update t set age = 7; commit
select age from t;

Let’s draw the version structure of the above transaction

Trx_id = 1 (trx_id = 1)

  1. Find trx_id = 3 for age = 3. Judge >= High water level of the current transaction, not visible. Continue to find the version with age = 4 based on PTR.
  2. Find trx_id = 4 for age = 4. Judge >= High water level of the current transaction, not visible. Continue to look for age = 5 versions based on PTR.
  3. Find trx_id = 5 for age = 5. Judge >= High water level of the current transaction, not visible. Continue to look for PTR versions with age = 24.
  4. Find trx_id = 1 for age = 24. Determine the low water level of the current transaction, visible.

A transaction with trx_id = 1 will see age = 24 whenever it is queried. So that’s the consistency view.

reference

MySQL · Engine features · InnoDB Transaction system