MySQL は InnoDB の MVCC 原則を要約しています。-mysql チュートリアル-php.cn

この記事では、mysql に関する関連知識を提供します。主に InnoDB の MVCC 原理に関連する問題を紹介します。MVCC は、主にデータベースの同時実行性を向上させるためのマルチバージョン同時実行制御です。パフォーマンスを見てみましょう。ぜひご覧ください。皆さんのお役に立てれば幸いです。

MySQL は InnoDB の MVCC 原則を要約しています。

推奨される学習: mysql ビデオチュートリアル

MVCC の正式名は Multi-Version Concurrency Control で、複数バージョンの同時実行制御です。、主にデータベースの同時実行パフォーマンスを向上させるために使用されます。同じデータ行に対して読み取りまたは書き込みリクエストが発生すると、そのリクエストはロックされブロックされます。ただし、MVCC は、読み取り/書き込み要求の処理に優れた方法を使用しているため、読み取り/書き込み要求の競合が発生したときにロックが発生しません。この読み取りは、現在の読み取りではなく、スナップショットの読み取りを参照します。現在の読み取りはロック操作であり、悲観的ロックです。では、ロックせずに読み取りと書き込みを実現するにはどうすればよいでしょうか? スナップショットの読み取りと現在の読み取りは何を意味するのでしょうか?これについては後で皆さんも学びます。

MySQL は、REPEATABLE READ 分離レベルでファントムリードの問題を大幅に回避できます。MySQL はどのようにこれを行うのでしょうか?

バージョンチェーン

InnoDB ストレージエンジンを使用するテーブルの場合、そのクラスター化インデックスレコードには 2 つの必要な隠し列 (row_id は必要ありません。row_id 列) が含まれることがわかっています。作成するテーブルに主キーまたは NULL 以外の UNIQUE キーがある場合は含まれません):

trx_id: トランザクションが特定のクラスターになるたびに、インデックスレコードが変更されると、トランザクションのトランザクション ID は、trx_id 非表示列に割り当てられます。
roll_pointer: クラスター化インデックスレコードが変更されるたびに、古いバージョンが UNDO ログに書き込まれ、この非表示列はポインターと同等になり、次の目的で使用できます。レコードが変更される前の情報を検索します。

この問題を説明するために、デモテーブルを作成します:

