MySQLのロングトランザクションに関する深い理解

序文：

この記事では主にMySQLのロングトランザクションに関する内容を紹介します。例えば、トランザクションを開始したがコミットまたはロールバックされていない場合はどうなるでしょうか？トランザクション待機状況にはどのように対処すればよいでしょうか？この記事ではその答えを紹介します。

注: この記事では、トランザクション分離レベルと関連機能については重点的に説明しません。代わりに、長いトランザクションに関連する危険性と、それらを監視および処理する方法について説明します。この記事は、MySQL バージョン 5.7.23 と非反復読み取り (RR) 分離レベルで実行された実験に基づいています。 (\G 構文を使用するとクエリ構造を読みやすくすることができますが、mysql コマンドラインからのみ使用できます。)

1. ロングトランザクションとは何ですか?

まず、ロングトランザクションとは何かを知る必要があります。名前が示すように、長時間実行され、長時間コミットされないトランザクションです。大規模トランザクションとも呼ばれます。このタイプのトランザクションは、多くの場合、多くのブロックとロックのタイムアウトを引き起こし、マスタースレーブの遅延を簡単に引き起こす可能性があります。長いトランザクションの使用は避けてください。

以下では、トランザクションを開始し、長いトランザクションをシミュレートする方法を説明します。

#次の構造とデータを持つ stu_tb テーブルがあるとします。mysql> show create table stu_tb\G
************************** 1. 行 ****************************
    テーブル: stu_tb
