MySQL の同時実行性の問題と解決策の分析

T1

始める;

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

始める;

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

T2

更新のために、ID=3 のユーザーから * を選択します。

+----+------+------+
| ID | 名前 | 年齢 |
+----+------+------+
| 3 | 太陽 | 20 |
+----+------+------+
セット内の 1 行 (0.00 秒)

更新のために、ID=4 のユーザーから * を選択します。

+----+------+------+
| ID | 名前 | 年齢 |
+----+------+------+
| 4 | 周 | 21 |
+----+------+------+
セット内の 1 行 (0.00 秒)

information_schema.INNODB_TRX から * を選択します。

メタデータ データベースの innodb トランザクション テーブルをクエリすると、現在実行中のトランザクションの数が 2 つ (トランザクション 1 とトランザクション 2) であることが監視されます。

T3

ユーザーセット名を 'haha' にして ID を 4 にして更新します。

id=4 のレコードはトランザクション 2 によってロックされているため、ステートメントはブロックされます。

現在実行中のトランザクションの数は 2 であると監視されます。 T4 ブロック状態

ユーザーを更新し、name='hehe' を id=3 に設定します。

エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

id=3 のレコードはトランザクション 1 によってロックされており、このトランザクションは id=4 のレコードの行ロックを保持しています。この時点で、InnoDB ストレージ エンジンはデッドロックを検出し、このトランザクションはロールバックされます。

トランザクション 2 はロールバックされましたが、トランザクション 1 はまだ実行中です。現在実行中のトランザクションの数は 1 です。 T5

クエリは正常、1 行が影響を受けました (20.91 秒)
一致した行: 1 変更された行: 1 警告: 0

トランザクション 2 はロールバックされるため、元々ブロックされていた更新ステートメントは引き続き実行されます。

監視対象の実行中のトランザクションの数は 1 です。 T6

専念;

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

トランザクション 1 はコミットされ、トランザクション 2 はロールバックされ、現在実行中のトランザクションの数は 0 です。

これは単純なデッドロックのシナリオです。トランザクション 1 とトランザクション 2 は、お互いがロックを解除するのを待機しています。InnoDB ストレージ エンジンはデッドロックを検出し、トランザクション 2 をロールバックします。つまり、トランザクション 1 はトランザクション B のロックを待機する必要がなくなり、実行を継続できます。では、InnoDB ストレージ エンジンはどのようにしてデッドロックを検出するのでしょうか?この問題を理解するために、まずこの時点での InnoDB の状態を確認しましょう。

エンジン InnoDB ステータスを表示\G

------------------------
最近検出されたデッドロック
------------------------
2018-01-14 12:17:13 0x70000f1cc000
*** （１）取引：
トランザクション 5120、アクティブ 17 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 3 つのロック構造体、ヒープ サイズ 1136、2 つの行ロック
MySQL スレッド ID 10、OS スレッド ハンドル 123145556967424、クエリ ID 2764、ローカルホスト ルート更新中
ユーザーセット名を「haha」にして、ID を 4 に変更します。
*** (1) このロックが許可されるのを待機しています:
レコード ロック スペース ID 94 ページ番号 3 n ビット 80 インデックス PRIMARY テーブル `test`.`user` の trx ID 5120 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 5 物理レコード: n_fields 5; コンパクト フォーマット; 情報ビット 0
0: 長さ 4; 16 進数 80000004; 昇順 ;;
1: 長さ 6; 16 進数 0000000013fa; 昇順 ;;
2: 長さ 7; 16 進数 520000060129a6; asc R ) ;;
3: 長さ 4; 16 進数 68616861; asc haha​​;;
4: 長さ 4; 16 進数 80000015; 昇順 ;;

*** （２）取引：
トランザクション 5121、アクティブ 12 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
3 つのロック構造体、ヒープ サイズ 1136、2 つの行ロック
MySQL スレッド ID 11、OS スレッド ハンドル 123145555853312、クエリ ID 2765 ローカルホスト ルート更新中
ユーザーセット名を「hehe」に更新します。IDは3です。
*** (2) ロックを保持する:
レコード ロック スペース ID 94 ページ番号 3 n ビット 80 インデックス PRIMARY テーブル `test`.`user` の trx ID 5121 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 5 物理レコード: n_fields 5; コンパクト フォーマット; 情報ビット 0
0: 長さ 4; 16 進数 80000004; 昇順 ;;
1: 長さ 6; 16 進数 0000000013fa; 昇順 ;;
2: 長さ 7; 16 進数 520000060129a6; asc R ) ;;
3: 長さ 4; 16 進数 68616861; asc haha​​;;
4: 長さ 4; 16 進数 80000015; 昇順 ;;