CREATE TABLE `teacher` (
  `number` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `domain` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

ログイン後にコピー

次に、このテーブルにデータを挿入します:

mysql> insert into teacher values(1, 'J', 'Java');Query OK, 1 row affected (0.01 sec)

ログイン後にコピー

現在のデータこれです。

mysql> select * from teacher;
+--------+------+--------+
| number | name | domain |
+--------+------+--------+
|      1 | J    | Java   |
+--------+------+--------+
1 row in set (0.00 sec)

ログイン後にコピー

レコードを挿入するトランザクション ID が 60 であるとすると、この時点のレコードの図は次のとおりです。

MySQL は InnoDB の MVCC 原則を要約しています。

トランザクション ID が 80 と 120 の次の 2 つの A トランザクションがこのレコードに対して UPDATE 操作を実行するとします。操作プロセスは次のとおりです:

##begin教師セット名 = 'S' 番号 = 1 を更新します;#更新教師セット名 = 'T' 番号 = 1;##コミット##教師セット名 = 'K' を更新 (数値 = 1);#教師セット名 = を更新'F' 番号 = 1;##commit

Trx80

Trx120

開始

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

MySQL は InnoDB の MVCC 原則を要約しています。

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。于是可以利用这个记录的版本链来控制并发事务访问相同记录的行为，那么这种机制就被称之为多版本并发控制(Mulit-Version Concurrency Control MVCC)。

ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

对于使用SERIALIZABLE隔离级别的事务来说，InnoDB使用加锁的方式来访问记录。

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：READ COMMITTED和REPEATABLE READ隔离级别在不可重复读和幻读上的区别，这两种隔离级别关键是需要判断一下版本链中的哪个版本是当前事务可见的。

为此，InnoDB提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
creator_trx_id：表示生成该ReadView的事务的事务id。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id
如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

我们还是以表teacher为例，假设现在表teacher中只有一条由事务id为60的事务插入的一条记录，接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

READ COMMITTED每次读取数据前都生成一个ReadView

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
set session transaction isolation level read committed;
begin
update teacher set name='S' where number=1;
update teacher set name='T' where number=1;

ログイン後にコピー

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL は InnoDB の MVCC 原則を要約しています。

假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

set session transaction isolation level read committed;
# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

ログイン後にコピー

这个SELECE1的执行过程如下：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

set session transaction isolation level read committed;
# Transaction 120
begin
update teacher set name='K' where number=1;
update teacher set name='F' where number=1;

ログイン後にコピー

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL は InnoDB の MVCC 原則を要約しています。

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number 为1的记录，如下：

# 使用READ COMMITTED隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'T'

ログイン後にコピー

这个SELECE2 的执行过程如下：

在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[120]（事务id为80的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为120，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’F’，该版本的trx_id值为120，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name 的内容是’K’，该版本的trx_id值为120，也在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’T’，该版本的trx_id值为80，小于ReadView中的min_trx_id值120，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’‘T’'的记录。

以此类推，如果之后事务id为120的记录也提交了，再次在使用READCOMMITTED隔离级别的事务中查询表teacher中number值为1的记录时，得到的结果就是’F’了，具体流程我们就不分析了。

总结一下就是：使用READCOMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

假设现在系统里有两个事务id分别为80、120的事务在执行：

# Transaction 80
begin
update teacher set name='S' where number=1;
update teacher set name='T' where number=1;

ログイン後にコピー

此刻，表teacher中number为1的记录得到的版本链表如下所示：

MySQL は InnoDB の MVCC 原則を要約しています。

假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

ログイン後にコピー

这个SELECE1的执行过程如下（与READ COMMITTED的过程一致）：

在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，最新版本的列name的内容是’T’，该版本的trx_id值为80，在m_ids列表内，根据步骤4不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’S’，该版本的trx_id值也为80，也在m_ids列表内，根据步骤4也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’J’，该版本的trx_id值为60，小于ReadView 中的min_trx_id值，根据步骤2判断这个版本是符合要求的。

之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

# Transaction 80
begin
update teacher set name='K' where number=1;
update teacher set name='F' where number=1;

ログイン後にコピー

此刻，表teacher 中number为1的记录的版本链就长这样：

MySQL は InnoDB の MVCC 原則を要約しています。

然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
begin;
# SELECE1：Transaction 80、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'
# SELECE2：Transaction 80提交、120未提交
SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'J'

ログイン後にコピー

这个SELECE2的执行过程如下：

現在のトランザクションの分離レベルは REPEATABLE READ であり、以前に SELECE1 を実行したときに ReadView が生成されているため、今回は以前の ReadView が直接再利用されます。以前の ReadView の m_ids リストの内容は次のとおりです。 [80, 120]、min_trx_id は 80、max_trx_id は 121、creator_trx_id は 0 です。
次に、バージョンチェーンから表示されているレコードを選択します。図からわかるように、最新バージョンの列名の内容は「F」です。このバージョンの trx_id 値は 120 です。ステップ 4 によると、list は可視性要件を満たしていないため、roll_pointer に基づいて次のバージョンにジャンプします。
次のバージョンの列名の内容は 'K' です。このバージョンの trx_id 値は 120 で、これは m_ids リストにもあります。手順 4 によると、可視性の要件を満たしていません。そして、roll_pointer に従って次のバージョンにジャンプします。
次のバージョンの列名の内容は 'T' です。このバージョンの trx_id 値は 80 で、m_ids リストにもあります。手順 4 によると、可視性要件を満たしていません。そして、roll_pointer に従って次のバージョンにジャンプします。
次のバージョンの列名の内容は「S」です。このバージョンの trx_id 値は 80 で、m_ids リストにもあります。手順 4 によると、可視性の要件を満たしていません。そして、roll_pointer に従って次のバージョンにジャンプします。
次のバージョンの列名の内容は「J」です。このバージョンの trx_id 値は 60 で、ReadView の min_trx_id 値 80 よりも小さく、このバージョンを生成したトランザクションが現在のトランザクションが ReadView を生成する前に送信されました。したがって、このバージョンは要件を満たしており、最終的にユーザーに返されるバージョンは、列名 ''J'' のレコードです。

つまり、2 つの SELECT クエリによって得られた結果が繰り返され、記録された列の値はすべて ''J'''' になります。これが意味します。繰り返し読める読書。

後でトランザクション ID 120 のレコードを送信し、REPEATABLE READ 分離レベルを使用したばかりのトランザクションで番号 1 のレコードの検索を続けると、結果は依然として「J」になります。実行プロセスを自分で分析できます。

MVCC でのファントム読み取り現象とファントム読み取りソリューション

MVCC が REPEATABLE READ 分離レベルでの非反復読み取り問題を解決できることはすでにわかっているため、ファントム読み取り毛織物？ MVCCはどのように解決されるのでしょうか?ファントム読み取りとは、トランザクションが同じ条件に従ってレコードを複数回読み取ることです。最後の読み取りでは、以前に読み取られていないレコードが読み取られます。このレコードは、別のトランザクションによって追加された新しいレコードに由来します。

これについて考えると、REPEATABLE READ 分離レベルのトランザクション T1 が最初に特定の検索条件に基づいて複数のレコードを読み取り、次にトランザクション T2 が対応する検索条件を満たすレコードを挿入して送信し、その後トランザクションが実行されます。 T1 次に、同じ検索基準に基づいてクエリを実行します。結果はどうなるでしょうか？ ReadView の比較ルールによると:

トランザクション T2 がトランザクション T1 より前にオープンされているかどうかに関係なく、トランザクション T1 は T2 の送信を確認できません。上記で紹介したバージョンチェーン、ReadView、可視性の判定ルールに従ってご自身で解析してください。

ただし、REPEATABLE READ 分離レベルにある InnoDB の MVCC は、ファントム読み取りを完全に禁止するのではなく、ファントム読み取りを大幅に回避できます。どうしたの？次の状況を見てみましょう:

#select * from Teacher wherenumber=30; データなしbegin;教師値に挿入(30, 'X', 'Java');教師セットのドメイン = 'MQ' を更新 (数値 = 30);教師から * を選択数値 = 30; データがあります#

さて、何が起こっているのでしょうか？トランザクション T1 には明らかにファントムリード現象があります。 REPEATABLE READ 分離レベルでは、T1 は通常の SELECT ステートメントを初めて実行するときに ReadView を生成し、その後 T2 が新しいレコードを教師テーブルに挿入して送信します。 ReadView は、T1 が新しく挿入されたレコードを変更する UPDATE または DELETE ステートメントを実行するのを防ぐことはできません (T2 はすでに送信されているため、レコードを変更してもブロックは発生しません)。ただし、この方法では、この新しいレコードの trx_id 非表示列の値がbe T1のトランザクションIDになります。その後、T1 は通常の SELECT ステートメントを使用してこのレコードをクエリするときにこのレコードを確認し、このレコードをクライアントに返すことができます。このような特殊な現象が存在するため、MVCC ではファントムリードを完全に禁止することはできないとも考えられます。

MVCC の概要

上記の説明から、いわゆる MVCC (Multi-Version ConcurrencyControl、マルチバージョン同時実行制御) が READ COMMITTD と REPEATABLE READ の使用を指すことがわかります。この分離レベルのトランザクションは、通常の SELECT 操作の実行時に記録されたバージョンチェーンにアクセスするため、異なるトランザクションの読み取り/書き込みおよび書き込み/読み取り操作を同時に実行できるため、システムのパフォーマンスが向上します。

READ COMMITTD と REPEATABLE READ の 2 つの分離レベルの大きな違いは、ReadView を生成するタイミングが異なることです。READ COMMITTD は通常の SELECT 操作の前に ReadView を生成しますが、REPEATABLE READ は ReadView のみを生成します。通常の SELECT 操作の後に ReadView を生成してから通常の SELECT 操作を実行し、この ReadView を後続のクエリ操作で再利用するだけで、基本的にファントム読み取りの現象が回避されます。

主キーを更新する DELETE ステートメントまたは UPDATE ステートメントを実行しても、対応するレコードがページからすぐに完全に削除されるわけではなく、いわゆる削除マーク操作が実行されると前に述べました。レコードにマークを付けるだけと同じで、主に MVCC に使用されるフラグビットを削除します。また、いわゆる MVCC は、通常の SEELCT クエリを実行する場合にのみ有効になります。これまで見てきた SELECT ステートメントはすべて通常のクエリです。特殊なクエリとは何かについては、後ほど説明します。

推奨学習: mysql ビデオチュートリアル