A simple understanding of MySQL locks, transactions, and MVCC

mysql TutorialThe column introduces a simple understanding of MySQL locks, transactions, and MVCC.

How does MySQL implement ACID for transactions?

The transaction has the four characteristics of ACID, so how does MySQL implement these four attributes of the transaction?

• Atomicity
  Either all succeed, or all fail. MySQL achieves atomicity by recording undo_log. undo_log is rollback log, write undo_log to disk before real SQL execution, and then Operate on database data. If an exception or rollback occurs, the reverse operation can be performed according to the undo_log to restore the data as it was before the transaction was executed.

• persistent sex
  Once a transaction is committed normally, its effect on the database should be permanent. At this time, even if the system crashes, the modified data will not be lost. As the storage engine of MySQL, InnoDB stores data on disk, but if disk IO is required for every read and write data, the efficiency will be very low. To this end, InnoDB provides a cache (Buffer Pool) as a buffer for accessing the database: when reading data from the database, it will first read from the Buffer Pool, if there is no Buffer Pool, it will be read from the disk and put into the Buffer Pool ; when data is written to the database, it will be written to the Buffer Pool first, and the modified data in the Buffer Pool will be periodically refreshed to the disk.

  This design also A corresponding problem arises: If the data is submitted, but the data is still in the buffer pool (the disk has not been flushed yet), what should I do if MySQL crashes or powers off? Will data be lost?

  The answer is no , MySQL guarantees persistence through the mechanism of redo_log. redo_log is redo log, simply speaking, when data is modified, in addition to modifying the data in the Buffer Pool, This operation will also be recorded in the redo_log; when the transaction is committed, the fsync interface will be called to refresh the redo_log. If MySQL is down, you can read the data in redo_log when restarting to restore the database.

• isolated Character

  Isolation is The most complicated one in ACID, which involves the concept of isolation level, there are four in total

  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable

  Simply speaking, the isolation level specifies: which transactions can modify data in a transaction.; line-height: 1.75; margin: 0.8em 0; font-size: 16px; color: #353535;”>Repeatable read isolation level solves the problem of non-repeatable read, multiple reads in a transaction will not appear different As a result, repeatable reading is guaranteed. In the previous article, we said that there are two ways to implement Repeatable read, one is the way of pessimistic locking, and the relative MVCC is the way of optimistic locking.

  Repeatable read isolation level can solve non-repeatable read root cause is actually the generation of read view The mechanism is different from Read committed.

  • Read committed : As long as the data submitted before the execution of the current statement is visible.
  • Repeatable read: As long as the data submitted before the execution of the current transaction is visible.

  Unlike Read committed, in Repeatable read Under the isolation level, when a transaction is created, the current global read view is generated and maintained until the end of the transaction. This enables repeatable reads.

  Phantom reading and Next-Key lock

  Current read and snapshot read

  Through the MVCC mechanism, although the data becomes repeatable, we read The received data may be historical data, untimely data, not the current data of the database! For this way of reading historical data, we call it snapshot read (snapshot read), and read The way to get the current version of the database is called current read (current read) Reference^[3]

  • Snapshot read: select

    • select * from table ….;
  • Current read: special read operations, insert/update/delete operations, which belong to the current read, deal with the current data and need to be locked .

    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert;
    • update ;
    • delete;

  Resolve phantom reading

  In order to solve the magic in current reading Reading the question, MySQL transactions use a next-key lock.

  The above is the detailed content of a simple understanding of MySQL locks, transactions, and MVCC. For more information, please pay attention to other related articles on 1024programmer.com!

  20px;” data-id=”heading-15″>Solve phantom reading

  To solve Phantom reading problem in current reading, MySQL transaction uses next-key lock.

  The above is for MySQL For more details on the simple understanding of locks, transactions, and MVCC, please pay attention to other related articles on 1024programmer.com!