Speaking of MVCC, what are transactions and transaction isolation levels first
MySQL transaction
In relational databases, a logical unit of work must satisfy four properties, ACID, namely Atomicity, Consistency, Isolation, and Durability, in order to become a transaction.
atomic
A transaction is an atomic unit of operation whose changes to data are either committed or rolled back.
consistency
This means that the integrity limits of the database are not broken before and after the transaction begins. Consistency includes two aspects, namely constraint consistency and data consistency.
- Constraint consistency: create table structure with foreign key, Check, unique index constraint, etc. MySQL does not support Check
- Data consistency: is a comprehensive rule, as it is the result of a combination of atomicity, persistence, and isolation, rather than relying on a single technology.
Isolation,
This means that transactions cannot interfere with each other, that is, operations within a transaction and the data used are isolated from other concurrent transactions.
persistence
This means that once a transaction is committed, its changes to the data in the database should be permanent, and should not be affected by subsequent operations or failures.
Concurrent transactions
When transactions are processed concurrently, there may be problems such as update loss, dirty reads, non-repeatable reads, magic reads, etc.
- Update loss: Update loss occurs when two or more transactions update the same row. It can be divided into rollback override and commit override.
- Rollback overwrite: A transaction that rolls back data committed by other transactions
- Commit overwrite: a transaction commit that overwrites data committed by other transactions
- Dirty read: One transaction reads data modified but not committed by another transaction.
- Non-repeatable reads: Multiple reads of the same row in a transaction are inconsistent, and subsequent reads are inconsistent with previous reads
- Phantom read: Query the same condition multiple times in a transaction, and the results are inconsistent. The results of the subsequent query are different from the results of the previous query, with more or less rows of records
Non-repeatable reads generally refer to the number of entries in a transaction, while phantom reads generally refer to the number of queries in a transaction.
Transaction Isolation level
In order to solve these problems, MySQL database uses transaction isolation level to solve these problems. The database system provides the following four transaction isolation levels for users to choose.
- Read uncommitted: Addresses the loss of updates for rollback override types, but can cause dirty reads, where data may be read from uncommitted transaction changes in another session.
- Read committed: Only committed data can be read from other sessions, which solves dirty reads. However, non-repeatable reads can occur, which means that two queries may have inconsistent results in a transaction.
- Repeatable reads: Solves non-repeatable reads by ensuring that multiple instances of the same transaction will see the same rows when reading data concurrently. However, in theory, there will be a phantom read. In simple terms, a phantom read refers to when a user reads a range of rows, another transaction inserts a new row in the range, and when the user reads the range of data, a new phantom walk will be found.
- Serialization: Sequential execution of all add-deletions, deletions, and searches, which resolves the problem of magic reads by forcing transactions to order each other, which can lead to a lot of timeouts and lock contention at this level, which is inefficient.
The higher the transaction isolation level of the database, the less concurrency problems, but the less concurrency processing power (cost). The read uncommitted isolation level is the lowest and concurrency problems are high, but concurrency processing capability is good. For future use, you can choose an appropriate isolation level based on the characteristics of your system, such as being less sensitive to non-repeatable reads and phantom reads and more concerned with concurrent database processing. In this case, you can use the Read Commited isolation level.
Here’s a picture to give you an idea of the isolation levels mentioned above. Assuming that table T has only one column, and one row has a value of 1, here is the action of executing two things in chronological order
If the isolation level is read uncommitted, the value of V1 is 2. At this time, although thing B has not been submitted, the result has been seen by A. So V2 and V3 are both 2.
If the isolation level is read committed, V1 is 1 and V2 is 2. The update of thing B is not visible to A until it is committed, so V3 also has A value of 2
If the isolation level is Repeatable read, V1, V2 is 1, and V3 is 2. All v2s are still 1, complying with the requirement that the data seen during execution must be consistent.
If the isolation level is “serialized”, transaction B will be locked when it performs “change 1 to 2”. Transaction B cannot continue until transaction A commits. So from A’s point of view, V1, V2 is 1, and V3 is 2.
In MySQL, the default transaction isolation level is repeatable read, so why not serialization? As mentioned above, serialization is the equivalent of adding a lock to synchronous serialization, which is very inefficient. Therefore, the default transaction isolation level in MySQL is repeatable read
select @@TRANSACTION_ISOLATIONUse this command to view the isolation level of the transaction and the results
Note: Transaction isolation level, for InnoDB engine, supports transactions, MyISAM engine does not have transactions.
Redo and Undo logs
Before we talk about MVCC, let’s talk about Redo and Undo logs
Undo Log
So Undo Log is just for rolling back transactions.
Before a database transaction starts, the modified records are stored in the Undo log. When a transaction is rolled back or the database crashes, the Undo log can be used to Undo uncommitted transactions and have an impact on the database. The Undo log is not deleted immediately when a transaction is committed. Innodb will put the undo log of the transaction into the delete list, which will be recycled by the background purge thread. An Undo log is a logical log that records a change process. For example, when a delete is performed, an insert is recorded in an Undo log. When an update is performed, an inverse UPDATE is recorded in an Undo log.
Undo log storage: The Undo log is managed and recorded in segments. The innoDB data file contains a rollback segment containing 1024 undo log segments. The undo log storage can be controlled with the following set of parameters.
show variables like '%innodb_undo%';
Redo Log
Redo means Redo. To reproduce the operation in case of an accident to the database for the purpose of recovering the operation.
The Redo log is the location where any data changes are made during a transaction and the latest data is backed up.
Redo log The Redo log is generated as transaction operations are executed. When a transaction commits, the Redo log is written to the log buffer, not to the disk file immediately after the transaction commits. Once the dirty pages of the transaction have been written to disk, the Redo log is complete, and the Redo log space can be reused.
MVCC multi-version concurrency control
What is MVCC?
MVCC- Multi-Version Concurrency Control is primarily a concrete implementation of the isolation level used by the InnoDB storage engine in MySQL to implement both read committed and repeatable isolation levels. In code, read committed and read repeatable are two interfaces, and MVCC is the implementation.
By default, MVCC does not lock the read and write operations on a row of data to ensure mutual exclusion. In order to ensure high isolation at the serialization isolation level, all operations are locked.
Undo log version chain and read View mechanism details
Undo log version chain refers to a row of data that has been modified by multiple transactions. After the transaction is modified, MySQL keeps the undo rollback log of the previous data, and concatenates these undo logs with two hidden fields, trx_ID and ROLL_pointer, to form a history version chain
At the repeatable read isolation level, when the transaction is on, the execution of any query SQL generates a read-view of the consistency of the current transaction, which does not change until the end of the transaction (if the isolation level is read committed, it is regenerated each time the query SQL is executed). This view consists of an array of all uncommitted transaction ids (min_id) and the maximum committed transaction ID (max_id). Any SQL query results in the transaction need to be compared to the snapshot from the latest data in the corresponding version chain
Version chain comparison rules:
- If the row’s trx_id falls in the green section (trx_id
- If a row’s trx_id falls in the red section (trx_id>min_id), this version is generated by a future transaction and is not visible (if a row’s trx_id means its current transaction is visible).
- If the row’s trx_id falls in the yellow section (min_id<=trx_id<=max_id), then there are two cases
- If the row’s trx_id is in the view array, that version was generated by an uncommitted transaction and is not visible (if the row’s trx_id means that its own transaction is currently visible)
- If the trx_id of a row is not in the view array, this version is generated by a committed transaction.
To illustrate, here we have five transactions
Delete is considered a special case of update. The latest data in the update chain is copied, and the trx_id is changed to the delete operation trx_ID. In the record header, the (deleted_flag) bit is set to true. If the value of delete_flag is true, it indicates that the record has been deleted and no data is returned.
Note: the begin/start transaction command is not the starting point of a transaction. After the first statement that modifs InnoDB table, the transaction is actually started and the transaction ID is requested from MySQL. MySQL internally assigns transaction IDS strictly according to the order in which transactions are started.
Summary: The realization of MVCC mechanism is through read-view mechanism and undo version chain comparison mechanism, so that different transactions can read different versions of the same data in the version chain according to the data version chain comparison rules.