This is the first day of my participation in Gwen Challenge

The introduction

Mysql storage engines originally had quite a few, but as business and scenarios have been iterated, now business is basically using InnoDB, and the use of engines that do not support transactions is becoming less and less. I’m going to talk about what magic is and how InnoDB solves magic.

What is illusory

We know that InnoDB has four transaction isolation levels

  • Read uncommitted
  • Read the submission
  • Repeatable read
  • serialization

Dirty read

There’s one before we talk about hallucinationsDirty read. Dirty reads are easier to understand. They read data that has not yet been committed by another transaction.Since dirty reads read data that has not been committed by another transaction, this is changed to read commit, stating that reads in a transaction can only read data that has been committed by another transaction.

Unrepeatable read

Read commits solve the problem of dirty reads, but introduce another problem, that isUnrepeatable readIn the same transaction, reading the same data can get different results.

Phantom read

A typical scenario for phantom reading is that I query A range of data in transaction A, pour 1 at first, then another transaction B inserts another one in that range, and then transaction A checks the same range again and finds an extra one

The difference between unrepeatable and phantom reads

At first glance, unrepeatable reading and phantasmagoric reading are similar. However, there is a conceptual difference between update and DELETE that cannot be repeated and insert that cannot be repeated. Unrereading can be done by locking this data so that no other transaction can update it. Phantom reading cannot be solved by row locking, except by locking the table, which is expensive.

serialization

Whether the phantom or unrepeatable read is handled by serialization, completely serial processing, it does not have the above problems, but it is certainly the worst efficiency.

How does InnoDB solve phantom reading

Current read and snapshot read

Locking Reads: Locking Reads, such as update, DELETE, insert, SELECT.. For update, the select… In share mode. Consistent Nonlocking Reads (Consistent Nonlocking Reads) cannot solve the scope search mentioned above: As its name implies, the read is a snapshot. Because of multiple versions of MVCC, snapshot Reads solve the phantom read such as regular select

MVCC (Multi-Version Concurrency Control)

Multi-version concurrency control, which literally means having multiple versions of data. How does mysql InnoDB implement MVCC? First of all, each row of data has two default columns in addition to the fields we can see (possibly three columns if you haven’t set the increment ID, if not, there is a default increment ID row_id).

  • Trx_id Records the transaction ID when the current data is inserted or updated
  • Roll_ptr rollback pointer, transaction rollback is implemented through roll_ptr. Redo log is a persistent log. Undo log is a rollback log. When a record is updated in a transaction, it looks like this:
  1. Exclusive locks are placed on the data to be updated
  2. Write old data to undo log before updating
  3. The new data is then updated and the roll_ptr of the new data is set to point to the old data that was just undo log.
  4. Write redo log to update trx_id to the current transaction ID and set roll_ptr
  5. Release exclusive lock

This way, when it comes time to roll back, the previous data can be found through roll_ptr. For deletions, which are not really deletions, the data is labeled as deleted and waits for the Purge thread to clean it up

What does MVCC do when we query data

  1. First, the current transaction has an ID: m_creator_trx_id
  2. Then get the set of transaction ids that are executing: m_IDS (ascending)
  3. There is a minimum transaction ID: m_ids[0]
  4. There is also a transaction ID to assign next: m_ids[len(m_IDS)-1]+1
  5. If the version of the accessed data is the same as m_CREator_trx_id, the current transaction modified it, then it can be accessed.
  6. If the version of the accessed data is less than m_IDS [0], then the data was committed before this transaction and is accessible
  7. If the version of the accessed data is greater than or equal to m_IDS [len(m_IDS)-1]+1, the data is committed by a higher version transaction and cannot be accessed
  8. If the version of the accessed data is between m_ids[0] and m_ids[len(m_ids)-1]+1, then check whether it is in m_IDS (because it is ordered, use binary method). If the transaction is active, then it cannot be accessed; if not, it can be retrieved.
  9. When inaccessible data is accessed, roll_ptr is used to keep looking until an accessible version is found

Select * from xx where name=xx (select * from xx where name=xx

  1. Mysql > select * from table where name does not have index; mysql > select * from table where name does not have index; mysql > select * from table where name does not have index; mysql > select * from table where name does not have index; The whole process is expensive and affects concurrency.
  2. Innodb next Key lock innoDB next key lock innoDB next key lock innoDB next key lock innoDB next key lock InnoDB next key lock Next key lock=gap lock+record lock, divide data into open and close form (-∞,5] (5,10] (10,15] (15,20) (20,+∞), assume name=xx corresponding index 15, Select * from xx where name=xx for update (10,15,20) If you insert the same name (5,15), the index position will look like this: (1,5) (2,10) (3,15) (5,15) (4,20

3. If name is the only index, then record lock is degraded to a single row. Because of the unique index constraint, no other transaction can insert the same data.