Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is the realization principle of consistency reading". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the principle of consistent reading implementation"?

Transactions in MySQL

In RDBMS system, the concept of transaction is basically the same, it is a unit of work constructed by a set of DML statements, either all succeed or all fail.

In the process of development, we are more concerned about long transactions, that is, work units with many DML statements. Too long transactions will lead to some errors, for example, the transaction packet size may exceed the parameter max_allowed_packet setting, which may lead to program errors, or there may be some SQL corresponding interface errors in the transaction, resulting in the failure of the whole service invocation. In program design, consideration should be given to avoiding the business impact of long transactions.

ACID of the transaction

Image-20201114221841801

Atomicity is the basis of transaction isolation, isolation and persistence are the means, and the ultimate goal is to maintain data consistency.

Concurrency of transactions

Dirty reading: transaction A reads uncommitted data from transaction B.

Non-repeatability: transaction A reads the same data many times, and transaction B modifies and commits the data in this process, resulting in inconsistent results of transaction A reading the same data many times.

Illusion: when transaction A modifies the data, transaction B inserts a piece of data. When transaction A commits, it is found that the data has not been modified, resulting in an illusion.

Unrepeatable reads focus on update operations, while phantom reads focus on insert or delete. To solve the problem of unrepeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table.

Transaction isolation level

Transaction isolation is one of the foundations of database processing. When multiple transactions change and execute queries at the same time, the isolation level adjusts the balance between performance and the reliability, consistency and reproducibility of results. InnoDB uses different locking strategies to support different isolation levels. There are four isolation levels in MySQL, namely read uncommitted (READ UNCOMMITTED), read committed (READ COMMITTED), repeatable read (REPEATABLE READ), and serialization (SERIALIZABLE).

Isolation level dirty reading cannot be repeated to read fantasy READ UNCOMMITTEDYesYesYesREAD COMMITTEDNoYesYesREPEATABLE READNoNoYesSERIALIZABLENoNoNo

InnoDB concurrency control

MVCC characteristics

InnonDB is a storage engine that supports row locks. In order to provide better support for concurrency, a non-lock read is used. Instead of waiting for the lock on the access data to be released, it reads a snapshot of the row. This method is implemented through the InnonDB MVCC feature.

MVCC is the abbreviation of Multi-Version Concurrency Control, that is, multi-version concurrency control. When a transaction occurs in parallel, under the premise of a certain isolation level, it can ensure that consistent reading can be achieved in a transaction, that is, the data read according to a certain condition when the transaction starts, until the end of the transaction, the same condition is executed again, or the same data is read, which will not change.

Benefits of MVCC

Read without lock, read and write without conflict. In OLTP applications with more reading and less writing, it is very important that there is no conflict between read and write, which can increase the concurrency performance of the system.

In MVCC, there are two kinds of read operations: snapshot and current read.

MVCC Snapshot

The consistent read snapshot used within MVCC is called Read View. Under different isolation levels, when the transaction starts or when the SQL statement starts, you may see different versions of the data snapshot. Read view is used under RR and RC isolation levels.

Each transaction in InnoDB has a unique transaction ID, called Transaction ID, which is applied to the transaction system of InnoDB at the beginning of the transaction and is strictly incremented in the order of application. Each row of data has multiple versions. Each time a transaction updates data, a new data version Read View is generated and Transaction ID is assigned to this data version of the transaction ID, marked row_trx_id. At the same time, the old data version should be retained, and in the new data version, you can get it directly with information. In fact, a row of records in the data table may have multiple data versions, and each version has its own row_trx_id.

InnoDB line format

Currently, the default row format of InnoDB, Dynamic, is an enhanced version of Compat format. The record header structure information takes up 5 bytes, and the transaction ID and rollback pointer occupy 6 and 7 bytes respectively. The row format is as follows:

Recording head structure

Item size (bit) description () 1Unknown () 1Unknowndeleted_flag1 data row delete mark min_rec_flag1=1 if the record is pre-defined as the smallest record n_owned4 has the number of records in the heap_no13 index heap the sort position of that record in the record_type3 record type 0000: normal, 001Transaction ID48 B+ tree leaf node, 010: pseudo column Infinum,011:Supernum,1xx: keep the transaction ID in the Transaction ID48 record of the next record in the next_record16page, fix the 6-byte Rollback Pointer56 rollback pointer, and fix 7 bytes

Data row storage

# create table mysql > create table store_users (id int not null auto_increment primary key comment 'primary key id',name varchar (20) not null default' 'comment' name') # View table status information mysql > show table status like 'store_users'\ G Row_format: Dynamic # default row format is Dynamic Rows: 0 # number of rows Avg_row_length: 0 # average row length Data_length: 16384 # initialize segment size 16K # start transaction and insert data mysql > begin Mysql > insert into store_users values (null, 'aaaaa'), (null,' bbbbb'); # View transaction ID mysql assigned by InnoDB > select trx_id from information_schema.innodb_trx\ G trx_id: 8407246 # transaction ID