テーブルの作成: CREATE TABLE `stu_tb` (
 `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
 `stu_id` int(11) NOT NULL COMMENT '学生ID',
 `stu_name` varchar(20) デフォルト NULL コメント '学生名',
 `create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
 `update_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
 主キー (`increment_id`)、
 BTREE を使用したユニーク キー `uk_stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='テスト学生テーブル'
セット内の1行（0.01秒）

mysql> stu_tb から * を選択します。
+--------------+--------+----------+----------------------+----------------------+
| 増分 ID | スタッ ID | スタッ名 | 作成時刻 | 更新時刻 |
+--------------+--------+----------+----------------------+----------------------+
| 1 | 1001 | 1 から | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 2 | 1002 | dfsfd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 3 | 1003 | fdgfg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 4 | 1004 | sdfsdf | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 5 | 1005 | dsfsdg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 7 | 1007 | fgds | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
| 8 | 1008 | dgfsa | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+----------------------+----------------------+
セット内の行数は 8 です (0.00 秒)

#トランザクションを明示的に開くには、beginまたはstart transactionを使用します
mysql> トランザクションを開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> stu_tb から * を選択して、 stu_id = 1006 を更新します。
+--------------+--------+----------+----------------------+----------------------+
| 増分 ID | スタッ ID | スタッ名 | 作成時刻 | 更新時刻 |
+--------------+--------+----------+----------------------+----------------------+
| 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 |
+--------------+--------+----------+----------------------+----------------------+
セット内の1行（0.01秒）

 # 前回のトランザクションを時間内にコミットしないと、このトランザクションは長いトランザクションになります。他のセッションがこのデータを操作しようとすると、待機状態が続きます。

2. ロングトランザクションを見つける方法

トランザクション待機の問題が発生した場合、最初に実行されているトランザクションを見つける必要があります。 information_schema.INNODB_TRX テーブルには、現在 innodb 内で実行されているトランザクション情報が含まれています。このテーブルはトランザクションの開始時刻を示します。少し計算するだけで、トランザクションの実行時間を取得できます。

mysql> INFORMATION_SCHEMA.INNODB_TRX t \G から t.*,to_seconds(now())-to_seconds(t.trx_started) のidle_time を選択します。
************************** 1. 行 ****************************
          トランザクションID: 6168
         trx_state: 実行中
        開始日時: 2019-09-16 11:08:27
   trx_requested_lock_id: NULL
     trx_wait_started: NULL
        trx_weight: 3
    trx_mysql_スレッドID: 11
         trx_query: NULL
    trx_operation_state: NULL
     使用中のtrxテーブル: 0
     trx_tables_locked: 1
     trx_lock_structs: 3
   trx_lock_memory_bytes: 1136
      ロックされた行数: 2
     trx_rows_modified: 0
  trx_concurrency_tickets: 0
    trx_isolation_level: 繰り返し読み取り
     trx_unique_checks: 1
  trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
     trx_is_read_only: 0
trx_autocommit_non_locking: 0
         アイドル時間: 170

結果では、idle_time が計算され、トランザクションの期間になります。ただし、トランザクションの trx_query は NUL です。これは、トランザクションが何も実行していないことを意味するものではありません。トランザクションには複数の SQL 文が含まれる場合があります。SQL 文が実行されると、表示されなくなります。現在のトランザクションは実行中であり、InnoDB はこのトランザクションの後続の SQL ステートメントがあるかどうか、またはいつコミットされるかを認識していません。したがって、trx_query は意味のある情報を提供できません。

このトランザクションによって実行された SQL を確認して、長いトランザクションを強制終了できるかどうかを確認したい場合はどうすればよいでしょうか。他のシステムテーブルと組み合わせてクエリを実行できます。具体的なクエリ SQL は次のとおりです。

mysql> select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx 内部結合
  -> information_schema.PROCESSLIST b
  -> a.TRX_MYSQL_THREAD_ID=b.id かつ b.command = 'Sleep' の場合
  -> 内部結合 performance_schema.threads c ON b.id = c.PROCESSLIST_ID
  -> 内部結合 performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
+---------------------+----------+----+-------+----------+---------+-----------------------------------------------------------+
| now() | diff_sec | id | ユーザー | ホスト | db | SQL_TEXT |
+---------------------+----------+----+-------+----------+---------+-----------------------------------------------------------+
| 2019-09-16 14:06:26 | 54 | 17 | root | localhost | testdb | 更新のために stu_id = 1006 の stu_tb から * を選択 |
+---------------------+----------+----+-------+----------+---------+-----------------------------------------------------------+

上記の結果では、diff_sec は上記のidle_time と同じ意味を持ち、このトランザクションが続く秒数です。 SQL_TEXT は、トランザクションによって実行されたばかりの SQL を示します。ただし、上記のステートメントでは、トランザクションで最後に実行された SQL しか見つけることができません。トランザクションには複数の SQL が含まれる場合があることはわかっています。次に、このコミットされていないトランザクションによって実行された SQL を照会します。これは実現可能でしょうか? 答えは、events_statements_history システムテーブルを組み合わせることでも実現できるということです。次のステートメントは、トランザクションによって実行されたすべての SQL をクエリします。

mysql> 選択
  -> ps.id 'プロセスID'、
  -> ps.ユーザー、
  -> ps.HOST、
  -> esh.EVENT_ID、
  -> trx.trx_started、
  -> esh.event_name 'イベント名',
  -> esh.sql_text 'SQL'、
  -> ps.time
  -> から
  -> PERFORMANCE_SCHEMA.events_statements_history esh
  -> PERFORMANCE_SCHEMA.threads th を esh.thread_id = th.thread_id で結合します
  -> information_schema.PROCESSLIST ps を ps.id = th.processlist_id で結合します
  -> LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id
  -> どこ
  -> trx.trx_id は NULL ではありません
  -> AND ps.USER != 'SYSTEM_USER'
  -> 順序
  ->esh.EVENT_ID;
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| プロセス ID | ユーザー | ホスト | EVENT_ID | trx_started | イベント名 | SQL | 時間 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+
| 20 | ルート | ローカルホスト | 1 | 2019-09-16 14:18:44 | ステートメント/sql/select | @@version_comment 制限 1 を選択 | 60 |
| 20 | root | localhost | 2 | 2019-09-16 14:18:44 | statement/sql/begin | トランザクションの開始 | 60 |
| 20 | ルート | ローカルホスト | 3 | 2019-09-16 14:18:44 | ステートメント/sql/select | SELECT DATABASE() | 60 |
| 20 | ルート | ローカルホスト | 4 | 2019-09-16 14:18:44 | ステートメント/com/Init DB | NULL | 60 |
| 20 | root | localhost | 5 | 2019-09-16 14:18:44 | statement/sql/show_databases | データベースを表示 | 60 |
| 20 | root | localhost | 6 | 2019-09-16 14:18:44 | statement/sql/show_tables | テーブルを表示 | 60 |
| 20 | root | localhost | 7 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | root | localhost | 8 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 |
| 20 | ルート | ローカルホスト | 9 | 2019-09-16 14:18:44 | ステートメント/sql/select | select * from stu_tb | 60 |
| 20 | root | localhost | 10 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb where stu_id = 1006 for update | 60 |
+------------+------+-----------+----------+---------------------+------------------------------+-----------------------------------------------------+------+

上記の結果から、トランザクションによって最初から現在までに実行されたすべての SQL ステートメントを確認できます。トランザクションの関連情報をすべて照会した後、他のトランザクションに影響を与えたり待機を引き起こしたりしないように、トランザクションを強制終了できるかどうかを判断できます。

これについてもう少し詳しく説明しましょう。長いトランザクションは、ブロッキングやデッドロックを引き起こす可能性が非常に高くなります。通常、まず sys.innodb_lock_waits ビューをクエリして、トランザクションのブロッキングがあるかどうかを判断できます。

#トランザクションが更新のためにselect * from stu_tb where stu_id = 1006を実行すると仮定します
#別のトランザクションが update stu_tb set stu_name = 'wang' where stu_id = 1006 を実行します

mysql> sys.innodb_lock_waits\G から * を選択します
************************** 1. 行 ****************************
        待機開始: 2019-09-16 14:34:32
          待機時間: 00:00:03
        待機時間秒数: 3
        ロックされたテーブル: `testdb`.`stu_tb`
        ロックされたインデックス: uk_stu_id
         ロックタイプ: レコード
       待機中のトランザクションID: 6178
     待機_trx_開始: 2019-09-16 14:34:32
       待機中のトランザクションの経過時間: 00:00:03
   待機中の trx 行ロック: 1
  待機中の trx 行の変更: 0
         待機中のpid: 19
        待機クエリ: stu_tb を更新し、stu_name を 'wang' に設定し、stu_id を 1006 に設定します。
       待機ロックID: 6178:47:4:7
      待機ロックモード: X
       ブロッキング_trx_id: 6177
        ブロッキングpid: 20
       ブロッキングクエリ: NULL
      ブロッキングロックID: 6177:47:4:7
     ブロッキングロックモード: X
    ブロッキング_trx_started: 2019-09-16 14:18:44
      ブロッキング_trx_age: 00:15:51
  ブロック_trx_行ロック: 2
 ブロッキング_trx_rows_modified: 0
   sql_kill_blocking_query: クエリ 20 を強制終了する
sql_kill_blocking_connection: 20 を強制終了する

上記の結果には、ブロックされた SQL とロックの種類が表示されます。さらに強力なのは、セッションを強制終了するステートメントも提供されることです。ただし、セッションの実行をブロックする SQL は見つかりませんでした。より詳細な情報を調べたい場合は、次のステートメントを使用できます。

mysql> 選択
  -> tmp.*,
  -> c.SQL_Text ブロッキングSQLテキスト、
  -> p.HOST ブロッキングホスト
  -> から
  -> (
  -> 選択
  -> r.trx_state 監視中のtrx_state、
  -> r.trx_id 待機中のtrx_id、
  -> r.trx_mysql_thread_Id 待機スレッド、
  -> r.trx_query 待機クエリ、
  -> b.trx_state ブロッキングtrx_state、
  -> b.trx_id ブロッキングtrx_id、
  -> b.trx_mysql_thread_id ブロッキングスレッド、
  -> b.trx_query ブロッキングクエリ
  -> から
  -> information_schema.innodb_lock_waits w
  -> INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  -> INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
  -> ) 一時、
  -> information_schema.PROCESSLIST p、
  -> PERFORMANCE_SCHEMA.events_statements_current c、
  -> PERFORMANCE_SCHEMA.threads t
  -> どこ
  -> tmp.blocking_thread = p.id
  -> AND t.thread_id = c.THREAD_ID
  -> かつ t.PROCESSLIST_ID = p.id \G
************************** 1. 行 ****************************
 watching_trx_state: ロック待機
  待機中のトランザクションID: 6180
  待機スレッド: 19
   待機クエリ: stu_tb を更新し、stu_name を 'wang' に設定し、stu_id を 1006 に設定します。
ブロッキングtrx状態: 実行中
  ブロッキング_trx_id: 6177
  ブロックスレッド: 20
  ブロッキングクエリ: NULL
 ブロッキングSQLテキスト: 更新のためにstu_id = 1006のstu_tbから*を選択
   ブロッキングホスト: ローカルホスト

上記の結果はより明確です。ブロックしているトランザクションとブロックされたトランザクションによって実行されたステートメントを明確に確認できるため、トラブルシューティングや、ブロックされたセッションを強制終了できるかどうかの確認に役立ちます。

3. 長いトランザクションを監視する

実際の作業では、長いトランザクションを監視し、30 秒などのしきい値を定義する必要があります。実行に 30 秒以上かかるトランザクションは長いトランザクションであり、記録して警告し、管理担当者に処理を促す必要があります。監視スクリプトは以下のとおりです。これを参照して、必要に応じて変更してください。

#!/bin/bash
# ---------------------------------------------------------------------------------
# ファイル名: long_trx.sh
# 説明: 長いトランザクションを監視する
# リビジョン: 1.0
# 日付: 2019/09/16
# 著者: 王

/usr/local/mysql/bin/mysql -N -uroot -pxxxxxx -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx 内部結合
情報スキーマ.PROCESSLIST b
a.TRX_MYSQL_THREAD_ID=b.id かつ b.command = 'Sleep' の場合
内部結合 performance_schema.threads c ON b.id = c.PROCESSLIST_ID
内部結合 performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | 読み取り中 ABCDEFGH
する
 [ "$C" -gt 30 ]の場合
   それから
   echo $(date +"%Y-%m-%d %H:%M:%S")
   echo "プロセスID[$D] $E@$F、db[$G] のトランザクション時間 $C SQL を保持:$H"
 フィ
完了 >> /tmp/longtransaction.txt

簡単に説明すると、ここでの -gt 30 は 30 秒を意味します。30 秒を超えると長いトランザクションとみなされます。実際のニーズに応じてカスタマイズできます。スケジュールされたタスクにスクリプトを追加すると、実行されます。

要約:

この記事では、主にロングトランザクションに関連する内容、ロングトランザクションの見つけ方、ロングトランザクションの処理方法、ロングトランザクションの監視方法について紹介します。皆さんの中には、取引についてあまり知らない人もいるかもしれません。この記事がお役に立てば幸いです。この記事では、クエリトランザクションに関連するステートメントを多数リストしているため、次のように要約されます。

# 実行中のすべてのトランザクションとその実行時間を照会する select t.*,to_seconds(now())-to_seconds(t.trx_started) idol_time from INFORMATION_SCHEMA.INNODB_TRX t \G

# トランザクションの詳細と実行されたSQLを照会する
information_schema.innodb_trx a から now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec、b.id、b.user、b.host、b.db、d.SQL_TEXT を選択し、information_schema.PROCESSLIST b を内部結合します。
a.TRX_MYSQL_THREAD_ID=b.id かつ b.command = 'Sleep' の場合
内部結合 performance_schema.threads c ON b.id = c.PROCESSLIST_ID
内部結合 performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

# トランザクションによって実行されたすべての履歴SQLレコードをクエリする SELECT
 ps.id 'プロセスID'、
 追伸：ユーザー様、
 追伸：ホスト、
 esh.EVENT_ID、
 trx.trx_started、
 esh.event_name 'イベント名',
 esh.sql_text 'SQL'、
 追伸時間 
から
 PERFORMANCE_SCHEMA.events_statements_history esh
 PERFORMANCE_SCHEMA.threads th を esh.thread_id = th.thread_id に結合します。
 information_schema.PROCESSLIST ps を ps.id = th.processlist_id に結合します。
 information_schema.innodb_trx trx を trx.trx_mysql_thread_id = ps.id に左結合します 
どこ
 trx.trx_id は NULL ではありません 
 かつ ps.USER != 'SYSTEM_USER' 
注文する
 esh.EVENT_ID;
 
 # 単純なクエリトランザクションロック select * from sys.innodb_lock_waits\G
 
 #トランザクションロックの詳細を照会するSELECT
 一時*、
 c.SQL_Text ブロッキングSQLテキスト、
 p.HOST ブロッキングホスト
から
 （
 選択
  r.trx_state 監視中のtrx_state、
  r.trx_id 待機中のtrx_id、
  r.trx_mysql_thread_Id 待機スレッド、
  r.trx_query 待機クエリ、
  b.trx_state ブロッキングtrx_state、
  b.trx_id ブロッキングtrx_id、
  b.trx_mysql_thread_id ブロッキングスレッド、
  b.trx_query ブロッキングクエリ
 から
  information_schema.innodb_lock_waits w
  内部結合 information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  内部結合 information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
 ) 一時、
 情報スキーマ.PROCESSLIST p、
 PERFORMANCE_SCHEMA.events_statements_current c、
 PERFORMANCE_SCHEMA.スレッド t
どこ
 tmp.blocking_thread = p.id 
 かつ t.thread_id = c.THREAD_ID 
 かつ t.PROCESSLIST_ID = p.id \G

上記は、MySQL ロングトランザクションの詳細な理解に関する内容です。MySQL ロングトランザクションの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: