開発者がデータベースロックを詳細に理解する必要がある理由

1.ロックしますか？

1.1 ロックとは何ですか?

ロックの本当の意味は、鍵またはコードで開くことができる閉じた物体です。コンピューターのロックは、一般的に共有リソースへの同時アクセスを管理するために使用されます。たとえば、Java のクラスメイトによく知られている Lock と synchronized は、一般的なロックです。もちろん、データベースにはリソースへの同時アクセスを制御するためのロックもあり、これがデータベースとファイル システムの違いの 1 つです。

1.2 データベース ロックを理解する必要があるのはなぜですか?

一般的に言えば、一般的な開発者にとって、データベースを使用する際には、いくつかの DQL (選択) と DML (挿入、更新、削除) を知っていれば十分です。

Xiao Ming は、大学を卒業したばかりの Java 開発エンジニアで、インターネット企業に勤務しています。彼の通常の仕事は、PM の要件を満たすことです。もちろん、要件を満たす間も、Spring、Springmvc、Mybatis のフレームワークから逃れることはできません。そのため、一般的に言えば、彼はまだ SQL を手動で記述しています。より複雑な SQL に遭遇した場合は、Baidu にアクセスしてインターネットで検索します。トランザクションなどの重要な操作については、Xiao Ming は Spring Transactions を使用してデータベース トランザクションを管理します。データ量が少ないため、現時点では分散トランザクションは使用されていません。

シャオミンはここ数ヶ月、順調な生活を送っていました。ある日、シャオミンは要求を受けました。商店には割引設定項目と呼ばれる設定項目があり、1つ買うと1つ無料、1つ買うと2つ無料などのルールを設定できます。もちろん、これらの設定はバッチでバックエンドに送信されます。これは、ルールごとに一致させて、削除、追加、または変更されたかどうかを判断する必要があるため、問題を引き起こしました。これにより、バックエンドのロジックが複雑になりました。賢いシャオミンは、商店の設定を直接削除してから、すべてを追加する方法を考え出しました。シャオミンはすぐに開発を完了し、無事に発売しました。

最初の起動では何も問題はありませんでしたが、ログに mysql-insert-deadlock 例外が頻繁に表示されました。 Xiao Ming は経験が浅く、この種の問題に初めて遭遇したため、グループのベテランである Dahong に尋ねました。 Dahong はこの問題を見て自分のコードを調べ、いくつかのコマンドを出力し、いくつかのログを読み、すぐに問題を特定しました。彼は Xiao Ming に次のように説明しました。「これは、削除中にギャップ ロックが追加されるためですが、ギャップ ロックは互いに互換性があります。ただし、新しいデータを挿入するときに、挿入意図ロックがギャップ ロックによってブロックされ、両側でリソースが相互に占有され、デッドロックが発生します。」これを聞いてシャオミンは理解したようだが、ダホンはやることがたくさんあったので、いつも邪魔をするのは不便だったので、自分で考えることにした。仕事が終わった後、シャオミンはダホンの言ったことを思い出しました。ギャップロックとは何ですか、挿入意図ロックとは何ですか。開発者として、データベース用のSQLを書けるだけではだめだと思いました。そうでなければ、いくつかの難しい問題を解決することができません。考えた後、シャオミンはMySQLロックを学ぶための後戻りできない道を歩み始めました。

2. バイナリ

2.1MySQLアーキテクチャ

Xiao Ming は、この知識を解き明かすのを急いでいませんでした。彼はまず、MySQL アーキテクチャについて学びました。

Mysql は、接続プール コンポーネント、管理サービスおよびツール コンポーネント、SQL インターフェイス コンポーネント、クエリ アナライザ コンポーネント、オプティマイザ コンポーネント、バッファ コンポーネント、プラグイン ストレージ エンジン、および物理ファイルで構成されていることがわかります。

