My knowledge of MySQL locks, transactions, and MVCC

Is a single SQL statement committed as a transaction when executed?

The following is excerpted from High Performance MySQL (version 3)

“

MySQL uses the AUTOCOMMIT mode by default. That is, if a transaction is not explicitly started, each query is committed as a transaction. In the current connection, you can enable or disable automatic commit mode by setting the AUTOCOMMIT variable

“


How does MySQL implement transactional ACID?

Transactions have four properties of ACID. How does MySQL implement these four properties of transactions?

  • Atomicity is either all success or all failure. MySQL implements atomicity by logging undo_log. Undo_log is a rollback log. The undo_log is written to disk before the actual SQL is executed, and then operations are performed on the database data. If an exception or a rollback occurs, you can do the reverse with undo_log to restore the data as it was before the transaction was executed.

  • Once a persistent transaction is committed normally, its impact on the database should be permanent. Even if the system crashes, the modified data will not be lost. As the storage engine of MySQ L, InnoDB stores data in disk, but if I/O of disk is needed for reading and writing data every time, the efficiency will be very low. For this purpose, InnoDB provides a Buffer Pool, which acts as a Buffer to access the database. When reading data from the database, it is read from the Buffer Pool first. If there is no Buffer Pool, it is read from disk and put into the Buffer Pool. When data is written to the database, it is first written to the Buffer Pool. The modified data in the Buffer Pool is periodically flushed to disks.

    This design brings up a problem: what if MySQL goes down and fails when the data is submitted while it is still in the buffer pool? Will data be lost?

    The answer is no, MySQL guarantees persistence through the redo_log mechanism. Redo_log is a redo log. When data is modified, the redo_log records the operation in the Buffer Pool. When a transaction commits, the fsync interface is called to flush the redo_log. If the MySQL database is down, you can read the data in redo_log to restore the database.

  • Isolation,

    Isolation is the most complex of ACID, and there are four isolation levels involved

    • Read uncommitted
    • Read committed
    • Repeatable read
    • Serializable

    In simple terms, the isolation level specifies which changes to data within a transaction are visible and which are not. Isolation is about managing the access order of multiple concurrent read and write requests.

    MySQL’s implementation of isolation will be discussed later.

  • consistency

    Consistency is achieved through rollback, recovery, and isolation in concurrent environments.

Problems that can be caused by transaction concurrency

From the last question I know that a single DDL execution can be automatically committed as a transaction, so either multiple SQL concurrency or multiple manually organized transactions containing multiple SQL concurrency can cause transaction concurrency problems.

Specifically, there are:

  • Dirty writes (data committed by one transaction overwrites data not committed by another transaction)
  • Dirty reads (one transaction reads uncommitted data from another transaction)
  • Non-repeatable reads (update and DELETE are read multiple times in a transaction)
  • Phantom read (insert a transaction with different numbers of read records)

We mentioned transaction isolation levels above. All isolation levels in MySQL guarantee that no dirty writes occur, so that leaves dirty reads, unrepeatable reads, and phantom reads.

Here’s a look at how each isolation level addresses or does not address these issues:


Read uncommitted

No read is committed. Locks are not applied to the read at this level but only to the write request. Therefore, the write operation will modify the data during the read and cause dirty reads. Unrepeatable and illusory readings also occur naturally.

Read committed

Committed read, and uncommitted read is also read unlocked, write locked. The difference is that the use of MVCC mechanism to avoid the problem of dirty read, there will also be unrepeatable read and phantom read problems. We’ll talk more about MVCC later.

Repeatable read

MySQL’s default isolation level, at which MySQL solves problems in two ways

  1. Read/write lock Read locks are added in parallel, and read locks are shared. Write lock is added whenever there is a write request, so that the read and write are serial. Data is read with locks and cannot be modified by other transactions. So there will be no unrepeatable reads. Data that is modified and deleted is also locked so that it cannot be read by other transactions, so there is no dirty read. The first is the so-called “pessimistic locking”, in which data is locked for the entire transaction, which is conservative and has a high performance cost.
  2. MVCC (later)

In addition, the Next Key lock is used to solve the illusion problem to a certain extent. We’ll talk more about that later.

Serializable

Transactions at this isolation level are executed sequentially. If auto commit is disabled, InnoDB implicitly converts all normal SELECT statements to SELECT… LOCK IN SHARE MODE. That is, a read shared lock is implicitly added to the read operation to avoid dirty reads, unrepeatable rereads, and phantom reads.

MVCC

“

Multiversion concurrency control (MCC or MVCC), is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory

“

Multi-version concurrency control (MCC or MVCC) is a concurrency control method commonly used by database management systems to provide concurrent access to databases and to implement transactional storage in programming languages.

Simply put, it’s a way for a database to control concurrency. Each database may have a different implementation of MVCC.

For example, MySQL InnoDB engine implements MVCC.

What problem does MVCC solve

As we can see from the above definition, MVCC mainly addresses the problem of data consistency in concurrent transactions

How InnoDB implements MVCC

