Mysql InnoDB Transaction

Transaction characteristics ACID
Atomicity: atomicity
A transaction must be considered an indivisible minimum unit of work, all operations in the entire transaction either commit successfully or all fail back, and for a transaction it is not possible to perform only a subset of these operations, which is the atomicity of the transaction

Consistency: Consistency
The database is always transitioning from one consistent state to another consistent state.

Isolation: Isolation
In general, changes made by a firm are not visible to other transactions until they are finally submitted.

Durability: Persistence
Once a transaction commits, its modifications are not persisted to the database.

Isolation level:
READ UNCOMMITTED (READ UNCOMMITTED)
at the Read UNCOMMITTED level, Modifications in the transaction, even if they are not committed, are visible to other transactions. Transactions can read uncommitted data, which is also known as dirty reads (Dirty read). This level can cause a lot of problems, and in terms of performance, READ uncommitted is not much better than the other levels, but it lacks many of the benefits of other levels, unless there is really a very necessary reason to use it rarely in practical applications.
Read COMMITTED
The default isolation level for most database systems is read commtted (but MySQL is not). READ committed satisfies the simple definition of the previously mentioned isolation: When a transaction starts, it can only "see" the changes that have been made to the firm that has been submitted. In other words, any changes you make to a transaction from the beginning until it is committed are not visible to other transactions. This level is sometimes called non-repeatable read (nonrepeatble read), because two times the same query is executed, you may get a different result
Repeatable READ (REPEATABLE READ)
Repeatable read resolves the problem of dirty reads. This isolation level guarantees that the same record is read multiple times in the same transaction. In theory, however, it is not possible to repeat the isolation level to solve the problem of another phantom read (Phantom read). The so-called Phantom reading refers to when a transaction reads a range of records, another transaction inserts a new record within that range, and when the previous transaction reads the record for that range again, a magic line (Phantom row) is generated. The InnoDB and XTRADB storage engines solved the problem of phantom reading through multi-version concurrency control (mvcc,multiversion Concurrency control). The
SERIALIZABLE (serializable)
SERIALIZABLE is the highest isolation level. It is executed serially by forcing the transaction to avoid the above-mentioned phantom-reading problem. Simply put, serializable will lock up every row of data, so it can cause a lot of timeouts and lock contention issues. This isolation level is rarely used in real-world applications, and is only considered when there is a need to ensure that data is consistent and acceptable without concurrency.

MVCC background:
MySQL transaction uses REPEATABLE READ (REPEATABLE Read) isolation level by default. This isolation level still generates a phantom read problem. That is, when a transaction reads a range of records, another thing inserts a new record in that range, and the previous transaction reads the record of that range again, producing a magic line. MySQL InnoDB is a multi-version concurrency control (MVCC) to solve the Phantom reading problem.
MVCC Introduction:
MVCC combines row locks of a database with multiple versions of a row, with minimal overhead, to enable non-locking reads, which greatly improves the concurrency performance of the database system.
MVCC implementation principle:
MVCC saves a snapshot of the data at a point in time. Within a transaction, you see a snapshot of the same version, and the data is consistent. The data that the different transactions see at the same point in time will be inconsistent because they are getting the same version of the data. InnoDB There are additional hidden fields in each row of records, one column stores the version number of the row being updated, and the other column stores the version number of the row being deleted. Whenever a transaction starts, InnoDB assigns an incremented version number to the transaction, so the version number can also be considered a transaction number. For each "query" statement, InnoDB will compare the version number of this query statement with the version number of the row encountered by the query. A different transaction isolation level is then combined to determine whether to return the row. When the isolation level is repeatable read, the SELECT, delete, INSERT, UPDATE statements operate under this policy:
1) Select for the SELECT statement, only rows that satisfy the following two conditions are returned:
? The modified version number of the line is less than or equal to the transaction number
? The deleted version number of the row is either not defined or is greater than the version number of the transaction: the deleted version number of the row is not deleted if it is not defined, or if the delete version number is greater than the transaction number of the current transaction. Indicates that the row was deleted by a transaction that was started after the transaction, because it is the repeatable read isolation level, after which the effect of the starting transaction on the data should not be seen by the first transaction, so the row should be returned.
2) Insert to the newly inserted row, the updated version of the row is modified to the transaction number of the transaction
3) Delete for deletion, InnoDB directly sets the deleted version number of the row to the current transaction number, which is equivalent to being marked for deletion instead of actually deleting
4) Updat E when updating the line, InnoDB copies the original line to the rollback segment and takes the current transaction number as the updated version of the row

InnoDB Storage Engine MVCC implementation strategy:
Save two extra hidden fields in each row of data: The version number at the time the current row was created and the version number when it was deleted (possibly empty). Each transaction has its own version number, so that the data versioning is achieved by comparing version numbers when performing CRUD operations within a transaction. See below for specific practices.

MVCC Disadvantages:
In order to implement multiple versions, InnoDB needs to maintain additional hidden fields and to clean up unwanted row versions, with additional overhead.

Verify (Open two terminals at the same time, the left side is thing a, the right side is the thing B; The table data is empty)
One, the thing level is RC

1. Thing a insert (update), Thing B reads
Follow the procedure:

We can come to the conclusion
1) When the thing a inserts the data does not commit, the thing B does not find the data (avoids the dirty reading)
2) When thing a submits the thing, the thing B can find the data (the same thing inside two times the result is inconsistent; cannot be read repeatedly)

2,a things update, b things are also updated (deleted).

When thing a updates the data, it locks the action line. When thing b updates this data, it needs to wait for things a to be committed or rolled back before it can be executed. So whether repeatable reads have no effect on the results, you need to wait for thing a to finish before thing B executes.
Second, the thing level is RR

1. Thing a inserts, thing B reads

We can come to the conclusion
1) When the thing a inserts the data does not commit, the thing B does not find the data (avoids the dirty reading)
2) When thing a submits the thing, the thing B still has no data (the same thing inside two times the result is consistent; repeatable reading)

Mysql InnoDB Transaction