魔法のMySQLデッドロックトラブルシューティング記録

背景

MySQL のデッドロックについて言えば、私は以前 MySQL のロックに関する基本的な紹介記事を書きました。基本的な MySQL のロックやデッドロックについては、開発者がデータベース ロックを理解する必要がある理由を説明したこの記事をお読みください。上記の経験から、デッドロックには簡単に対処できると思っていました。予想外に、晴れた日の午後に別のデッドロックが報告されましたが、今回は想像していたほど簡単ではありませんでした。

問題が初めて現れた

ある日の午後、システムが突然警報を発し、例外が発生しました。

よく見ると、トランザクションのロールバックが異常だったようです。デッドロックが原因でロールバックされたとのことでした。デッドロックの問題であることが判明しました。私は MySQL のロックについてある程度理解していたので、この問題を積極的に調査し始めました。

まず、データベースで Innodb Status を検索します。Innodb Status は、最後のデッドロックの情報を記録します。次のコマンドを入力します。

エンジン INNODB ステータスを表示

デッドロック情報は以下の通りで、SQL情報は単純に処理されます。

------------------------ 
最近検出されたデッドロック 
------------------------ 
2019-02-22 15:10:56 0x7eec2f468700 
*** （１）取引： 
トランザクション 2660206487、アクティブ 0 秒開始インデックス読み取り 
使用中の MySQL テーブル 1、ロックされているテーブル 1 
LOCK WAIT 2 ロック構造体、ヒープ サイズ 1136、1 行ロック 
MySQL スレッド ID 31261312、OS スレッド ハンドル 139554322093824、クエリ ID 11624975750 10.23.134.92 erp_crm__6f73 更新中 
/*id:3637ba36*/テナント構成の更新 SET 
 オープンカードポイント = 0 
 ここで、tenant_id = 123 
*** (1) このロックが許可されるのを待機しています: 
レコード ロック スペース ID 1322 ページ番号 534 n ビット 960 インデックス uidx_tenant (テーブル `erp_crm_member_plan`.`tenant_config` の trx ID 2660206487) lock_mode X はレコードをロックしますが、ギャップ待機はロックしません 
 *** （２）取引： 
トランザクション 2660206486、アクティブ 0 秒開始インデックス読み取り 
使用中の MySQL テーブル 1、ロックされているテーブル 1 
3 つのロック構造体、ヒープ サイズ 1136、2 つの行ロック 
MySQL スレッド ID 31261311、OS スレッド ハンドル 139552870532864、クエリ ID 11624975758 10.23.134.92 erp_crm__6f73 更新中 
/*id:3637ba36*/テナント構成の更新 SET 
 オープンカードポイント = 0 
 ここで、tenant_id = 123 
*** (2) ロックを保持する: 
レコード ロック スペース ID 1322 ページ番号 534 n ビット 960 インデックス uidx_tenant (テーブル `erp_crm_member_plan`.`tenant_config`) trx ID 2660206486 ロック モード S 
*** (2) このロックが許可されるのを待機しています: 
レコード ロック スペース ID 1322 ページ番号 534 n ビット 960 インデックス uidx_tenant (テーブル `erp_crm_member_plan`.`tenant_config` の trx ID 2660206486) lock_mode X はレコードをロックしますが、ギャップ待機はロックしません 
 *** トランザクションをロールバックします (1) 
------------

このデッドロック ログを簡単に分析して説明します。トランザクション 1 が Update ステートメントを実行する場合、where 条件の uidx_tenant インデックスの X ロック (行ロック) を取得する必要があります。トランザクション 2 は同じ Update ステートメントを実行し、uidx_tenant の X ロック (行ロック) も取得しようとします。するとデッドロックが発生し、トランザクション 1 がロールバックされます。当時私は混乱していたので、デッドロックに必要な条件を思い出しました。

1. 相互に排他的。

2. 条件を要求し、維持する。

3. 条件の剥奪はありません。

4. ループで待機します。

ログを見ると、トランザクション 1 とトランザクション 2 の両方が同じ行の行ロックを競合していることがわかります。これは、以前の循環ロック競合とは少し異なります。どのように見ても、循環待機条件を満たすことはできません。同僚から注意を受けたところ、デッドロック ログはトラブルシューティングに使用できないため、ビジネス コードとビジネス ログを使用してトラブルシューティングするしか方法がないとのことでした。このコードのロジックは次のとおりです。

パブリック int saveTenantConfig(PoiContext poiContext、TenantConfigDO tenantConfig) { 
 試す { 
  戻り値: tenantConfigMapper.saveTenantConfig(poiContext.getTenantId(), poiContext.getPoiId(), tenantConfig); 
 } キャッチ (DuplicateKeyException e) { 
  LOGGER.warn("[saveTenantConfig] 主キーが競合しています。レコードを更新してください。context:{}, config:{}", poiContext, tenantConfig); 
  戻り値: tenantConfigMapper.updateTenantConfig(poiContext.getTenantId(), tenantConfig); 
 } 
 }

