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 のトランジション効果とアニメーショントランジションの使用例の詳細な説明

推薦する

MySQLパスワードを忘れた場合のいくつかの解決策

解決策1完全にアンインストールしてすべてのデータを削除します。まず、MySQLに関連するすべてのプロ...

JS の難しさ 同期と非同期、スコープとクロージャ、プロトタイプとプロトタイプ チェーンの詳細な説明

目次JS スリーマウンテンズ同期 非同期同期と非同期の違い範囲、終了関数スコープチェーンブロックスコ...

Web面接でよくある質問:リフローとリペイントの原理と違い

目次ブラウザのレンダリングメカニズムリフローと再塗装リフロー逆流を引き起こす行為:再描画再描画を引き...

JSは画像の滝の流れの効果を実現します

この記事では、画像ウォーターフォールフローを実現するためのJSの具体的なコードを参考までに共有します...

JavaScript 操作要素の例

操作要素の詳細については、前回の記事を参照してください。JavaScript WebAPI、DOM、...

Windows 10 システムで Mysql8.0.13 のルート パスワードを忘れる方法

1. まずmysqlサービスを停止します管理者としてCMDを開いて閉じるか、Windowsサービスペ...

JavaScript はクラス宝くじアプレットを実装します

この記事では、クラス抽選アプレットを実装するためのJavaScriptの具体的なコードを参考までに紹...

CSS における zoom:1 属性の定義と機能

今日、CSS の zoom 属性は何のために使用されるのかと尋ねられました。この属性は、フローティン...

JavaScriptはキャンバスを使用して座標と線を描画します

この記事では、JavaScriptでキャンバスを使用して座標と線を描く具体的なコードを参考までに紹介...

React リストバーとショッピングカートコンポーネントの使用の詳細な説明

この記事では、Reactリストバーとショッピングカートコンポーネントの具体的なコードを参考までに紹介...

MIME TYPEとは?MIME-Typesタイプコレクション

MIME タイプとは何ですか? 1. まず、ブラウザがコンテンツを処理する方法を理解する必要がありま...

Alibaba Cloud centos7にmysql8.0.22をインストールする詳細なチュートリアル

1. MySQLインストールパッケージをダウンロードするまず、https://dev.mysql.c...

Vue/React シングルページ アプリケーションをリフレッシュなしで復元するソリューション

目次導入なぜわざわざ?落とし穴のあるコミュニティソリューション(Vue を例に挙げる)現時点では良い...

ウェブデザインと制作に関する科学的原則と提案の要約

<br />ネットワーク設計の分野では、アイトラッキングに関する研究が非常に盛んに行われ...

Vue ルーティング遅延読み込みの詳細

目次1. ルートの遅延読み込みとは何ですか? 2. ルートの遅延読み込みの使用1. ルートの遅延読み...