Detailed explanation of MySQL transaction isolation level and MVCC

Transaction Isolation Level

Problems encountered in concurrent transaction execution

• Dirty Write
  
  • If a transaction modifies data that has been modified by another uncommitted transaction, it means a dirty write has occurred.
    
• Dirty Read
  
  • If a transaction reads data that has been modified by another uncommitted transaction, it means a dirty read has occurred.
    
• Non-repeatable read
  
  • If a transaction can only read data modified by another committed transaction, and the transaction can query the latest value every time other transactions modify and commit the data, it means that a non-repeatable read has occurred.
    
• Phantom Read
  
  • If a transaction first queries some records based on certain conditions, and then another transaction inserts records that meet these conditions into the table, when the original transaction queries again according to the conditions, it can also read the records inserted by the other transaction, which means that a phantom read has occurred.
  • Phantom reads emphasize that when a transaction reads records multiple times according to the same condition, the later reads read records that were not read before.
  • What about the situation where a record that has been read previously cannot be read later? In fact, this is equivalent to a non-repeatable read occurring for each record. Phantom reads simply emphasize that records that were not obtained in previous reads were read.
    

Four isolation levels in the SQL standard

• READ UNCOMMITTED: Uncommitted dirty read, non-repeatable read, and phantom read occur
• READ COMMITTED: Read committed, non-repeatable read, phantom read occurs
• REPEATBLE READ: Repeatable read phantom read occurs
• SERIALIZABLE: Serializability does not occur
  

Four isolation levels supported in MySQL

• MySQL can prevent phantom reads in REPEATABLE READ isolation level (we will explain how to prevent phantom reads later).
• The default isolation level of MySQL is REPEATABLE READ
  

MVCC Principle

Version Chain

For tables using the InnoDB storage engine, their clustered index records contain two necessary hidden columns:

• trx_id: Every time a transaction modifies a clustered index record, the transaction id of the transaction is assigned to the trx_id hidden column.
• roll_pointer: Every time a clustered index record is modified, the old version is written to the undo log, and then this hidden column is equivalent to a pointer, which can be used to find the information before the record is modified.
  

ReadView

• For transactions using the READ UNCIMMITTED isolation level, since you can read records modified by uncommitted transactions, you can just read the latest version of the records directly.
• For transactions using the READ COMMITTED and REPEATABLE READ isolation levels, it is necessary to ensure that the records modified by the committed transactions are read. That is to say, if another transaction has modified the records but has not yet been committed, the latest version of the records cannot be directly read. Core problem: It is necessary to determine which version in the version chain is visible to the current transaction. ReadView is designed for this purpose
• readView contains 4 important contents:
  • m_ids: indicates the transaction ID of the active read and write transactions in the current system when the ReadView is generated
  • min_trx_id: indicates the smallest transaction id among the active read and write transactions in the current system when the ReadView is generated, that is, the minimum value in m_ids
  • max_trx_id: indicates the id value that should be assigned to the next transaction in the system when generating a ReadView
  • creator_trx_id: indicates the transaction ID of the transaction that generated the ReadView
    
    • As we said before, a transaction id is assigned to a transaction only when changes are made to records in a table (when INSERT, DELETE, UPDATE statements are executed). Otherwise, the transaction id value in a read-only transaction defaults to 0.
• With this ReadView, when accessing a record, you only need to follow the steps below to determine whether a version of the record is visible:
  • If the trx_id attribute of the accessed version is the same as the creator_trx_id in ReadView, it means that the current transaction is accessing the record that it has modified, so the version can be accessed by the current transaction.
  • If the accessed trx_id attribute value is less than the min_trx_id value in ReadView, it indicates that the transaction that generated this version has been committed when the current transaction generates ReadView, so this version can be accessed by the current transaction.
  • If the trx_id attribute value of the accessed version is greater than or equal to the max_trx_id value in ReadView, it means that the transaction that generated this version was opened after the current transaction generated ReadView, so this version cannot be accessed by the current transaction.
  • If the trx_id attribute value of the accessed version is between the min_trx_id and max_trx_id of the ReadView, it is necessary to determine whether the trx_id attribute value is in the m_ids list. If it is, it means that the transaction that generated the version when the ReadView was created is still active and the version cannot be accessed. If it is not, it means that the transaction that generated the version when the ReadView was created has been committed and the version can be accessed.

To summarize:

• A transaction at the READ COMMITTED isolation level generates a separate ReadView at the beginning of each query.
• REPEATABLE READ: Generates a ReadView when reading data for the first time, which means that the results obtained from the two SELECT queries are duplicated.
  

MVCC Summary: The so-called MVCC refers to the process of accessing the version chain of records when executing ordinary SELECT operations using transactions at the READ COMMITTED and REPEATABLE READ isolation levels. This allows read-write and write-read operations of different transactions to be executed concurrently, thereby improving performance.

How to solve phantom read in MySQL at RR level

1. Currently reading, the latest version is read, and the lock of the corresponding record needs to be acquired, as shown in the following SQL

• select ... lock in share mode
• select ... for update
• update, delete, insert
  

Phantom reading is achieved through next-key

2. Snapshot read is solved by mvcc

The above is a detailed explanation of the isolation level and MVCC of MySQL transactions. For more information about the isolation level and MVCC of MySQL transactions, please pay attention to other related articles on 123WORDPRESS.COM!