mysql.png
Introduction to the
Concurrency Control for MULTI-version Concurrency Control (MVCC). Most of MySQL’s transactional storage engines (InnoDB,Falcon, etc.) do not implement simple row-level locking. In order to improve concurrency performance, they generally implement MVCC at the same time. Not only MySQL, but other database systems such as Oracle and PostgreSQL also implement MVCC. It is worth noting that MVCC does not have a unified implementation standard, so different databases, different storage engine implementation is not the same.
As the most widely used storage engine in MySQL, this article focuses on InnoDB’s implementation of multi-version concurrency control
Advantages and disadvantages MVCC
MVCC replaces line locking in most cases, realizing non-blocking on reads, no locks on reads, and no read/write conflicts. The downside is that each row requires additional storage and more row maintenance and checking.
Realization principle of MVCC
undo log
In order to understand the implementation principle of MVCC, the working process of undo log is briefly introduced here
The simplification of working with undo log without redo log is as follows:
| The serial number | action |
|---|---|
| 1 | Start the transaction |
| 2 | Record row data snapshot to undo log |
| 3 | Update the data |
| 4 | Write the undo log to disk |
| 5 | Write data to disk |
| 6 | Commit the transaction |
1) To ensure the persistence of the data, the data must be persisted before the transaction commits. 2) Undo log must be persisted before the data is persisted, so that the system can be used to roll back and forth the transaction in the event of a crash
Hidden columns in Innodb
Innodb uses undo log to keep a snapshot of old version information with changed rows. InnoDB’s internal implementation adds three hidden columns for each row of data to implement MVCC.
| The column name | Length (bytes) | role |
|---|---|---|
| DB_TRX_ID | 6 | The transaction identifier of the last transaction to insert or update a row. (Delete as update, mark as deleted) |
| DB_ROLL_PTR | 7 | Write undo logging to the rollback segment (if the row has been updated, the undo logging contains information needed to rebuild the contents of the row before updating it) |
| DB_ROW_ID | 6 | Row identifier (hiding monotonic increment ID) |
structure
| Data column | . | DB_ROW_ID | DB_TRX_ID | DB_ROLL_PTR |
|---|
MVCC working process
MVCC only works at READ COMMITED and REPEATABLE READ isolation levels. READ UNCOMMITTED always reads the latest rows, not rows that match the current version of the transaction. SERIALIZABLE locks all rows that are read
SELECT
InnoDB checks each row based on two criteria:
- InnoDB only looks for rows whose version (DB_TRX_ID) is earlier than the current transaction version (the system version number of the row <= the transaction’s system version number, which ensures that the rows either existed before the transaction started or were inserted or modified by the transaction itself).
- The delete version number of the row (DB_ROLL_PTR) is either undefined (not updated) or greater than the current transaction version number (updated after the current transaction started). This ensures that rows read by the transaction are not deleted before the transaction begins.
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
The resources
High Performance MYSQL
MySQL 5.7 Reference Manual
MVCC implementation
Reprint please indicate the source: www.jianshu.com/p/a3d49f750…