Xiao Ming は、MySQL のストレージ エンジンがプラグインの形で提供されていることを発見しました。MySQL には複数のストレージ エンジンがあり、各ストレージ エンジンには独自の特徴があります。次に、Xiao Ming はコマンドラインに次のように入力しました。

エンジンを表示 \G;

エンジンには実にたくさんの種類があることがわかりました。

次に、次のコマンドを入力して、現在のデータベースのデフォルト エンジンを表示します。

'%storage_engine%' のような変数を表示します。 

Xiao Ming は突然、データベースが InnoDB を使用していることに気付きました。彼は、学生時代に MyISAM というエンジンについて聞いたことを漠然と思い出しました。Xiao Ming は、この 2 つの違いは何だろうと考えました。彼はすぐにいくつかの情報を調べました。

Xiao Ming は InnoDB と MyISAM の違いを大体理解していました。InnoDB を使用していたので、Xiao Ming はそれについてあまり心配していませんでした。

2.2 トランザクションの分離

ロックを勉強する前に、シャオミンは学校で学んだデータベーストランザクション分離を思い出しました。実際、データベースにおけるロックの機能の 1 つは、トランザクション分離を実現することです。トランザクションの分離は、実際には、ダーティ リード、非反復リード、ファントム リードなどの問題を解決するために使用されます。

2.2.1 ダーティリード

トランザクションは、別のトランザクションによってコミットされていない更新されたデータを読み取ります。 それはどういう意味ですか？

トランザクション A と B では、トランザクション A がそれぞれ時点 2 と 4 でユーザー テーブルの id=1 のデータを照会しましたが、時点 3 でトランザクション B がそれを変更したため、時点 4 でのトランザクション A の照会結果は、実際にはトランザクション B によって変更された結果になりました。これにより、データベース内の分離が破壊されます。

2.2.2 繰り返し不可能な読み取り

同じトランザクションで、同じデータを複数回読み取ると、異なる結果が返される場合があります。ダーティ リードとは異なり、ここで読み取られるデータはコミットされたデータです。

トランザクション B で送信された操作はトランザクション A の 2 番目のクエリの前に行われますが、トランザクション B の更新結果が引き続き読み取られるため、トランザクションの分離も破壊されます。

2.2.3 ファントムリード

あるトランザクションは、別のトランザクションによってコミットされた挿入データを読み取ります。

トランザクション A では、ID が 1 より大きいクエリが 2 つ実行されました。最初のクエリでは、結果にデータはありませんでした。ただし、トランザクション B が ID=2 のデータを挿入したため、トランザクション A は 2 番目のクエリでトランザクション B で挿入されたデータを見つけることができました。

トランザクションの分離:

Xiao Ming は、情報収集の過程で、InnoDB が他のデータベースとは少し違うという情報があることに気付きました。InnoDB の繰り返し読み取りは、実際にファントム読み取りを解決できます。Xiao Ming は考えました。「この InnoDB は本当にすごい。どのように動作するのか、もっと詳しく調べてみよう。」

2.3 InnoDB ロックの種類

Xiao Ming はまず、Mysql の一般的なロックの種類を理解します。

2.3.1 SまたはX

InnoDb には 2 つの標準的な行レベル ロックが実装されており、これらは単純に 2 つの読み取り/書き込みロックとして考えることができます。

• S-共有ロック: 読み取りロックとも呼ばれます。他のトランザクションは共有ロックを追加し続けることができますが、排他ロックを追加し続けることはできません。
• X 排他ロック: 書き込みロックとも呼ばれます。書き込みロックが追加されると、他のトランザクションはそれをロックできなくなります。

互換性: トランザクション A が特定の行に対して特定のロックを取得した後、トランザクション B も同じ行に対して特定のロックを取得しようとすることを意味します。すぐに取得できる場合はロック互換性と呼ばれ、そうでない場合は競合と呼ばれます。

垂直軸は既存のロックを表し、水平軸は取得しようとしたロックを表します。

2.3.2 意図ロック