*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 94 ページ番号 3 n ビット 80 インデックス PRIMARY テーブル `test`.`user` の trx ID 5121 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 7 物理レコード: n_fields 5、コンパクト フォーマット、情報ビット 0
0: 長さ 4; 16 進数 80000003; 昇順 ;;
1: 長さ 6; 16 進数 0000000013fe; asc ;;
2: 長さ 7; 16 進数 5500000156012f; asc UV /;;
3: 長さ 4; 16 進数 68656865; asc へへ;;
4: 長さ4; 16進数80000014; 昇順;;

*** トランザクションをロールバックします (2)

InnoDB の状態を示す指標は多数あります。ここではデッドロック関連の情報を傍受します。InnoDB は最近のデッドロック情報を出力できることがわかります。実際、多くのデッドロック監視ツールもこの機能に基づいて開発されています。

デッドロック情報には、ロックを待機している 2 つのトランザクションに関する情報が表示されます (トランザクション 1 は青、トランザクション 2 は緑)。「WAITING FOR THIS LOCK TO BE GRANTED」および「HOLDS THE LOCK(S)」に特に注意してください。

WAITING FOR THIS LOCK TO BE GRANTED は、現在のトランザクションが待機しているロック情報を示します。出力から、トランザクション 1 はヒープ番号 5 の行ロックを待機しており、トランザクション 2 はヒープ番号 7 の行ロックを待機していることがわかります。

HOLDS THE LOCK(S): 現在のトランザクションによって保持されているロック情報を示します。出力から、トランザクション 2 がヒープ番号 5 のロックを保持していることがわかります。

出力結果から、InnoDB が最終的にトランザクション 2 をロールバックしたことがわかります。

では、InnoDB はどのようにしてデッドロックを検出するのでしょうか?

考えられる最も簡単な方法は、トランザクションがロックを待機している場合、待機時間が設定されたしきい値を超えるとトランザクション操作が失敗し、複数のトランザクションが互いに長時間待機する状況を回避することです。パラメータ innodb_lock_wait_timeout は、ロック待機時間を設定するために使用されます。

この方法を使用すると、デッドロックの解決に時間がかかります (つまり、待機時間が innodb_lock_wait_timeout で設定されたしきい値を超えます)。この方法はやや受動的であり、システム パフォーマンスに影響します。InnoDB ストレージ エンジンは、デッドロックの問題を解決するためのより優れたアルゴリズムである wait-for graph アルゴリズムを提供します。簡単に言うと、複数のトランザクションが互いに待機し始めると、待機グラフ アルゴリズムが有効になります。アルゴリズムによってデッドロックが発生したと判断されると、トランザクションの 1 つが直ちにロールバックされ、デッドロックが解決されます。この方法の利点は、より積極的な検査と待ち時間の短縮です。

待機グラフ アルゴリズムの基本原理は次のとおりです。

理解しやすくするために、デッドロックを 4 台の車が互いにブロックしているシナリオとして考えてみましょう。

4 台の車は 4 つのトランザクションと見なされ、お互いのロック待ちとなり、デッドロックが発生します。待機グラフアルゴリズムの原理は、トランザクションをノードとして扱い、トランザクション間のロック待機関係を有向エッジで表現することです。たとえば、トランザクション A がトランザクション B のロック待ちをする場合、ノード A からノード B へ有向エッジが引かれます。このように、A、B、C、D で構成される有向グラフがサイクルを形成すると、デッドロックと判断されます。これが wait-for graph アルゴリズムの基本原理です。

要約:

1. ビジネス開発における行き詰まりをどのように検出するか?先ほど紹介したように、InnoDB のステータスを監視することで、デッドロック レコードを収集し、後で簡単に確認できる小さなツールを作成できます。

