MySQL ロックブロッキングの詳細な分析

MySQL ロックブロッキングの詳細な分析

日常のメンテナンスでは、スレッドがブロックされることが多く、データベースの応答が非常に遅くなります。どのスレッドがブロックの原因になっているかを調べる方法を見てみましょう。

1. 環境の説明

RHEL 6.4 x86_64 + MySQL 5.6.19

トランザクション分離レベル: RR

2. テストプロセス

3. ロックをブロックするスレッド情報を表示する

分析にはいくつかの方法があります。

3.1 show processlistを使用して表示する

MySQL [(なし)]> プロセスリストを表示します。
+----+------+-----------+---------+-------+------+--------------+------------------------------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+-----------+---------+-------+------+--------------+------------------------------------------+
| 2 | root | localhost | NULL | クエリ | 0 | init | プロセスリストを表示 |
| 3 | root | localhost | test | クエリ | 70 | データを送信中 | select count(*) from t3 a,t3 b |
| 4 | root | localhost | test | クエリ | 65 | 更新中 | empno=7788 の emp から削除 |
| 7 | root | localhost | test | クエリ | 68 | 更新中 | update emp set sal=3500 where empno=7788 |
+----+------+-----------+---------+-------+------+--------------+------------------------------------------+
セット内の 4 行 (0.00 秒)

データベース内にスレッドが多数ある場合、この方法では確認するのが確かに困難です。

3.2 show engine innodb statusを使用して直接表示する

------------
取引
------------
Trx ID カウンター 4131
トランザクションの n:o < 4119 のパージが完了しました。n:o < 0 の状態を元に戻します: 実行中ですがアイドル状態です
履歴リストの長さ 126
各セッションのトランザクションのリスト:
---トランザクション0、開始されていません
MySQL スレッド ID 2、OS スレッド ハンドル 0x7f953ffff700、クエリ ID 115 localhost root init
エンジンの InnoDB ステータスを表示
---トランザクション 4130、アクティブ 41 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 360、1 行ロック
MySQL スレッド ID 4、OS スレッド ハンドル 0x7f953ff9d700、クエリ ID 112 ローカルホスト ルート更新中
empno=7788 の emp から削除
------- TRX はこのロックが許可されるまで 41 秒待機しています: ## 41 秒待機しました
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4130 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10; コンパクト フォーマット; 情報ビット 0 ## スレッド 4 は、test.emp、ページ番号 = 3 の主キーに X ロックを追加するのを待機しています。
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
------------------
---トランザクション 4129、アクティブ 45 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 360、1 行ロック
MySQL スレッド ID 7、OS スレッド ハンドル 0x7f953ff6c700、クエリ ID 111 ローカルホスト ルート更新中
empno=7788 で emp set sal=3500 を更新します
------- TRX はこのロックが許可されるまで 45 秒待機しています: ## 45 秒待機しました
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4129 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0 ## スレッド 7 は、test.emp、ページ番号 = 3 の主キーに X ロックを追加するのを待機しています。
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
------------------
---トランザクション 4128、アクティブ 51 秒
2 つのロック構造体、ヒープ サイズ 360、1 つの行ロック
MySQL スレッド ID 3、OS スレッド ハンドル 0x7f953ffce700、クエリ ID 110 ローカルホスト ルートのクリーンアップ中

主な根本原因は依然として thread=3 によって引き起こされていることはわかっていますが、この結果は innodb ステータスからは分析できません。

上記から、スレッド 4 とスレッド 7 の両方が、test.emp、ページ番号 = 3 のプライマリ キーに X ロックを追加するのを待機していることがわかります。ただし、スレッド 7 は 45 秒間待機し、スレッド 4 は 41 秒間待機します。ロックはスレッド 7 よりも後で適用されるため、スレッド 7 がスレッド 4 をブロックしたことがわかります。スレッド 7 が待機している理由については、ここでは根本的な原因を分析できません。

3.3 mysqladmin debugを使用して表示する

# mysqladmin -S /tmp/mysql3306.sock デバッグ

エラー ログには次の内容が表示されます:

スレッド database.table_name ロック/待機中 Lock_type
 
 
3 test.t3 ロック - 読み取り 低優先度読み取りロック
7 test.emp ロック済み - 書き込み 優先度の高い書き込みロック

この方法では、スレッド ID=3 と 7 がブロッカーであることがわかりますが、スレッド 7 もスレッド ID=3 によってブロックされていると判断するにはまだ正確さが足りません。

3.4 innodb_lock_monitorを使用してブロッキングロックスレッドを取得する

MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; ## 任意のデータベースにこのテーブルを作成すると、ロックモニターが有効になります
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.07 秒)
 
MySQL [テスト]> 警告を表示\G
************************** 1. 行 ****************************
 レベル: 警告
 コード: 131
メッセージ: テーブル名 innodb_lock_monitor を使用して診断出力を有効にすることは非推奨であり、将来のリリースで削除される可能性があります。INFORMATION_SCHEMA または PERFORMANCE_SCHEMA テーブルを使用するか、SET GLOBAL innodb_status_output=ON を使用してください。
セット内の 1 行 (0.00 秒)

注: これにより 5.6 では警告が発生しますが、使用には影響しません。

次に、show engine innodb status を使用して以下を表示します。

------------
取引
------------
Trx ID カウンター 4667
トランザクションの n:o < 4659 のパージが完了しました。n:o < 0 の状態を元に戻します: 実行中ですがアイドル状態です
履歴リストの長さ 138
各セッションのトランザクションのリスト:
---トランザクション0、開始されていません
MySQL スレッド ID 9、OS スレッド ハンドル 0x7f813c5f7700、クエリ ID 152 localhost root init
エンジンの InnoDB ステータスを表示
---トランザクション 4663、アクティブ 78 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 360、1 行ロック
MySQL スレッド ID 4、OS スレッド ハンドル 0x7f813c628700、クエリ ID 149 ローカルホスト ルート更新中
empno=7788 の emp から削除
------- TRX はこのロックが許可されるまで 78 秒待機しています: ## 78 秒待機しました
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4663 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0 ## スレッド 4 は、test.emp、ページ番号 = 3 の主キーに X ロックを追加するのを待機しています。
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
------------------
TABLE LOCK テーブル `test`.`emp` trx id 4663 ロック モード IX ## 主キー行に X ロックを追加する前に、まずテーブルにインテンション ロック IX を追加します。
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4663 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
---トランザクション 4662、アクティブ 81 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
LOCK WAIT 2 ロック構造体、ヒープ サイズ 360、1 行ロック
MySQL スレッド ID 7、OS スレッド ハンドル 0x7f813c5c6700、クエリ ID 148 ローカルホスト ルート更新中
empno=7788 で emp set sal=3500 を更新します
------- TRX はこのロックが許可されるまで 81 秒待機しています: ## 81 秒待機しました
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4662 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0 ## スレッド 7 は、test.emp、ページ番号 = 3 の主キーに X ロックを追加するのを待機しています。
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
------------------
TABLE LOCK テーブル `test`.`emp` trx id 4662 ロック モード IX ## 主キー行に X ロックを追加する前に、まずテーブルにインテンション ロック IX を追加します。
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 インデックス `PRIMARY`、テーブル `test`.`emp` trx ID 4662 lock_mode X はレコードをロックしますが、ギャップ待機はロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;
 
---トランザクション 4615、アクティブ 1579 秒、InnoDB 1222 内でスレッドが宣言されました
使用中の MySQL テーブル 2、ロックされている 0
2 つのロック構造体、ヒープ サイズ 360、1 つの行ロック
MySQL スレッド ID 3、OS スレッド ハンドル 0x7f813c659700、クエリ ID 147 localhost root データ送信
select count(*) from t3 a,t3 b ## これはスレッド3で現在実行されているSQLです
Trx 読み取りビューでは、ID >= 4662 の trx は表示されず、< 4659 が表示されます。
テーブル ロック テーブル `test`.`emp` trx id 4615 ロック モード IX ## スレッド 3 は、テーブルに対する意図的な IX ロックと、test.emp テーブル、ページ番号 = 3 の主キーに対する行レベル X ロックを保持しています。
レコード ロック スペース ID 16 ページ番号 3 n ビット 88 テーブル `test`.`emp` のインデックス `PRIMARY` trx ID 4615 lock_mode X はレコードをロックしますが、ギャップはロックしません
レコード ロック、ヒープ番号 9 物理レコード: n_fields 10、コンパクト フォーマット、情報ビット 0
 0: 長さ 4; 16 進数 80001e6c; asc l;;
 1: 長さ 6; 16 進数 000000001018; 昇順 ;;
 2: 長さ 7; 16 進数 91000001420084; asc B ;;
 3: 長さ 5; 16 進数 53434f5454; 昇順 SCOTT;;
 4: 長さ 7; 16 進数 414e414c595354; asc ANALYST;;
 5: 長さ 4; 16 進数 80001d8e; asc ;;
 6: 長さ 4; 16 進数 208794f0; 昇順;;
 7: 長さ 4; 16 進数 80000bb8; 昇順 ;;
 8: SQL NULL;
 9: 長さ 4; 16 進数 80000014; 昇順 ;;

