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

How to understand the consistent reading of MySQL, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

A preface

MySQL provides two read modes of consistent read (unlocked) and current read (locked read) at different transaction isolation levels. At present, reading is relatively simple, and the editor mainly studies consistent reading.

The concept of two principles

Official concept

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database ata point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions.The exception to this rule is that the query sees the changes made by earlier statements within the same transaction.

Consistent reading refers to the use of MVCC mechanism to read the committed data of a transaction, which is actually a snapshot of the data obtained from undo. However, there are special cases: in this transaction, if the select after modifying a table can read the latest data of the table, the later example can verify.

Consistent reading of different transaction isolation levels

3.1 RR mode

From the official document "If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction."

In RR mode, snapshots of consistent reads within the same transaction are based on the first read operation. Let's do a test to interpret the consistent reading in RR mode.

A) the starting point of a transaction in RR mode is based on the first statement executed, rather than begin.

Session 1

Session2

Test [RW] 10:01:33 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:02:12 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:02:22 > select * from ty

Empty set (0.00 sec)

Test [RW] 10:02:36 > insert into ty (a meme b) values (1pm 2)

Query OK, 1 row affected (0.00 sec)

Test [RW] 10:02:51 > commit

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:02:33 > select * from ty

+-- +

| | id | a |

B) consistent reads in RR mode take the point in time of the execution of the first select statement as the point in time that the snapshot is established, even when accessing different tables.

Test [RW] 10:35:11 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:35:13 > select * from x

+-- +

| | id |

+-- +

| |

Test [RW] 10:34:32 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:34:51 > insert into ty (arecom b) values (2jue 4)

Query OK, 1 row affected (0.00 sec)

Test [RW] 10:35:39 > select * from ty

+-- +

| | id | a |

C) in RR mode, if the select statement on a table is modified within this transaction, the latest data of the table can be read.

Test [RW] 10:42:56 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:43:07 > select * from ty

+-- +

| | id | a |

Test [RW] 10:35:34 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 10:43:25 > insert into ty (amemb) values (3pyr5)

Query OK, 1 row affected (0.00 sec)

Test [RW] 10:43:38 > select * from ty

+-- +

| | id | a |

Test [RW] 10:43:14 > update

D) within the same transaction in RR mode, the first query is the current read operation, and subsequent queries can view the latest data.

Test [RW] 11:07:23 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 11:07:26 > update ty set axi5 where id=2

Query OK, 1 row affected (0.00 sec)

Rows matched: 1

Test [RW] 11:07:31 > begin

Query OK, 0 rows affected (0.00 sec)

Test [RW] 11:07:33 > select * from ty where id=2 for update

+-- +

| | id | a |

Test [RW] 11:07:36 > insert into ty (amemb) values (6pc7)

Query OK, 1 row affected (0.00 sec)

Test [RW] 11:07:55 > commit

Query OK, 0 rows affected (0.00 sec)

Test [RW] 11:07:58 > select * from ty

+-- +

| | id | a |

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

Four current readings

Unlike consistent reading, the current read requires the use of select xx for update, or lock in share mode, to read the latest data and lock the accessed row (RC plus row lock, except RR plus gap lock unique key) regardless of whether another transaction commits or not, and if another transaction has acquired the relevant lock, the for update,lock in share mode statement continues to wait until the other transaction releases the lock and acquires the latest data.

Judging from the above tests, consistent snapshot reading in RR mode has more features (let's call it features). The RC schema itself supports non-repeatable readings and can query the latest committed data of other transactions. Based on the above tests, it is recommended that the business use RC mode as the transaction isolation level.

This is the answer to the question on how to understand the consistency of MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.