Shulou(Shulou.com)05/31 Report--

This article mainly explains "the way of MySQL to ensure data consistency". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the way of MySQL to ensure data consistency".

1. MySQL transaction model ACID

MySQL is a multi-engine database, of which InnoDB supports database transactions and is the most commonly used engine. The transaction model of InnoDB is introduced below.

The official MySQL documentation describes transactions as "transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, all changes are successful when the transaction is committed or undone when the transaction is rolled back."

"the ACID model is a set of database design principles that emphasize the importance of the reliability of business data and critical applications. MySQL includes innodb storage engine components that are closely integrated with the ACID model to ensure that the data is not corrupted and the results are not tampered with by anomalies such as software crashes and hardware failures. When you rely on the features of ACID, you no longer need to reinvent consistency checking and crash recovery mechanisms."

ACID model is divided into four characteristics according to alphabetical disassembly.

A: atomicity atomicity. Atomicity is our most intuitive understanding of transactions: a transaction is a series of operations, either all or none at all.

C: consistency consistency. Database transactions cannot break the integrity of relational data and the consistency of business logic. For example, for bank transfer transactions, regardless of the success or failure of the transaction, you should ensure that the deposits of Tom and Jack in the ACCOUNTS table remain unchanged after the transaction ends.

I: isolation isolation. In a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space.

D: durability persistence. As long as the transaction ends successfully, its updates to the database must be saved permanently. Even if a system crash occurs, the database can be restored to the state it was when the transaction ended successfully after restarting the database system.

Second, InnoDB storage engine architecture

The following figure shows the architecture of InnoDB, which consists of two parts, the memory structure (In-Memory Structures) and the structure on disk (On-Disk Structures).

In this diagram, pay particular attention to the "Redo Log" and "Undo Tablespaces" areas, which are closely related to transactions.

Memory structure (In-Memory Structures) is more about improving performance, so this article won't pay too much attention to it. If you are interested, you can visit MySQL's official website www.mysql.com.

"Undo Tablespaces" contains Undo Log (undo log), which is a collection of undo logging records that contains information on how to undo the latest changes recorded by a transaction to a clustered index. Undo Log exists in the undo log segment, which is contained in the rollback segment.

Of the four features of MySQL transactions, three features of ACD are implemented through Redo Log (redo log) and Undo Log, while I (isolation) is implemented through Lock (locks).

Third, popularize a concept MVCC

MVCC,Multi-Version Concurrency Control, multi-version concurrency control. This technique ensures that consistent reads are performed at the transaction isolation level of InnoDB, in other words, to query rows that are being updated by another transaction and to see their values before they are updated. This is a powerful technique that can be used to enhance concurrency so that queries do not have to wait for another transaction to release the lock. This technology is widely used in databases, such as Oracle,PostgreSQL. Of course, there are some database products and other storage engines of mysql that do not support it.

Take a look at the schematic diagram of the MVCC mechanism. The text explanation is given below.

The horizontal axis at the bottom of the picture is the time, and the vertical arrows are used to mark the time when additions, deletions, changes and queries occur. Pay special attention to the two color blocks above the timeline, which represent the two versions of the data, V1 and V2. In order to stand out, I circled V1 and V2 with red boxes (multiple versions). Interpret this picture from left to right

1. T1 transaction inserts the data axi3, then commits it, and generates the V1 version of the data.

2. T2 transaction begins to read a data, and the reading will last for a period of time. Since there is only V1 version at the beginning of reading, T2 will eventually read astat3.

3. In the process of T2 reading, T3 modifies the data an and generates the V2 version of the a data, but it is not submitted at this time, so the V1 version data is in effect.

4. Before the T3 modification is submitted, T4 reads the a data, and because the V1 version data takes effect at this time, T4 reads axi3.

5. T3 submits the modification of axi4, the data of V1 version is invalid, and V2 becomes effective. The value of a changes to 4

6. T5 reads the value of a, reads the V2 version, astat4

At this point, the concept of MVCC is understood, so how is MySQL implemented?

Fourth, the implementation of multiple versions of InnoDB.

1. Three hidden fields

Internally, InnoDB adds three fields to each row of data stored in the database.

(1) DB_TRX_ID field, 6 bytes. Represents the transaction identifier of the last transaction that inserted or updated the row. In addition, deletion is considered an update internally, where the special bit in the row is set to mark it as deleted.

(2) the DB_ROLL_PTR field, 7 bytes, is called a roll pointer. The rollback pointer points to the undo log (Undo Log) written to the rollback segment. If the row is updated, the undo log contains the information needed to rebuild the contents of the row before the update.

(3) DB_ROW_ID field, 6 bytes. Contains a row ID that monotonously increases as new rows are inserted, and if innodb automatically generates a clustered index, the index contains the row ID value. Otherwise, the DB_ROW_ID column will not appear in any index.

2. Multi-version generation process

The specific implementation is illustrated by adding a new record and modifying the record twice.

This record has three implicit fields (described earlier) that correspond to the ID of the line, the transaction number, and the rollback pointer.

When a new piece of data is inserted, the corresponding rollback segment pointer on the record is NULL

This process has done the following things

Lock the row with an exclusive lock

Copy the pre-modified value of the line to Undo Log

Modify the value of the current row and fill in the transaction number so that the rollback pointer points to the pre-modified row in Undo Log

Record Redo Log, including changes in Undo Log

After multiple updates, the rollback pointer strings different versions of the records together. There is a Undo Log thread in InnoDB that queries for Undo Log that are older than the oldest active transactions and deletes them, thus ensuring that the Undo Log file does not grow indefinitely.

3. Commit and rollback

When the transaction commits normally, InnoDB only needs to change the transaction state to commit, and no additional work is needed.

Rollback (rollback) needs to find the pre-modified version of the transaction from the Undo Log according to the current rollback pointer and restore it. If the transaction affects a large number of rows, the rollback may be slow, and according to experience, the number of uncommitted transaction rows is between 1000 and 10000, and InnoDB efficiency is still very high (Tang Cheng-database multi-version implementation insider).

High efficiency of commit and high cost of rollback

4. Visibility

Transaction isolation is one of the foundations of database processing, and isolation is the abbreviation "I" in ACID. The isolation level is a setting to fine-tune the balance between performance, reliability, consistency, and result reproducibility when multiple transactions make changes and execute queries at the same time.

InnoDB provides four isolation levels defined by the SQL1992 standard, READ UNCOMMITTED (uncommitted read), READ COMMITTED (committed read), REPEATABLE READ (repeatable read), and and SERIALIZABLE (serializable). The default is REPEATABLE READ

The specific meaning of each isolation level can be found by Baidu, and the implementation principle is more complex. Note the DB_TRX_ID sequence of the transaction ID field hidden in each data, which can theoretically be used to implement functions related to the isolation level according to some strategies. In fact, InnoDB does the same thing. Of course, this function also involves a lot of lock issues, which will no longer be expanded here.

The official MySQL documentation introduces InnoDB locks at the beginning of the chapter "Lock and transaction Model". Cut a directory and read it if you are interested.

Thank you for reading, the above is the content of "the way MySQL ensures data consistency". After the study of this article, I believe you have a deeper understanding of the way MySQL ensures data consistency, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.