The following figure is from High Performance MySQL (version 3)

This book is well written and translated, and my first systematic understanding of MySQL came from reading this book. However, there are some problems in the description of how MVCC is implemented.

What’s the problem

  • First look at the official documentation of MySQL. I have compared the documentation of three versions [1], 5.1, 5.6 and 5.7. The description of MVCC is almost the same.


According to the documentation, it is obvious to add three hidden columns to each column:

  • The 6-byte DB_TRX_ID field represents the transaction ID of the record that was last inserted or updated.
  • The 7-byte DB_ROLL_PTR field points to the undo log of the rollback segment.
  • The 6-byte DB_ROW_ID is automatically incremented when new data is inserted. InnoDB automatically generates a clustered index containing the DB_ROW_ID field when there is no primary key on the table.

Here I add a MySQL internal structure diagram containing rollback segments

Version of the chain

We have talked about the concept of undo_log before. Every undo log has a roll_pointer property, so all versions will be connected into a linked list by the roll_pointer property. We call this linked list version chain, and the head node of the version chain is the latest value of the current record.

ReadView

By hiding columns and version chains, MySQL can restore data to the specified version; But which version to restore to depends on ReadView. The so-called ReadView refers to that the transaction (denoted as transaction A) takes A snapshot of the entire transaction system (TRX_SYS) at A certain moment, and then compares the transaction ID in the read data with the TRX_SYS snapshot to determine whether the data is visible to the ReadView. That is, whether transaction A is visible. (Reference [2])

So far we have seen that MVCC is implemented based on hidden fields, undo_log chains, and ReadView.

MVCC in Read Committed

Earlier we talked about the use of MVCC to solve dirty reads at the Read Committed isolation level. Here I refer to two articles:

  • https://cloud.tencent.com/developer/article/1150633
  • https://cloud.tencent.com/developer/article/1150630

InnoDB only looks for rows whose version is earlier than the current transaction version (that is, rows whose version number is less than or equal to the transaction’s system version number) to ensure that data is read from rows that either existed before the transaction started or were inserted or modified by the transaction itself. Therefore, there is no dirty read.

Unrepeatable reads at the Read COMMITTED isolation level are due to the generation mechanism of the Read View. At the Read Committed level, data is visible as long as it was committed before the current statement was executed. During each statement execution, the read View is closed and the current copy of the Read View is recreated. This creates a transaction interval for the Read View based on the current global transaction linked list. At the Read COMMITTED isolation level, MVCC generates a snapshot version each time a select is committed. Therefore, a different version is Read each time a select is committed, causing unrepeatable reads.

Repeatable Read MVCC

Repeatable Read isolation level solves the problem of non-repeatable reads. Multiple reads in a transaction will not have different results, ensuring Repeatable reads. In the previous article, we said that Repeatable Read can be implemented in two ways, one is the pessimistic lock mode, while the opposite MVCC is the optimistic lock mode.

Repeatable Read isolation level can solve the root cause of Repeatable read because read View generation mechanism is different from Read COMMITTED.

  • Read COMMITTED: All data committed before the current statement is committed is visible.
  • Repeatable Read: All data committed before the current transaction is visible.

Unlike Read COMMITTED, at the isolation level of Repeatable Read, when a transaction is created, the current Global Read View is generated until the transaction ends. This enables repeatable reads.

Phantom read with next-key lock

Current read and snapshot read

The MVCC mechanism makes the data repeatable, but the data we read may be historical data, is not timely data, not the current data of the database! This method of reading historical data is called snapshot read, and the method of reading the current version of the database is called current read. [3]

  • Snapshot read: select
    • Select * from table… .;
  • Current read: Special read operations, such as insert/update/delete operations, are current read operations. They process current data and need to be locked.
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert;
    • update ;
    • delete;

Solve the phantom read

To solve the phantom read problem in the current read, MySQL transactions use a next-key lock.


Repeatable Read avoids phantom reading by next-key lock mechanism.

InnoDB storage engine has three algorithms for row locking:

  • Record Lock: A Lock on a single Record
  • Gap Lock: A Gap Lock that locks a range but not the record book
  • Next-Key Lock: Gap Lock + Record Lock

Next-key lock is a kind of row lock, which is equivalent to record lock + gap lock; It features not only locking the record itself (record lock function), but also locking a range (gap lock function).

When InnoDB scans an index Record, it first places a Record Lock on the index Record and then a Gap Lock on the gaps between the index records. With a gap lock, other transactions cannot modify or insert records in this gap.

When the query index contains unique attributes, next-key Lock optimizes and demotes it to a Record Lock, which locks only the index itself, not the range.

The following figure is quoted fromThe cloud community[4]

The resources

[1]

Mysql 5.7 document: https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html


[2]

Refer to the blog: https://www.cnblogs.com/kismetv/p/10331633.html


[3]

Meituan technology blog: https://tech.meituan.com/2014/08/20/innodb-lock.html


[4]

The cloud community: https://yq.aliyun.com/articles/108095

More exciting content please pay attention to the public account “small box technology sharing”