インテンション ロックは、InnoDB のテーブル レベルのロックです。名前が示すように、トランザクションが取得したいものを表現するために使用されます。インテンションロックは次のように分類されます。

• 意図的な共有ロック: トランザクションがテーブル内の特定の行に対して共有ロックを取得することを表します。
• 意図的な排他ロック: トランザクションがテーブル内の特定の行に対して排他ロックを取得することを表します。

このロックは何の用途があるのでしょうか?このロックはなぜ必要なのですか? まず、そのようなロックがない場合、このテーブルにテーブル ロックを追加する場合、各行をトラバースして行ロックがあるかどうかを確認するのが一般的な方法です。これはあまりにも非効率的です。ただし、意図的なロックがある場合は、意図的なロックがあるかどうかを判断するだけでよく、各行を 1 つずつスキャンする必要はありません。

InnoDB は行レベルのロックをサポートしているため、InnoDB ロックの互換性は次のように拡張できます。

2.3.3 自動増分ロック

自動インクリメント ロックは、同時挿入のパフォーマンスを向上させる特別なテーブル ロック メカニズムです。このロックにはいくつかの機能があります:

• ロックはトランザクションの実行時ではなく、SQL の実行時に解除されます。
• 挿入...選択の場合、大量のデータを挿入すると、別のトランザクションの実行がブロックされるため、挿入のパフォーマンスに影響します。
• 自動インクリメント アルゴリズムは設定可能です。

MySQL バージョン 5.1.2 以降では、多くの最適化が行われており、ロックを増やす方法はさまざまなモードに応じて調整できます。 Xiao Ming はこれを見て MySQL を開き、バージョンが 5.7 であることを確認したので、次のステートメントを入力して現在のロック モードを取得しました。

mysql> 'innodb_autoinc_lock_mode' のような変数を表示します。

+--------------------------+-------+

| 変数名 | 値 |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 2 |

+--------------------------+-------+

セット内の1行（0.01秒）

MySQL では、innodbautoinclock_mode には 0、1、2 の 3 つの構成モードがあり、それぞれ「従来モード」、「連続モード」、「インターリーブ モード」に対応します。

• 従来モード: これは上部で使用するテーブル ロックです。
• 連続モード: 挿入時に行数を判別できる場合はミューテックスを使用し、行数を判別できない場合はテーブル ロックを使用します。
• インターリーブ モード: すべてミューテックスを使用します。なぜインターリーブ モードと呼ばれるのでしょうか。バッチ挿入時に自動インクリメント値が連続しない可能性があります。もちろん、一般的に言えば、自動インクリメント値の連続性を重視しない場合は、通常、最高のパフォーマンスを発揮するこのモードを選択します。

2.4InnoDB ロックアルゴリズム

Xiao Ming は InnoDB のロックの種類について学習しましたが、これらのロックの使用方法は依然としてロック アルゴリズムによって異なります。

2.4.1 レコードロック

レコード ロックはレコードをロックします。ここで説明する必要があるのは、ここでロックされているのは実際のデータ レコードではなく、インデックス レコードであるということです。

• 非主キー インデックスがロックされている場合は、自身のインデックスがロックされ、次に主キーがロックされます。
• テーブルにインデックスがない場合 (主キーがない場合を含む)、非表示の主キー インデックスがロックに使用されます。
• ロックする列にインデックスがない場合、テーブル内のすべてのレコードがロックされます。

2.4.2 ギャップロック

名前が示すように、ギャップ ロックはギャップをロックしますが、レコードはロックしません。ギャップをロックするということは、ある範囲をロックするということです。ギャップロックはギャップロックとも呼ばれます。他のギャップロックをブロックすることはありませんが、ギャップロックの挿入をブロックします。これはファントムリードを防ぐ鍵でもあります。

2.4.3 次のキーロック