2. デッドロックが発生した場合、ビジネス システムはどのように対応する必要がありますか?上記から、InnoDB がデッドロックを検出すると、クライアントに「ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションの再開をお試しください」というメッセージを報告し、トランザクションをロールバックすることがわかります。アプリケーションは、このメッセージに基づいてトランザクションを再開し、オンサイト ログを保存してさらに分析し、次のデッドロックを回避する必要があります。

5. ロック待機問題の分析

ビジネス開発では、デッドロックの可能性は低いですが、ロック待機の可能性は高くなります。ロック待機は、トランザクションが長時間ロック リソースを占有し、他のトランザクションが前のトランザクションによるロックの解放を待機しているために発生します。

T1

始める;

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

始める;

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

T2

更新のために、ID=3 のユーザーから * を選択します。

+----+------+------+
| ID | 名前 | 年齢 |
+----+------+------+
| 3 | 太陽 | 20 |
+----+------+------+
セット内の 1 行 (0.00 秒)

その他のクエリ操作

information_schema.INNODB_TRX から * を選択します。

メタデータ データベースの innodb トランザクション テーブルをクエリすると、現在実行中のトランザクションの数が 2 つ (トランザクション 1 とトランザクション 2) であることが監視されます。

T3 その他のクエリ操作

ユーザーを更新し、name='hehe' を id=3 に設定します。

id=3 のレコードはトランザクション 1 によってロックされているため、ステートメントはブロックされます (つまり、ロック待機状態になります)。

現在実行中のトランザクションの数は 2 であると監視されます。 T4 その他のクエリ操作

エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。

ロック待機時間がしきい値を超えたため、操作は失敗します。注: この時点ではトランザクション 2 はロールバックされません。

現在実行中のトランザクションの数は 2 であると監視されます。 T5 コミット。トランザクション 1 はコミットされましたが、トランザクション 2 はコミットされていません。現在実行中のトランザクションの数は 1 です。

上記から、トランザクション 1 が id=3 の行ロックを長時間保持し、トランザクション 2 がロック待機を生成していることがわかります。待機時間が innodb_lock_wait_timeout を超えると操作は中断されますが、トランザクションはロールバックされません。ビジネス開発でロック待機が発生すると、パフォーマンスに影響するだけでなく、ビジネス プロセスにも課題が生じます。これは、ビジネス側でロック待機状況に対して、操作を再試行するか、トランザクションをロールバックするかに応じて適応的な論理処理を行う必要があるためです。

MySQL メタデータ テーブルは、information_schema データベースの INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS などのトランザクションとロック待機に関する情報を収集します。これらのテーブルを使用して、ビジネス システムのロック待機ステータスを監視できます。次のステートメントを使用して、トランザクションとロック待機の関係を簡単に照会することもできます。

結果：

待機中のトランザクションID: 5132
待機スレッド: 11
watching_query: ユーザーセット名を 'hehe' に設定し、ID を 3 に設定
ブロッキング_trx_id: 5133
ブロックスレッド: 10
ブロッキングクエリ: NULL

要約:

1. ビジネス システムのロック待機を監視してください。これにより、現在のデータベース ロック状況を把握し、ビジネス手順を最適化できます。

2. ロック待機タイムアウト状況については、業務システム内で適切な論理的判断を行う必要があります。

6. まとめ

この記事では、いくつかの簡単な例を通して、一般的な MySQL 同時実行性の問題をいくつか紹介し、これらの問題のトラブルシューティングのアイデアを考え出します。この記事では、トランザクション、テーブル ロック、メタデータ ロック、行ロックについて説明していますが、トランザクション分離レベル、GAP ロックなど、これら以外にも発生する可能性のある同時実行性の問題は数多くあります。実際の同時実行の問題は数多くあり、複雑である可能性がありますが、トラブルシューティングのアイデアと方法は再利用できます。この記事では、show processlist、show engine innodb status、および query metadata tables を使用して、問題のトラブルシューティングと検出を行いました。問題にレプリケーションが関係している場合は、マスター/スレーブの監視も必要です。

テーブル `user` を作成します (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(300) デフォルト NULL,
`age` int(11) デフォルト NULL,
主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 デフォルト CHARSET=utf8