Concepts of dirty read, phantom read, and unrepeatable read

1. Dirty read: a transaction accesses uncommitted data from another transaction. When a transaction is accessing data and making changes to the data that have not yet been committed to the database, another transaction also accesses the data and then consumes it.

2. Non-repeatable read: a transaction queries the same record twice and gets inconsistent results. Read the same data multiple times within a transaction. The same data is accessed by another transaction while the transaction is still active. Then, between the two reads in the first transaction, the data read in the first transaction may not be the same because of the modification in the second transaction. This happens when the data read twice in a transaction is not the same and is therefore called a non-repeatable read.

3. Unreal read: a transaction is queried twice, and the number of records obtained is inconsistent. Phantom reading is a special case of unrepeatable reading. The first transaction adds or removes corresponding data in the interval between the first and second queries, and the first transaction does not have the same number of queries, as if in an illusion.

Transaction isolation level

1. Read Uncommitted is when changes made by a transaction can be seen by other transactions before a transaction has committed. Generally speaking, someone else’s transaction to change data has not been committed, and I can read it in my transaction.
2. Read Committed means that after a transaction is committed, the changes it makes can only be seen by other transactions. In layman’s terms, someone else’s transaction has been committed and I can only read it in my transaction.
3. Repeatable read means that the data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions. In layman’s terms, someone else’s transaction has been committed and I don’t read it in my transaction. The MySQL InnoDB storage engine supports an isolation level of repeatable reads by default.
4. Serializable, as the name implies, is that for the same row, “write” will add “write lock”, “read” will add “read lock”. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue. In plain English, if my transaction has not been committed, no one else can change the data.

• If the isolation level is“Read uncommitted”,V1The value of PI is 2. Transaction B has not committed yet, but the result has already been seen by A. soV2, V3They’re both 2’s.
• If the isolation level is“Read submit”,V1Is 1,V2The value of theta is 2. Transaction B’s updates are not seen by A until they are committed. So,V3The value of PI is also 2.
• If the isolation level is“Repeatable”,V1, V2,Is 1,V3Is 2. The reasonV21, which follows the requirement that the data seen by the transaction during execution must be consistent.
• If the isolation level is“Serialization”, the read lock will be added when transaction A executes “query value 1”. Transaction B cannot continue execution until transaction A commits. So from A’s point of view,V1, V2,Value is 1,V3The value of theta is 2.

MVCC

Concurrency Control is a Concurrency Control for multi-version Concurrency Control. Read/write conflicts are resolved without locking by reading the history of the specified version and by means of ensuring that the values read match the isolation level at the transaction. 🔗

For the use ofInnoDBFor tables in the storage engine, the clustered index record contains the following two necessary hidden columns:

trx_idEach time a transaction makes a change to a clustered index record, the transaction’sTransaction idAssigned totrx_idHide the column.

roll_pointer: Every time a change is made to a clustered index record, the old version is writtenThe undo logIn the. The hidden column acts as a pointer to the record before it was modified.

To determine which version in the version chain is visible to the current transaction, MySQL has devised the concept of ReadView. Four important things are as follows:

• m_ids: Was in the current system when the ReadView was generatedList of active transaction ids
• min_trx_id: Was in the current system when the ReadView was generatedMinimum transaction ID active, which is the minimum value in m_IDS
• max_trx_id: When generating ReadView, the system shouldThe transaction ID value assigned to the next transaction
• creator_trx_id:The transaction ID of the transaction that generated the ReadView

A unique transaction ID is assigned to an insert, delete, or update statement that changes a table record, otherwise the default transaction ID is 0. Max_trx_id is not the maximum value in M_IDS; transaction ids are incrementally assigned. When a new transaction is generated, the value of m_ids will be 1 and 2, min_trx_id will be 1, and max_trx_id will be 4.

The execution process is as follows:

• If the version is accessedtrx_id = creator_id, meaning that the current transaction is accessing its own modified record, so that version can be accessed by the current transaction;
• If the version is accessedtrx_id < min_trx_id, indicating that the transaction that generated this version was generated in the current transactionReadViewPreviously committed, so the version can be accessed by the current transaction;
• Of the version being accessedtrx_id >= max_trx_id, indicating that the transaction that generated this version was generated in the current transactionReadViewThis version cannot be accessed by the current transaction.
• Of the version being accessedtrx_idWhether inm_idsIn the list
  • Is to createReadView, the version is still active and cannot be accessed. Follow the version chain to find the next version of the data, continue to perform the above steps to determine the visibility, if the last version is not visible, it means that the record is completely invisible to the current transaction;
  • No, createReadViewWhen the transaction that generated the version has been committed and the version can be accessed.

MVCC implements read commit

MVCC implements Read Committed, generating a ReadView before each Read.

Create data table:

CREATE TABLE `girl` (
  `id` int(11) NOT NULL.`name` varchar(255),
  `age` int(11),
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Here are three transactions executed, one line representing a point in time:

Select statement execution process when point in time is 5:

1. There are two transactions in the systemidTransactions of 100,200 are executing.
2. Generate a ReadView when performing select,Mids = [100200].min_trx_id = 100.max_trx_id = 201.creator_trx_id = 0(The SELECT transaction does not perform the change operation and the transaction ID defaults to 0).
3. Latest versionnameListed as Xi Shi, this editiontrx_idThe value is 100 atmidsList, does not meet the visibility requirements, according toroll_pointerSkip to the next version.
4. The next versionnameLiwang Zhaojun, the versiontrx_idThe value of 100 is also inmidsList, therefore does not meet the requirements, continue to skip to the next version.
5. The next versionnameListed as diao Chan, the versiontrx_idThe value is 10, less thanmin_trx_id, so the final returnnameThe value is diao cicada.

Select statement execution process when point in time is 8:

1. There is a transaction in the systemidA transaction of 200 is executing (transactionidTransactions for 100 have been committed).
2. Generate a ReadView when executing the SELECT statement,mids = [200].min_trx_id = 200.max_trx_id = 201.creator_trx_id = 0.
3. Latest versionnameListed as Yang Yuhuan, that editiontrx_idThe value is 200midsList, does not meet the visibility requirements, according toroll_pointerSkip to the next version.
4. The next versionnameListed as Xi Shi, the versiontrx_idThe value is 100, less thanmin_trx_id, so the final returnnameThe value is xi Shi.
5. When a transactionidFor 200 transactions committed when the query is obtainednameListed as Yang Yuhuan.