このロックは本質的にはレコード ロックとギャップ ロックを組み合わせたものです。 RR 分離レベル (InnoDB のデフォルト) では、Innodb は行スキャン ロックにこのアルゴリズムを使用しますが、クエリ スキャンに一意のインデックスがある場合は、レコード ロックのみを使用するようになります。なぜでしょうか? ユニーク インデックスは行数を判別できますが、他のインデックスは行数を判別できないためです。このインデックスのデータが他のトランザクションで再度追加され、ファントム リードが発生する可能性があります。

これは、MySQL が RR レベルでファントム リード (PHANTOM READ) を解決できる理由も説明しています。

2.4.4 挿入意図ロック

挿入意図ロックのMySQL公式説明:

挿入意図ロックは、行の挿入前に INSERT 操作によって設定されるギャップ ロックの一種です。このロックは、同じインデックス ギャップに挿入する複数のトランザクションが、ギャップ内の同じ位置に挿入していない場合は、互いに待機する必要がないように、挿入意図を通知します。値が 4 と 7 のインデックス レコードがあるとします。それぞれ 5 と 6 の値を挿入しようとする個別のトランザクションは、挿入された行の排他ロックを取得する前に、挿入意図ロックを使用して 4 と 7 の間のギャップをロックしますが、行が競合しないため、互いにブロックすることはありません。

挿入時に挿入意図ロックが生成されていることがわかります。複数のトランザクションが同時に同じインデックスギャップに異なるデータを書き込む場合、他のトランザクションが完了するのを待つ必要がなく、ロック待ちは発生しません。キー値 4 と 7 を含むレコード インデックスがあり、異なるトランザクションがそれぞれ 5 と 6 を挿入するとします。各トランザクションは 4 と 7 の間に追加された挿入意図ロックを生成し、挿入された行の排他ロックを取得しますが、データ行が競合しないため、相互にロックされることはありません。

ここで注意すべきは、ギャップロックがあると意図ロックの挿入がブロックされるということです。

2.5 MVCC

MVCC、マルチバージョン同時実行制御テクノロジー。 InnoDB では、作成バージョン番号と削除バージョン番号を記録するために、レコードの各行の後に 2 つの隠し列が追加されます。バージョン番号と行ロックにより、データベース システムの同時パフォーマンスが向上します。

MVCC では、読み取り操作は次の 2 つのタイプに分けられます。

• スナップショット読み取り: 履歴データを読み取り、単純な選択ステートメントを使用し、ロックなしで、MVCC を使用して繰り返し読み取りを実現し、MVCC メカニズムを使用してコミットされたデータを元に戻して読み取ります。したがって、その読み取りは非ブロッキングです。
• 現在の読み取り: 更新、挿入、削除、選択など、更新のためのロックを必要とするステートメントはすべて現在の読み取りです。

RR 分離レベルでのスナップショット読み取りの場合、スナップショットの作成時間は、開始トランザクションが開始された時点ではなく、最初の SELECT ステートメントがスナップショットの作成時点として使用される時点です。後続の選択では、現在の時点のスナップショット値が読み取られます。

RC 分離レベルでは、スナップショットの読み取りごとに新しいスナップショットが作成されます。

具体的な原則としては、各行に 2 つの隠しフィールドがあり、1 つは現在のトランザクションを記録するためのフィールド、もう 1 つは Undolog を指すロールバックを記録するためのフィールドです。記録用のスペースを割り当てることなく、undolog を使用して以前のスナップショットを読み取ることができます。

3. ロック分析

Xiao Ming は MySQL ロックに関する基本的な知識を多く学んだので、実験を行うためにテーブルを作成することにしました。まず、単純なユーザー テーブルを作成します。

