Writing in the front

This is the first chapter of “High Performance MySQL”

And the photoshoot is pink (๑• ㅂ•́)و✧

The transaction

Simply put, a transaction is a set of atomic SQL queries that either all execute successfully or all fail. Here is a brief introduction to the ACID of a transaction, which stands for atomicity, consistency, isolation, and persistence.

  • Atomicity: A transaction is an indivisible minimum unit of work. The entire transaction will either all succeed or all fail. It is impossible to perform only a part of the middle operation.
  • Consistency: A transaction is executed to move the database from one consistent state to another. If the transaction is not committed, the changes made by the transaction are not saved to the database.
  • Isolation: Normally, a transaction is not visible to other transactions until it is committed, but this invisibility requires consideration of isolation levels, such as uncommitted reads that are visible to other transactions before committing. Isolation levels are discussed below.
  • Persistence: Once a transaction is committed, the changes made to the database are stored permanently, and the modified data will not be lost even if the database crashes.

Isolation level

There are four isolation levels defined in the SQL standard, which are briefly described here.

  • Uncommitted reads: Uncommitted reads mean that changes in a transaction, even if they are not committed, are visible to other transactions, but dirty reads can occur and uncommitted reads are not normally used.
  • Commit read: Commit read means that changes made by one transaction are not visible to other transactions until they are committed. This level is also called “non-repeatable read” because the same operation may be performed twice with different results.
  • Repeatable reading: Repeatable read, solved the problem of the dirty reads this level to ensure the same transaction reads many times also records the result is consistent, but the isolation level cannot solve the problems of the phantom reads, the so-called phantom read that is to say, when a transaction read range data, another transaction and insert a new record in the scope, when before the transaction again read the range data, It creates illusions. InnoDB storage engine solves the phantom read problem with MVCC. Repeatable reads are MySQL’s default transaction isolation level.
  • Serializable: This is the highest isolation level and avoids the illusion problem mentioned earlier. Serializability locks every row read, which can lead to a lot of timeouts and lock contention issues, and this isolation level is rarely used in practice.

A deadlock

A deadlock is when two or more transactions occupy the same resource and request to lock the resource occupied by the other. The solution to deadlocks is to roll back one or more transactions.

MVCC

MVCC can be seen as a variant of row locking. In many cases, MVCC avoids locking and therefore costs less. Different transactional storage engines implement MVCC differently. MVCC is implemented by saving a snapshot of the data at a point in time. That is, each transaction sees the same data no matter how long it executes. Depending on the transaction start time, each transaction may see different data for the same table at the same time. Here is a brief introduction to InnoDB MVCC. InnoDB’s MVCC is implemented by keeping two hidden columns after each row of records. These two columns, one holding the creation time of the row and the other holding the expiration time of the row, store not the actual time, but the version number. The system version number is automatically incremented each time a new transaction is started. The system version number at the start of the transaction is used as the version number of the transaction and is compared with the version number of each row queried. The following details how MVCC operates at the repeatable read isolation level.

  • SELECT
    • InnoDB checks each record based on two criteria:
      • Look only for rows whose version is less than or equal to the transaction version number
      • Only rows with an undefined delete time or a delete time greater than the transaction version number are found
  • INSERT
    • InnoDB stores the current system version number as the row version number for each newly inserted row
  • DELETE
    • InnoDB stores the current system version number as the deleted version number for each row that is deleted
  • UPDATE
    • InnoDB adds a new record that saves the current system version number as the version number of the new row
    • On the original row of the deleted record, save the current system version number as the deleted version number of the deleted record row

Advantages:

  1. Because there are two hidden columns to record the state of the data, most read operations can be unlocked
  2. Good performance, while ensuring that the read data is correct

Disadvantages:

  1. Extra space is required to record the state of each row
  2. Row state needs to be maintained and checked

How to solve phantom reading

InnoDB uses a next-key lock to solve phantom reading. InnoDB uses a next-key lock to solve phantom reading.

  • Record Lock: A Lock on a single row Record that holds the index
  • Gap Lock: Interval Lock, locking a range, but not including the record itself, open interval
  • Next-key Lock: Record Lock + Gap Lock

To take a simple example,

select id from user where id > 15 and id < 30
Copy the code

The diagram clearly shows the gap lock ~

conclusion

This chapter seems to have been sent a bit late, it should have been sent before the index. But, still thanks for watching ~ finally vindicate me Pan, mua! (first ╯3╰), Happy Tanabata ~

Reference

High Performance MySQL