Analyze the header information of the table as well as hidden transaction ID and rollback pointers.

# use the tool hexdump under Linux to analyze $hexdump-C-v / usr/local/var/mysql/test/store_users.ibd > store_users.txt $vi store_users.txt 00010060 02 00 1b 69 6e 66 69 6d 75 6d 00 03 0000b 00. Infimum. | 00010070 73 75 70 72 65 6d 75 6d 05 00 00 0010 00 1c 8000 | supremum. | 00010080 000100008048 ce 83.0000d8 01 10 61 | .H.a | # Record Header information 00010090 61 61 61 05 00 00 18 ff d6 80 00 00 00 02 00 | aaaa. | 000100a0 00 80 48 ce 83 00 0001 d8 01 1d 62 62 62 |.. H.bbbbb | 000100b0 00 00 00 |. |

10 indicates the length of the variable length field, only one varchar (20) does not exceed 256 bytes and has no null value.

00 represents the NULL flag bit, and the first row is not NULL data.

The hexadecimal value of the character an is 61, that is, 61 61 61 represents the field value aaaaa

00 00 00 80 48 ce 6 bytes is Transaction ID, converted to decimal 8407246, which is the value of the information_schema.innodb_trx.trx_id column above, trx_id: 8407246.

83 00 00 01 d8 01 10 7 bytes is Rollback Pointer.

1c 8000 00 01 is 5 bytes and represents Record Header information.

Isolation level and snapshot

REPEATABLE READ

The default isolation level, consistent read Snapshot (Read View), is established when the first SELECT is initiated and does not change after that. If multiple unlocked SELECT statements are issued in the same transaction, the results returned by these SELECT statements are consistent before the transaction commits.

The snapshot Read View under RR is not created when the transaction is initiated, but after the first SELECT is initiated.

READ COMMITTED

Under READ COMMITTED read submitted, consistent read snapshot (Read View) generates the latest Read View after each SELECT, that is, every time SELECT can read the COMMIT data, there will be non-repeatable reading and phantom reading.

Undo rollback segment

When the transaction execution update statement (insert/update/deeldte) is enabled, the execution plan is generated through the processing of the Server layer, and then the API of the storage engine layer is called to read and write the data. Before the user triggers COMMIT or ROLLBACK, the data of these Uncommitted Data is called Post Image, and the data is stored in Undo Log to facilitate user rollback or MySQL Server Crash recovery. At the same time, Undo Log is used for circular coverage.

# start transactions, update account balances, and do not commit transactions. Mysql > start transaction; mysql > update account set balance = 100000 where account_no = 10001; Rows matched: 1 Changed: 1 Warnings: 0

Above, under the RR isolation level, open a transaction, do the update update operation, do not commit the transaction, and check the undo situation through show engine innodb status\ G.

Trx id counter 8407258 Purge done for trx's n:o

< 8407257 undo n:o < 0 state: running but idle History list length 33 ...... ---TRANSACTION 8407257, ACTIVE 154 sec 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1 Trx id counter 8407258当前的事务ID，undo log entries 1使用了的undo entries，ACTIVE 154 sec事务持续时间，事务commit后，会调用Purge Thread把undo中的老数据清理掉。 回滚记录 insert：反向操作是delete，undo里记录的是delete相关信息，存储主键id即可。 udpate：反向操作是update，undo里记录的是update前的相关数据。 delete：反向操作是insert，undo里记录的是insert values(…..)相关的记录。 从这里可以知道，更新操作占用Undo空间的大小排序如下： delete >

Update > insert

Therefore, it is not recommended that physical delete delete data. A large number of Undo Log,Undo will be switched as soon as they are full, and there will be a large number of IO operations during this period, resulting in slow DML of the business.

Consistent reading

Description of consistent reads in the MySQL official documentation:

The read operation gets a snapshot of the data at that time based on a certain point in time, regardless of the modifications made by other transactions at the same time. During the query, if other transactions modify the data, you need to get the old version of the data from undo log. This can effectively avoid the problem of decreasing transaction parallelism due to the need for locking (to prevent other transactions from modifying these data at the same time).

At the REPEATABLE READ (RR) isolation level, the data snapshot version is created when the first read request is initiated. Under the READ COMMITTED (RC) isolation level, a snapshot is recreated each time a read request is made.

Consistent read is the default mode for InnoDB to process SELECT requests under RR and RC. Because a consistent read does not lock the table it requests, other transactions can modify data at the same time without being affected.

There are multiple versions of a row of data, each with its own trx_id, and each transaction or query generates its own consistent view through trx_id. An ordinary select statement is a consistent read, which determines the visibility of the data version based on row trx_id and consistency view. In the figure, UR1,UR2 is undo, which is stored in Undo Log. Each query constructs a consistent data page (Consistent Read Page) based on the current data page and Undo page, and returns the data to the user by reading CR Page.

At this point, I believe you have a deeper understanding of "what is the principle of consistent reading". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.