このコードは設定ファイルを保存することを意味します。ユニークインデックスの競合が発生した場合は更新されます。もちろん、標準的な方法で記述されない場合があります。実際には、

...に挿入する 
重複キーの更新時

同様の効果が得られますが、それでもデッドロックが発生します。コードを読んだ後、同僚が当時の業務ログを送ってくれました。

同時に発生したログが 3 つあることがわかります。これは、一意のインデックスの競合が発生し、更新ステートメントが入り、デッドロックが発生したことを示しています。この時点で、ようやく答えが明らかになり始めているようです。

ここで、テーブル構造を次のように見てみましょう (簡略化)。

テーブル `tenant_config` を作成します ( 
 `id` bigint(21) NOT NULL AUTO_INCREMENT, 
 `tenant_id` int(11) NULLではない、 
 `open_card_point` int(11) デフォルト NULL, 
 主キー (`id`)、 
 ユニークキー `uidx_tenant` (`tenant_id`) 
) エンジン=InnoDB デフォルト文字セット=utf8mb4 行フォーマット=COMPACT

tenant_id は一意のインデックスとして使用され、挿入条件と更新条件はすべて一意のインデックスに基づいています。

テナント構成の更新 SET 
 オープンカードポイント = 0 
 ここで、tenant_id = 123

この時点では、挿入時にユニークインデックスをロックすることが関係しているようです。次に、詳細な分析の次のステップに進みます。

詳細な分析

上記では、更新ステートメントに 3 つのトランザクションが入ると述べました。説明を簡単にするために、更新ステートメントに同時に入るトランザクションは 2 つだけです。次の表は、プロセス全体を示しています。

ヒント: S ロックは共有ロックであり、X ロックはミューテックス ロックです。一般的に言えば、X ロックと S、X ロックは相互に排他的ですが、S ロックと S ロックは相互に排他的ではありません。

上記のプロセスから、このデッドロックの解決の鍵は S ロックを取得することであることがわかります。再度挿入するときに S ロックを取得する必要があるのはなぜでしょうか?一意のインデックスを検出する必要があるからですか? RR 分離レベルで読み取りを行う場合は、現在の読み取りとなるため、実際には S ロックを追加する必要があります。ここで、ユニークキーがすでに存在していることがわかります。このとき、2 つのトランザクションの S ロックによって更新の実行がブロックされ、上記の循環待機状態が形成されます。

ヒント: MVCC では、現在の読み取りとスナップショット読み取りの違いは、現在の読み取りでは最新のデータを取得するために毎回ロックする必要がある (共有ロックまたはミューテックス ロックを使用できます) のに対し、スナップショット読み取りではトランザクションの開始時のスナップショットが読み取られ、これは UNDO ログを通じて実現されるという点です。

これがデッドロック全体の原因です。このデッドロックが発生する可能性がある別の状況は、3 つの挿入操作が同時に実行される場合です。最初に挿入されたトランザクションが最終的にロールバックされると、他の 2 つのトランザクションでもこのデッドロックが発生します。

解決

ここでの根本的な問題は、S ロックを削除する必要があることです。参考までに、次の 3 つの解決策を示します。

• RR 分離レベルを RC 分離レベルまで下げます。ここでは、RC 分離レベルではスナップショット読み取りが使用されるため、S ロックは追加されません。
• 再度挿入するときは、更新に select * を使用し、X ロックを追加して、S ロックが追加されないようにします。
• Redis、ZK などを使用して、事前に分散ロックを追加することができます。分散ロックについては、こちらの記事を参照してください。分散ロックについて話しましょう

最初の方法はあまり現実的ではありません。結局のところ、分離レベルを簡単に変更することはできません。 3番目の方法はもっと面倒です。そこで、最終的に私たちが採用したのが 2 番目の方法です。

要約する

ここまで述べてきましたが、簡単にまとめたいと思います。デッドロックの問題をトラブルシューティングする場合、デッドロック ログを確認するだけでは問題が解決しないことがあります。正しい結果を得るには、ビジネス ログ、コード、テーブル構造全体を分析する必要があります。もちろん、上記にはデータベース ロックに関する基本的な知識が含まれています。理解できない場合は、私の別の記事「開発者がデータベース ロックについて知っておく必要がある理由」を参照してください。

前回の記事は、コミュニティによって構築された包括的で優れた Java 学習ルートである JGrowing-CaseStudy に含まれていました。オープンソース プロジェクトのメンテナンスに参加したい場合は、一緒に構築できます。github アドレスは https://github.com/javagrowing/JGrowing です。

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