This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.

Concurrency Control: Multi-version Concurrency Control.

  • Why is MVCC used in MySQL

    • Solve the problem of read and write: read committed and unrepeatable read
    • No need to lock, improve read and write efficiency

  • What is read submitted

    • Only updates committed by other transactions can be read in a transaction

    • For example:

      • Select * from user where id=1;

      • Open transaction A, change “Zhang SAN” to “Li Si”;

      • Select name from user where id=1

        • If transaction A has not committed, it reads “John”;

        • If transaction A is committed, it reads “Li Si”;

          If transaction A is not committed, transaction B reads “Li Si”, this situation is called the occurrence of dirty read; Read Uncommitted.

  • What is repeatable

    • Multiple reads of the same row in a transaction have the same result and are not affected by other transactions

    • For example,

      • Select * from user where id=1;

      • Open transaction A, change “Zhang SAN” to “Li Si”;

      • Select name from user where id=1

        • If transaction A has not committed, it reads “John”;
        • If transaction A has been committed, it still reads “John”;

  • How to resolve RC and RR

    • Use undo log and readView

  • What is undo log

    • Transactions have the four properties of ACID, where A is atomicity, meaning that all operations in A transaction either complete or are unaffected.

    • To ensure atomicity of transactions, MySQL keeps a history of all operations and uses this log to roll back previous operations to the unmodified point during a transaction rollback. This log is called the undo log.

    • In addition to the user-defined fields in each row of mysql data, there are two hidden fields: trx_id and roll_pointer, which are also hidden in undo log.

      • trx_idIndicates which transaction generated the undo log for the row
      • roll_pointerIt can be understood as a pointer, which is used to chain the undo log of the same row of data to form an undo log chain.

  • What is a ReadView

    • At the RC and RR isolation levels, ReadView is introduced during transaction execution to ensure data isolation, that is, which data is visible in which transaction version.

    • Several important data are recorded in ReadView:

      • m_ids: list of transaction ids that were active when the ReadView was generated
      • min_trx_id:m_idsThe smallest transaction ID in
      • max_trx_id: When generating ReadViewThe next transaction ID that the system should assign
      • creator_trx_id: creates the transaction ID of the ReadView

  • How to solve visibility with ReadView

    • If trx_id=creator_trx_id, the version data is updated by the current transaction.

    • If the trx_id of the accessed version is less than min_trx_id, it indicates that the data generated for this version has been committed during ReadView generation.

    • If the trx_id of the accessed version is greater than or equal to max_trx_id, the data generated for this version is not enabled when ReadView is generated.

    • If the trx_id of the accessed version is between min_trx_id and max_trx_id, check whether trx_id is in m_IDS

      • If yes, it indicates that the version of the transaction is still active and invisible when the ReadView is generated.
      • If not, this version of the transaction was committed when the ReadView was generated.

  • How can isolation be resolved

    • RC

      • A ReadView is generated each time data is read
      • This allows you to read updates to committed transactions each time
      • For example:
        • The current trx_idFor 5
        • In the ReadView generated for the first timem_idsFor [3 and 6]
        • The second generated ReadView is [5,6,7]
        • Trx_id =6; trx_id=6; trx_id=6; trx_id=6;

    • RR

      • A ReadView is generated when the data is read for the first time and is used for the rest of the data
      • Since the same ReadView is used to read the data each time, the visible version is the same, that is, repeatable reads.