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

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

背景

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

問題が初めて現れた

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

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

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

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

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

------------------------ 
最近検出されたデッドロック 
------------------------ 
2019-02-22 15:10:56 0x7eec2f468700 
*** (1)取引: 
トランザクション 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 はレコードをロックしますが、ギャップ待機はロックしません 
 *** (2)取引: 
トランザクション 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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • Mysql のデッドロックの表示とデッドロックの除去の詳細な説明
  • MySQLのデッドロックチェック処理の通常の方法
  • MySQLデッドロックの原因と解決策
  • MySQLデッドロック問題の詳細な分析
  • MySQL デッドロック ルーチン: 一意のインデックスの下でのバッチ挿入順序の不一致
  • MySQL デッドロック シナリオ例の分析
  • MySQL データベースのパージデッドロック問題の分析
  • SQLによる分散デッドロックの検出と排除の詳細な説明

<<:  Vue で動的なスタイルを実現するためのさまざまな方法のまとめ

>>:  Ubuntu 19 以下に Android Studio をインストールするチュートリアル

推薦する

ユーザーがフォームを繰り返し送信するのを防ぐ方法の概要

重複したフォーム送信は、マルチユーザー Web アプリケーションで最も一般的で厄介な問題です。重複送...

IDEA が Docker を統合してリモート展開を実現するための手順

1. Dockerサーバーへのリモートアクセスを有効にするdocker が配置されているリモート サ...

el-table のテーブルを最適化するために仮想リストを使用する方法についての簡単な説明

目次序文解決具体的な実装満たすべき前提条件質問序文テーブルをよく使用します。データ量が多い場合は直接...

非常に優れた CSS スキル 10 選のコレクションと共有

ここでは、CSS テクニックを巧みに使用することで、HTML を変更せずにブログやテンプレートの外観...

MySQL のクエリパフォーマンスに対する制限の影響

I. はじめにまず、MySQL のバージョンについて説明します。 mysql> バージョンを選...

CSS3で作成した画像スクロール効果

成果を達成する実装コードhtml <base href="https://s3-us...

Tomcatの各ポートの機能の詳細な説明

tomcat 設定ファイルから、tomcat の起動時にデフォルトで 8080 (8443)、800...

CSS3 を使用したテキスト折り紙効果のサンプルコード

序文この記事では主に、CSS3 を使用してテキスト折り紙効果を実現する例を紹介します。これは、参考と...

Nginx で HTTPS 証明書を構成する詳細なプロセス

1. HttpとHttpsの違いHTTP: インターネットで最も広く使用されているネットワーク プロ...

docker-compose を使用して Clickhouse をすばやくデプロイする方法のチュートリアル

ClickHouse は、オープンソースの列指向 DBMS (Yandex によって開発) です。 ...

Dockerコマンドは一般ユーザーが実行できるように実装されている

dockerをインストールすると、通常はdockerユーザーグループが作成されます。ステップ2: 現...

JavaScriptプロトタイプチェーンを理解する

目次1. プロトタイプとプロトタイプチェーンの平等関係を理解する2: プロトタイプとプロトタイプ チ...

nginx と openssl で https を実装する方法

サーバーデータがSSL証明書を使用して暗号化および認証されていない場合、ユーザーのデータはプレーンテ...

MySQL 8.0.22 のインストールと設定方法のグラフィックチュートリアル

この記事ではMySQL 8.0.22のインストールと設定について記録します。具体的な内容は以下のとお...

JavaScript 事前分析、オブジェクトの詳細

目次1. 事前分析1. 変数の事前解析と関数の事前解析1. 変数の事前解析2. 機能事前分析2. 事...