This is the third day of my participation in Gwen Challenge

preface

Most of MySQL’s transactional storage engines do not implement simple row-level locking. In order to improve concurrency performance, they generally implement multi-version concurrency control (MVCC) simultaneously. Not only MySQL, but other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different, because there is no unified implementation standard for MVCC. MVCC can be considered a variant of row-level locking, but it avoids locking in many cases and is therefore less expensive. Implementation mechanisms vary, but most implement non-blocking reads, and writes lock only the necessary rows. MVCC is implemented by saving a snapshot of the data at a point in time. That is, each transaction sees the same data no matter how long it takes to execute. Depending on when the transaction starts, each transaction may see different data for the same table at the same time. If you don’t have the concept before, this sentence can sound a little confusing. Once you are familiar with it, you will find that this sentence is actually very easy to understand.

Undo log in InnoDB

To understand MVCC, you need to understand Undo log.

A, Begin Start transaction B, record original value A=1 in undo log C, modify record A=3 D, record original value B=1 in undo log E, modify record B=2 F, write undo log to disk G, write data to disk H, CommitCopy the code

If the undo log is broken before being written to the disk, the undo log is rolled back. If the undo log is written to the disk, the undo log is rolled back. InnoDB uses undo log to save snapshots of old version information. Undo logs in rollback blocks are divided into INSERT and update Undo logs. Insert undo logs are only required for transaction rollback and are deleted immediately after the transaction commits. Update undo logs are also used in transactions, but the time at which they are deleted is uncertain. They are deleted only when there is no transaction that requires any snapshot of the row. So commit transactions frequently, or the undo log corresponding to the rollback block may become very large.

In the InnoDB MVCC

InnoDB stores three values per row in the table to implement MVCC

The column name The length of the role
DB_TRX_ID 6 The transaction Id of the last transaction that inserted or updated the row (deletes are special updates and the row is marked as deleted)
DB_ROLL_PTR 7 The rollback pointer is used to point to the undo log record written to the rollback block (if the row is updated, the undo log contains the information needed to roll back to the update).
DB_ROW_ID 6 Row flag, monotonic increment Id, cluster index is automatically created when will contain this Id, otherwise hidden, no one can see

The system version number is automatically incremented each time a new transaction is started. The system version number at the start of the transaction is used as the version number of the transaction and is compared with the version number of each row of records queried.

MVCC run level

MVCC only works at REPEATABLE READ and READ COMMITTED isolation levels. The other two isolation levels are incompatible with MVCC because READ UNCOMMITTED always reads the latest row, not the row that matches the current version of the transaction. SERIALIZABLE locks all rows that are read.

MVCC operations

Let’s look at how the MVCC operates in the REPEATABLEREAD isolation level.

SELECT

InnoDB will check each line according to the following two conditions: InnoDB only find versions earlier than the current transaction data lines (i.e., the system version number less than or equal to the transaction system version number), so that we can make sure that the transaction of rows read, or is already exist before the start of the transaction, or is inserted or modified transaction itself. The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that rows read by the transaction are not deleted before the transaction begins. Only records that meet the preceding two conditions can be returned as the query result.

INSERT

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

DELETE

InnoDB stores the current system version number as a row deletion identifier for each row that is deleted.

UPDATE

InnoDB inserts a new record, saves the current system version number as the row version number, and saves the current system version number to the original row as the row deletion mark. Save these two additional system versions so that most read operations are unlocked. This design makes reading data simple, performs well, and guarantees that only rows that meet the criteria will be read. The downside is that each row requires additional storage, more row checking, and some additional maintenance.

Delete under MVCC

In the case of multiple versions of InnoDB, when a row is deleted using SQL commands, the row is not physically deleted immediately (similar to the normal logical deletion when we write programs). InnoDB will physically delete rows when it deletes update undo logs that were added to delete data. This physical delete is very fast, similar to the time it takes SQL to perform a delete.

conclusion

This article explains the database multi-version concurrency control some knowledge, I believe to help you increase some of the transaction of the superficial understanding. Next time I’ll bring you some other MySQL content. Stay tuned and see you in the next post!