MySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法-mysql チュートリアル-php.cn

背景

本番環境でデッドロックが発生しました。デッドロックのログを確認すると、デッドロックの原因は 2 つの同一の更新ステートメント (where 条件の値のみが異なる) であることがわかりました。

は次のとおりです:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = &#39;xxx1&#39; AND `status` = 0;
UPDATE test_table SET `status` = 1 WHERE `trans_id` = &#39;xxx2&#39; AND `status` = 0;

ログイン後にコピー

最初は理解するのが難しかったですが、多くの調査と研究を経て、デッドロック形成の具体的な原理を分析しました。同じ問題を抱えている友人たちに役立つことを願って、この記事をみんなと共有したいと思います。

MySQL には多くの知識ポイントがあるため、多くの名詞はここではあまり紹介しません。興味のある友人は、特別に詳しく学習してフォローしてください。

デッドロックログ

*** (1) TRANSACTION:
TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = &#39;xxx1&#39; AND `status` = 0;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update
UPDATE test_table SET `status` = 1 WHERE `trans_id` = &#39;xxx2&#39; AND `status` = 0;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X
Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

ログイン後にコピー

上記のデッドロックログを簡単に分析します:

1. 最初の内容 (行 1 ～ 9) )、行 6 はトランザクション (1) によって実行される SQL ステートメントで、行 7 と 8 はトランザクション (1) が idx_status インデックスの X ロックを待機していることを意味します;
2。コンテンツの 2 番目のブロック (11 行目から 19 行目) では、16 行目はトランザクション (2) によって実行される SQL ステートメントであり、17 行目と 18 行目は、トランザクション (2) が idx_status インデックス X ロックオンを保持していることを意味します。

CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trans_id` varchar(21) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

ログイン後にコピー

テーブル構造からわかるように、

trans_id

columnuniq_trans_id # に一意のインデックスがあります。 ##,status 列 idx_status には通常のインデックスがあり、id 列は主キーインデックス PRIMARY です。 InnoDB エンジンには 2 種類のインデックスがあります:

データストレージとインデックスを配置します。インデックス構造のリーフノードは一緒に行データを格納します。
補助インデックスのリーフノードには、クラスター化インデックスのキー値である主キー値が格納されます。
主キーインデックス

はクラスター化インデックスであり、行データはリーフノードに格納されます。

uniq_trans_id インデックスと idx_status インデックスは補助インデックスであり、リーフノードには主キー値 (id 列の値) が格納されます。 補助インデックスを通じて行データを検索する場合、最初に補助インデックスを通じて主キー ID を見つけ、次に主キーインデックスを通じて二次検索 (これもテーブルに呼び出されます) を実行し、最後に行データを見つけます。

実行計画

実行計画を見ると、update ステートメントでインデックスのマージが使用されていることがわかります。つまり、このステートメントでは ## の両方が使用されています。 #uniq_trans_id MySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法インデックスが再度使用されます

idx_status

インデックス、 intersect(uniq_trans_id,idx_status) を使用することは、2 つのインデックスを通じて交差を取得することを意味します。 index_merge が使用される理由 MySQL5.0 より前は、テーブルは一度に 1 つのインデックスしか使用できず、条件付きスキャンで複数のインデックスを同時に使用することはできませんでした。ただし、5.1 からは、

index merge

最適化テクノロジが導入され、複数のインデックスを使用して同じテーブルで条件付きスキャンを実行できるようになりました。

たとえば、実行プラン内のステートメント:

UPDATE test_table SET `status` = 1 WHERE `trans_id` = &#39;38&#39; AND `status` = 0 ;

ログイン後にコピー

MySQL は、条件 trans_id = ‘38’ に基づいて uniq_trans_id# を使用します。

## インデックスはリーフノードに保存されている ID 値を検索します。同時に、条件

status = 0 に従って、idx_status インデックスを使用して、リーフノードに保存された ID 値; その後、2 つのグループ ID 値が交差され、最終的に交差 ID がテーブルに返されます、つまり、リーフノードに保存された行データが PRIMARY インデックスを通じて検出されます。 多くの人がここで疑問を抱くかもしれません。uniq_trans_id はすでに一意のインデックスです。このインデックスを通じて見つかるのは最大でも 1 つのデータだけです。では、なぜ MySQL オプティマイザーは 2 つのインデックスを使用するのでしょうか。 2 つのインデックスの共通部分を検索し、クエリのためにテーブルに戻ります。これにより、別の idx_status

インデックス検索プロセスが追加されませんか?これら 2 つの状況の実行プロセスを分析してみましょう。

最初の例では、uniq_trans_id インデックスのみを使用します。

##クエリ条件 trans_id = ‘38’ によると、 use

uniq_trans_id

PRIMARY インデックスを使用して、見つかった ID 値を通じてリーフノードに保存されている行データを検索します;
status = 0
条件でフィルター処理します。
2 番目のタイプはインデックスのマージを使用します。 Using intersect(uniq_trans_id,idx_status) ：

trans_id によると= ‘38’ クエリ条件、

uniq_trans_id

status によると = 0 クエリ条件。
idx_status
1/2 で見つかった ID 値の共通部分を取得し、PRIMARY インデックスを使用してリーフノードに保存されている行データを見つけます

上記の 2 つのケースの主な違いは、1 つ目の方法では最初にインデックスを通じてデータを検索し、次に他のクエリ条件を使用してフィルタリングすること、2 つ目の方法では最初に ID 値の共通部分を取得することです。 2 つのインデックスを通じて見つかりました。交差部分の後に ID 値がまだある場合は、テーブルに戻ってデータを取得します。

オプティマイザが 2 番目のケースの実行コストが最初のケースの実行コストより小さいと判断した場合、インデックスのマージが発生します。 (運用環境のフローテーブルには status = 0 のデータがほとんどありません。これが、オプティマイザが 2 番目のケースを考慮する理由の 1 つです)。

index_merge

MySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法

index_merge を使用した後にデッドロックが発生する理由上記は、2 つの更新トランザクションをロックするプロセスを簡単に示しています。図では、idx_status

インデックスと PRIMARY (クラスター化インデックス) の両方に重複部分と交差部分があり、デッドロックの状態が発生します。

たとえば、次のようなタイミングでデッドロックが発生します。

MySQL 最適化インデックスのマージによって発生するデッドロックを解決する方法

トランザクション 1 がトランザクション 2 の解放を待つ。ロックが解除されている場合、トランザクション 2 はトランザクション 1 がロックを解放するのを待つため、デッドロックが発生します。

MySQL はデッドロックを検出すると、より低いコストでトランザクションを自動的にロールバックします。上のタイミング図のように、トランザクション 1 がトランザクション 2 よりも少ないロックを保持している場合、MySQL はトランザクション 1 を続行します。。

解決策

1. コードレベル

# クエリ条件から、データをクエリするために trans_id
のみを渡します。、コードレベルでステータスが 0 であるかどうかを判断します。
Use force Index(uniq_trans_id) クエリステートメントで uniq_trans_id
を使用するように強制します。 index;
ここで、クエリ条件の直後に id フィールドを使用し、主キーを介して更新します。