1: Multiple transactions update and query data concurrently. Why do dirty writes and dirty reads occur

  • Dirty wrote: Suppose there are two transactions A and B that modify A field of A row of data at the same time. The initial value is NUll. Transaction A first changes the value to A_value, then an undo log will be recorded for rollback, and transaction B changes the value to B_value and commits the transaction. The undo log of transaction A is NULL, and the undo log of transaction A is NULL. The undo log of transaction A is NULL, and the undo log of transaction B is NULL. So a B transaction is a dirty write if it changes the value, but it’s NULL
  • Dirty read: If the value of B_value is NULL, the value of B_value is NULL, and the value of B_value is NULL, the value of B_value is NULL, and the value of B_value is NULL, and the value of B_value is NULL. That is, reading data that has not been committed by other transactions

2: What is unrepeatable

  • Let’s say we have A transaction A that needs to read the value of name in the row with id=1 several times. Note that the query is in the same transaction. Let’s say we have A transaction B that needs to update the value of name in the first read. SQL > alter table name= ‘b’; SQL > alter table name= ‘b’

3: Scary fantasy

  • Let’s say we have A transaction A that needs to be queried based on conditions such as select * from user where id > 10; Select * from user where id > 10; select * from user where id > 10; select * from user where id > 10; That is, multiple queries in the same transaction with an inconsistent number of results. This is a phantom read

4: four transaction isolation levels of the SQL standard

  • The most common thing we talk about is MySql’s transaction isolation level, but now we’re going to talk about the SQL standard transaction isolation level, and MySql’s isolation level is pretty much the same
  • Read Uncommitted: This does not allow dirty writes, but does allow dirty, unrepeatable, and fantasy reads
  • Read COMMITTED: This does not allow dirty writes, dirty reads, but does allow unrepeatable reads, and fantasy reads
  • REPEATABLE READ: This does not allow dirty writes, dirty reads, and non-repeatable reads, but phantom reads still occur
  • Serializable: Dirty write, dirty read, unrepeatable read, phantom read does not occur, but has the worst performance

5: How does MySql support four transaction isolation levels

  • Now we know that REPEATABLE READ in SQL defined transaction isolation level is phantom, but REPEATABLE READ in MySql (RR for short) is not phantom, which is a difference of MySql. The other three transaction isolation levels are the same as those defined in SQL

6: Understand the MVCC prelude, what is the undo log chain

  • MVCC mechanism: The RR isolation level of MySql is higher than that of dirty reads, non-repeatable reads, and illusory reads. That is, transactions are isolated from each other. The underlying implementation principle is MVCC(Multi-version concurrency control mechanism), but you need to know the undo log chain before understanding MVCC
  • Trx_id: indicates the transaction ID of the most recently updated record. Roll_pointer: indicates the undo log that points to the transaction before the update

Suppose there is now A transaction A(ID =50) that inserts A record, and the hidden fields of this data point to the undo log as shown in the figure below

A transaction B(id=58) will run and change the value from A to B. An undo log will be generated before the update and roll_pointer will point to the undo logIf there is another transaction C(id=69) to modify the record, the same is true, as shown in the figureTherefore, when multiple transactions are executed in serial, each transaction changes a row of data and will update the hidden TRx_ID and roll_pointer. At the same time, multiple undo logs will be connected in series through the roll_pointer to form the undo log chain

7: What is the ReaView mechanism based on undo log multi-version chain

  • In simple terms, when you execute a transaction, you will be given a ReadView, which has four key items
    • M_ids: this means that there are any transactions running in MySql that have not yet been committed
    • Min_trx_id: is the smallest value in m_IDS
    • Max_trx_id: indicates the next transaction ID to be generated by mysql, which is the maximum transaction ID
    • Creator_trx_id: is the id of your transaction

8: How is the Read Committed isolation level implemented based on the ReadView mechanism

  • The Read Committed isolation level can Read data that has been Committed by another transaction, but can still be Read and illread
  • Suppose we now have A row of data, transaction ID 50, the transaction id equal to 50 was previously committed, and now we have two active transactions, transaction A and transaction B, as shown below

The current situation is that shiwuB is going to modify the data, so the transaction ID of the data is now 70 and claims an undo log, as shown belowTransaction A will generate A ReadView with min_trx_id=60,max_trx_id=71,creator_trx_id=60

Trx_id =70 (trx_id=70) =70 (trx_id=70) =70 (trx_id=70) =70 (trx_id=70) Trx_id =50 (trx_id=50); trx_id=50 (trx_id=50); trx_id=50 (trx_id=50)

  • If transaction B commits at this time, then transaction A initiates the query again, then it should be able to read the modified data of transaction B, but at this time transaction A still retains transaction B’s ReadView, so transaction A generates A new ReadView when it initiates the query request next time. In this way, m_IDS in ReadView will not have transaction B and will be able to read the data changed and committed by transaction B

9: How is MySql’s best isolation level implemented based on ReadView

  • Assume that there is already one data, trx_id=50, as shown below

There are two active transactions A and B, and transaction A wants to query this data, so transaction A will generate A ReadView, and there will be 60,70 transactions in the ReadView, namely transaction A and transaction B, and transaction A will read this data. If trx_id=50 is found to be less than min_trx_id in ReadView, it indicates that this row of data has been modified by transaction before, so it is read out. At this time, transaction B has modified this row of data, so the trx_id of this row of data is =70, as shown in the figure below

Trx_id =70 (trx_id=70, trx_id=70, trx_id=70, trx_id=70, trx_id=70) Trx_id =50, and 50 is less than min_trx_id in my ReadView, indicating that this line of data was submitted before the start of my transaction, so read this line of data. This is the non-repeatable read based on the ReadView mechanism

  • How to solve phantom reading?
    • Select * from user where id > 1; select * from user where id > 1; The trx_id of the data inserted by transaction C is 80.

The trx_id of the extra record is 80, which is greater than max_trx_id in the ReadView. That is to say, the data was executed after the transaction was started, so transaction A will not query this data. The result of transaction A’s next query is still A piece of data