テーブル `user` を作成します (

`id` int(11) 符号なし NOT NULL AUTO_INCREMENT,

`name` varchar(11) 文字セット utf8mb4 デフォルト NULL,

`comment` varchar(11) 文字セット utf8 デフォルト NULL,

主キー (`id`)、

キー `index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=0 デフォルト CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

次に、いくつかの実験データを挿入しました。

ユーザー選択 20,333,333 を挿入します。

ユーザー選択 25,555,555 を挿入します。

ユーザー選択 20,999,999 を挿入します。

データベーストランザクション分離はRRを選択する

3.1 実験1

Xiao Mingは2つのトランザクションを開始し、実験1を実施しました。

Xiao Ming は 2 つのトランザクションを開始し、上記のステートメントを入力しましたが、トランザクション B が実際にタイムアウトしたことを発見しました。Xiao Ming は確認し、行名 = 555 が明らかにロックされていたことを発見しました。では、名前 = 556 を挿入しようとしたときにブロックされたのはなぜでしょうか。そこで、Xiao Ming はコマンドラインを開いて次のように入力しました。

information_schema.INNODB_LOCKS から * を選択します

トランザクション A で 555 に Next-key ロックが追加されていることがわかります。トランザクション B が挿入すると、挿入意図ロックが最初に挿入されるため、次の結論が導き出されます。

ギャップ ロックと挿入意図ロックの競合により、トランザクション B がブロックされていることがわかります。

3.2 実験2

Xiao Ming は、上記のクエリ条件で通常の非一意のインデックスが使用されていることに気づいたので、主キー インデックスを試しました。

トランザクション B はブロックされていないことがわかりました。何が起こっているのでしょうか? Xiao Ming は少し困惑しています。実験 1 のルーチンによると、25 から 30 の間にギャップ ロックがあるため、ブロックされるはずです。そこで、Xiao Ming はコマンド ラインを再度使用し、X レコード ロックのみが追加されたことを確認しました。ユニーク インデックスによってレコード ロックのレベルが下がることが判明しました。その理由は、ユニークでないインデックスと次のキー ロックの組み合わせでは行の正確な数を判断できないため、クエリ中に他のトランザクションがこのインデックスのデータを再度追加し、分離が破壊される可能性があり、これがファントム リードとなるためです。一意のインデックスは唯一のデータ行を指定するため、ファントム読み取りを解決するためにギャップ ロックを追加する必要はありません。

3.3 実験3

上記では、主キー インデックスと非一意インデックスがテストされています。インデックスのない別のフィールドがあります。ロックされている場合はどうなるでしょうか。

実際、インデックス付けされていないデータを使用すると、すべてのクラスター化インデックスに次のキー ロックが追加されます。

そのため、通常の開発では、クエリ条件にインデックスがない場合、一貫性のある読み取り、つまりロックされた読み取りを実行する必要があり、その結果、テーブル全体にインデックスが追加され、他のすべてのトランザクションがブロックされ、データベースは基本的に使用できない状態になります。

4. 事故に戻る

4.1 デッドロック

シャオミンは実験を終えて、ようやく基本的なロックルーチンを理解しましたが、以前オンラインで発生したデッドロックとは何だったのでしょうか?

デッドロック: 実行中にリソースの競合により 2 つ以上のトランザクションが互いに待機する現象を指します。つまり、デッドロックは待機がある場合にのみ発生します。デッドロックは、トランザクションをロールバックするなど、待機をなくすことで解決できます。

デッドロックを解決するには 2 つの方法があります。

• タイムアウト待ち: タイムアウトを待ってトランザクションがロールバックされると、別のトランザクションを実行できます。ただし、これは非効率的であり、待機時間が発生します。もう 1 つの問題は、トランザクションの重みが大きく、大量のデータを更新したにもかかわらずロールバックされると、リソースの無駄につながることです。
• 待機グラフ: 待機グラフは、トランザクション間の待機関係を記述するために使用されます。このグラフにループが表示される場合は、次のようになります。

ロールバックが発生した場合、InnoDB は通常、重みが小さいトランザクション、つまり UNDO 値が小さいトランザクションをロールバックすることを選択します。

4.2 オンラインの問題

Xiao Ming は必要な基本スキルをすべて備えているので、ローカル テーブルでこの問題を再現し始めます。

トランザクション A はロールバックされ、トランザクション B は正常に実行されたことがわかります。 それぞれの時点で何が起こったのでしょうか?

時点 2: トランザクション A は、名前 = '777' のデータを削除します。インデックス 777 に次のキー ロックを追加する必要がありますが、存在しません。そのため、555 と 999 の間にギャップ ロックのみが追加されます。同様に、トランザクション B も 555 と 999 の間にギャップ ロックを追加します。ギャップロックは互いに互換性があります。

時点 3: トランザクション A は挿入操作を実行し、最初にインテンション ロックを挿入します。ただし、555 ～ 999 の間にギャップ ロックがあります。挿入インテンション ロックとギャップ ロックの競合により、トランザクション A はブロックされ、トランザクション B がギャップ ロックを解除するのを待機します。トランザクション B も同様で、トランザクション A がギャップ ロックを解除するのを待機します。つまり、A->B、B->A のループが待機していることになります。

時点 4: トランザクション マネージャーはトランザクション A のロールバックを選択し、トランザクション B の挿入操作が正常に実行されます。

4.3 バグ修正

Xiao Ming はついにこの問題を発見しました。これはギャップ ロックが原因です。今度はこの問題を解決する必要があります。この問題の原因はギャップ ロックなので、これを解消しましょう。

• 解決策 1: 分離レベルを RC にダウングレードします。RC レベルではギャップ ロックは追加されないため、問題は発生しません。ただし、RC レベルではファントム リードが発生し、コミットされた読み取りによって分離の問題が破壊されるため、この解決策は実行可能ではありません。
• 解決策 2: 分離レベルをシリアル化可能にアップグレードします。テストの結果、Xiao Ming はこの問題は発生しないことがわかりました。ただし、シリアル化可能レベルではパフォーマンスが低下し、ロック待機が増えるため、これも考慮されていません。
• 解決策 3: コード ロジックを変更します。直接削除しないでください。代わりに、ビジネス ロジックでどのデータが更新、削除、追加されるかを決定します。この作業負荷は少し大きくなります。Xiao Ming は、これらの複雑な処理を回避するためにこの直接削除ロジックを作成したため、この解決策は現時点では考慮されていません。
• 解決策 4: コード ロジックの変更が少なくなります。削除する前に、スナップショット (ロックなし) を介してクエリを実行できます。クエリに結果がない場合は、直接挿入します。主キーを介して削除が行われる場合、セクション 3 の前の実験 2 では、一意のインデックスがレコード ロックにダウングレードされるため、ギャップ ロックは発生しません。

検討の末、シャオミンは4番目の選択肢を選び、すぐに修理を行い、オンラインで観察と検証を行いました。すると、バグはもう発生しないことがわかりました。これでシャオミンはようやくぐっすり眠れるようになりました。

4.4 デッドロックを防ぐ方法

シャオミンは基礎学習と日常経験を通じて以下の点をまとめました。

• テーブルと行は固定された順序でアクセスされます。クロスアクセスにより、トランザクション待機ループが発生する可能性が高くなります。
• 大規模なトランザクションは避けてください。占有するリソース ロックが増えるほど、デッドロックが発生する可能性が高くなります。小さなタスクに分割することをお勧めします。
• 分離レベルを下げます。ビジネスで許可されている場合 (上記の 4.3 で分析したように、一部のビジネスでは許可されていません)、分離レベルを下げることも良い選択です。たとえば、分離レベルを RR から RC に調整すると、ギャップ ロックによって発生する多くのデッドロックを回避できます。
• テーブルに適切なインデックスを追加します。デッドロックの可能性が急激に高まるため、インデックスがない場合にはテーブル ロックが発生しないようにします。

やっと

スペースが限られているため、十分に紹介できないことがたくさんあります。ご興味があれば、「MySQL Technology Insider - InnoDB Engine」の第 6 章と、Master He の MySQL Lock Processing Analysis をお読みください。作者のレベルには限界がありますので、間違いがあれば指摘してください。

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。