In Mysql, the undo log version chain mechanism is used to implement ReadView mechanism. In Mysql, the undo log version chain is used to implement ReadView mechanism.

ReadView

What a ReadView is, simply put, is when you start a transaction, a ReadView is generated for you to implement the MVCC mechanism. Here are some key things included in ReadView

  • M_ids: this indicates which transactions in the mysql transaction are not committed at this time
  • Min_trx_id: minimum transaction Id, that is, the smallest transaction Id in m_IDS
  • Max_trx_id: indicates the maximum transaction Id, which is the Id of the next transaction to be generated by Mysql
  • Creator_trx_id: indicates the Id of the current transaction

After every row in Mysql there are two hidden fields, trx_id and roll_pointer

  • Trx_id: indicates the transaction Id of the current data update
  • Roll_pointer: points to the next undo log version chain and is used to find data in the undo log version chain

RC ISOLATION implementation principle

Read Committed: transactions can only read committed data; In Mysql, RC is implemented by generating a new ReadView for each transaction query.

MVCC implements RC isolation level: each query generates a new Readview, as shown in the following example

1. Here is a piece of data from the database:

Trx_id = 1; trx_id = min_trx_id; trx_id = min_trx_id; trx_id = min_trx_id;

3. If transaction B(id = 25) modiates a row and commits it, the trx_id of the row will be changed to trx_id = 25, and roll_pointer will point to the undo log version chain of the row where trx_id = 10. Transaction A initiates the query again and A new ReadView will be generated, so trx_id is in the range min_trx_id and ma_trx_id and not in the m_IDS list, so the transaction change has been committed and can be read at the RC isolation level.

4. When transaction A changes the value of trx_id in m_IDS, the value of trx_id in ReadView is the same as that of creator_id in ReadView. So at this time A transaction to query can be queried.

RR ISOLATION implementation principle

Repeatable read: transaction A is queried multiple times with the same result; Mysql implements RR isolation by generating a fixed ReadView.

MVCC implements RR isolation level: Each query will use the ReadView used when the transaction is started, as shown in the following example

1. Here is a piece of data from the database:

Id = 20; id = 25; id = 25; id = 25; id = 25;

3. If transaction A(id = 20) starts the query and the current data trx_id = 10 is found to be less than min_trx_id, then this data has been submitted before transaction A starts, so this data can be queried.

Id = 25; trx_id = 25; ReadView = m_IDS; trx_id = 25; Therefore, the data cannot be queried at the RR isolation level.

Select * from m_ids where id = 20 and trx_id = 20 and trx_id = 20 Trx_id = 20, creator_trx_id = 20, creator_trx_id = 20, creator_trx_id = 20, creator_trx_id = 20, creator_trx_id = 20

Select * from ReadView where trx_id = 30; trx_id = 30; trx_id = 30; However, if trx_id is greater than max_trx_id, it indicates that this data is started after transaction A is started, so it cannot be queried.

conclusion

MVCC implementation of RR and RC isolation level implementation, rookie learning summary, big guy do not spray.