スレッド 3 が現在 select t3 テーブル操作を実行しているのに、test.emp テーブルのページ num=3 をロックしているのはなぜですか?

test.emp テーブル上のスレッド 3 のトランザクションが時間内にコミットされなかった可能性があります。

したがって、スレッド 3 がスレッド 7 をブロックし、スレッド 7 がスレッド 4 をブロックしているため、根本的な原因はスレッド 3 にあると結論付けることができます。できるだけ早くスレッド 3 を送信するか、強制終了してください。

4. 結論

InnoDB でのロック ブロッキングを分析する場合、いくつかの方法を比較します。

(1)show processlistを使用して表示するのは信頼できません。

(2)show engine innodb statusを直接使用して問題の根本原因を確認することは不可能である。

(3) mysqladmin debugを使用してロックを生成するすべてのスレッドを表示すると、それらを確認することはできますが、どれが根本的な原因であるかを判断することはできません。

(4) innodb_lock_monitorを有効にした後、show engine innodb statusを使用してロックブロックの根本原因を見つけます。

オリジナルリンク: https://blog.csdn.net/hw_libo/article/details/39080809

これで、MySQL ロック ブロッキングの詳細な分析に関するこの記事は終了です。MySQL ロック ブロッキングの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL のロック待機とデッドロック問題の分析
  • MySQL ロックの知識ポイントのまとめ
  • MySQL のロックに関する問題

<<:  Docker に Elasticsearch 7.6.2 をインストールするチュートリアル

>>:  Vue のトランジション効果とアニメーショントランジションの使用例の詳細な説明

推薦する

IDEAでVUEプロジェクトをデバッグするための詳細な手順

js コードをデバッグするには、コード内にデバッガーを記述するか、Chrome で毎回ブレークポイン...

Windows での MySQL インストール チュートリアル (画像とテキスト付き)

MySQL インストール手順 MySQL は、スウェーデンの MySQL AB によって開発された...

Web コンポーネントの内部イベント コールバックと問題点の分析

目次前面に書かれたWC とは何でしょうか?現在の欠陥1. コンポーネント内部イベントのコールバック2...

html オプション 無効 選択 選択 無効 オプションの例

コードをコピーコードは次のとおりです。 <選択> <オプション値="&q...

CSSはコンテンツの高さが足りない場合にフッターを自動的に下部に固定します

UI カットのプロセスでは、ページはヘッダー、コンテンツ、フッターの 3 つの部分で構成されることが...

【Webデザイン】E-WebTemplates の美しい海外の Web ページ テンプレート (FLASH+PSD ソース ファイル+HTML) を共有します

これらはすべて海外のE-WebTemplates WebサイトからのWebページテンプレートであり、...

16進カラーコード(完全版)

赤とピンク、およびそれらの 16 進コード。 #990033 #CC6699 #FF6699 #FF...

nginx を使用して正規表現で指定された URL リクエストを傍受する方法

nginx サーバーnginx は、静的ファイルの処理に非常に効率的な優れた Web サーバーです。...

HTML&CSS&JS 互換性ツリー (IE、Firefox、Chrome)

Web デザインにおけるツリーとは何ですか?簡単に言うと、リンクをクリックするとサブディレクトリが展...

Linux の一般的なテキスト処理コマンドと vim テキストエディタ

今日は、いくつかの一般的なテキスト処理コマンドとvimテキストエディタを紹介します。 3日目 - 一...

DockerでRedisをデプロイして起動する方法

DockerでRedisをデプロイするまずLinuxにDockerをインストールし、次にDocker...

DockerにRedisをインストールし、設定ファイルとして起動する詳細な説明

更新: 最近、サーバーがマイニング ウイルスによってハッキングされたことが判明しました。これは、おそ...

MySQLがウィンドウ関数で合計関数を実行するときに発生する可能性のあるバグ

MySql のウィンドウ関数を使用して統計データを収集する際に、小さな問題が見つかったので、それにつ...

SQL 結合クエリの内部結合、外部結合、クロス結合の違いの詳細な説明

データベースを使用するアプリケーションを開発する場合、必然的にユニオンクエリを使用する必要があります...

jQueryはキャンバスタグを使用して検証コードを描画します

<canvas> 要素は、クライアント側のベクター グラフィックス用に設計されています。...