MySQLのデッドロックチェック処理の通常の方法

#ステップ 1: ウィンドウ 1mysql> start transaction;
mysql> update aa set name='aaa' where id = 1;
 
#ステップ 2: ウィンドウ 2mysql> start transaction;
mysql> bb を更新し、name='bbb' を設定します (id = 1)。
 
#ステップ 3: ウィンドウ 1mysql> update bb set name='bbb';

#ステップ 4: ウィンドウ 3#自動送信するかどうかmysql> show variables like 'autocommit';
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| 自動コミット | オン |
+---------------+-------+
 
#現在の接続を表示しますmysql> show processlist;
mysql> 完全なプロセスリストを表示します。
mysql> INFORMATION_SCHEMA.PROCESSLIST から * を選択;
+----+------+-----------+---------+-------+-------+-------+-----------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+------+-----------+---------+-------+-------+-------+-----------------+
| 4 | ルート | ローカルホスト | テスト | スリープ | 244 | | NULL |
| 5 | ルート | ローカルホスト | テスト | スリープ | 111 | | NULL |
| 6 | root | localhost | NULL | クエリ | 0 | init | プロセスリストを表示 |
+----+------+-----------+---------+-------+-------+-------+-----------------+
 
 
#現在ロックされているトランザクションを表示します (ロック要求がタイムアウトすると見つかりません)
mysql> INFORMATION_SCHEMA.INNODB_LOCKS から * を選択;
+------------------+-------------+------------+-------------+-------------+-----------------+-------------+-----------+----------------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+------------+-------------+-------------+-----------------+-------------+-----------+----------------+
| 130718495:65:3:4 | 130718495 | X | レコード | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
| 130718496:65:3:4 | 130718496 | X | レコード | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 |
+------------------+-------------+------------+-------------+-------------+-----------------+-------------+-----------+----------------+
 
#現在ロックを待機しているトランザクションを表示します (ロック要求がタイムアウトすると見つかりません)
mysql> INFORMATION_SCHEMA.INNODB_LOCK_WAITS から * を選択; 
+-------------------+-------------------+------------------+------------------+
| 要求中の trx_id | 要求されたロック ID | ブロック中の trx_id | ブロック中のロック ID |
+-------------------+-------------------+------------------+------------------+
| 130718499 | 130718499:65:3:4 | 130718500 | 130718500:65:3:4 |
+-------------------+-------------------+------------------+------------------+
 
 
#現在コミットされていないトランザクションを表示します (デッドロック待機がタイムアウトした場合、トランザクションはまだ閉じられていない可能性があります)
mysql> INFORMATION_SCHEMA.INNODB_TRX から * を選択;
+--------------------------------------------------------------------------------------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight |
+-----------+-----------+----------------------+-----------------------+----------------------+------------+
| 130718500 | 実行中 | 2018-03-12 09:28:10 | NULL | NULL | 3 |
| 130718499 | ロック待機 | 2018-03-12 09:27:59 | 130718499:65:3:4 | 2018-03-12 09:32:48 | 5 |
==========================================================================================================
| trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use |
+---------------------+---------------------------------------+---------------------+-------------------+
| 4 | NULL | NULL | 0 |
| 5 | bb を更新、名前を 'bbb' に設定 | 開始インデックス読み取り | 1 |
=========================================================================================================
| trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified |
+-------------------+------------------+-----------------------+-------------------+-------------------+
| 0 | 2 | 360 | 3 | 1 |
| 1 | 4 | 1184 | 4 | 1 |
===========================================================================================================================
| trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error |
+-------------------------+---------------------+-------------------+-------------------+---------+----------------------------+
| 0 | 繰り返し読み取り | 1 | 1 | NULL |
| 0 | 繰り返し読み取り | 1 | 1 | NULL |
===========================================================================================================================
| trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------------------------+---------------------------+------------------+----------------------------+
| 0 | 10000 | 0 | 0 |
| 0 | 10000 | 0 | 0 |
+---------------------------+---------------------------+------------------+----------------------------+
 
 
#アクセスされているテーブルを表示します。mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+------------+------------+
| データベース | テーブル | 使用中 | 名前がロックされています |
+----------+-------+--------+------------+------------+
| テスト | bb | 1 | 0 |
+----------+-------+--------+------------+------------+

#ステップ 3: ウィンドウ 1 (ステップ 3 のロック要求に時間がかかりすぎると、ロックがタイムアウトになり、実行が終了します)
mysql> bbを更新し、名前を'bbb'に設定します。
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。
 
 
#「ウィンドウ 1」のロック要求がタイムアウトする前に、ステップ 5 を実行してデッドロックを発生させます。その後、接続「ウィンドウ 2」の実行が終了し、「ウィンドウ 1」が正常に実行されます。#ステップ 5: ウィンドウ 2mysql> update aa set name='aa';
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

#最新のデッドロック状況を表示しますmysql> SHOW ENGINE INNODB STATUS\G;
...............
------------------------
最近検出されたデッドロック
------------------------
2018-03-12 11:01:06 7ffb4993a700 #発生時刻*** (1) トランザクション: #トランザクション 1
トランザクション 130718515、アクティブ 19 秒開始インデックス読み取り
mysql テーブル使用数 1、ロック数 1 #テーブルがアクセス中 LOCK WAIT 4 ロック構造体、ヒープ サイズ 1184、行ロック 4、undo ログ エントリ 1 #影響を受ける行数 MySQL スレッド ID 5、OS スレッド ハンドル 0x7ffb498f8700、クエリ ID 205 localhost root 更新中 #スレッド/接続ホスト/ユーザー更新 bb セット名 = 'bb' #要求ステートメント*** (1) このロックが許可されるのを待機しています: #次のリソース (ロック位置とロック モード) を待機しています
レコード ロック スペース ID 65 ページ番号 3 n ビット 72 テーブル `test`.`bb` のインデックス `GEN_CLUST_INDEX` トランザクション ID 130718515 ロック モード X 待機中
レコード ロック、ヒープ番号 5 物理レコード: n_fields 5; コンパクト フォーマット; 情報ビット 0
 0: 長さ 6; 16 進数 000000000300; 昇順 ;;
 1: 長さ 6; 16 進数 000007ca9b34; 昇順 4;;
 2: 長さ 7; 16 進数 1f000002092075; asc u;;
 3: 長さ 4; 16 進数 80000001; 昇順 ;;
 4: 長さ 2; 16 進数 6262; asc bb;;
 
*** (2) トランザクション: #トランザクション2
トランザクション 130718516、アクティブ 14 秒開始インデックス読み取り
使用中の MySQL テーブル 1、ロックされているテーブル 1
4 つのロック構造体、ヒープ サイズ 1184、4 つの行ロック、UNDO ログ エントリ 1
MySQL スレッド ID 4、OS スレッド ハンドル 0x7ffb4993a700、クエリ ID 206 ローカルホスト ルート更新中
update aa set name='aa' #要求ステートメント*** (2) ロックを保持: #ロックリソースを保持 レコードロック スペース ID 65 ページ番号 3 n ビット 72 テーブル `test`.`bb` のインデックス `GEN_CLUST_INDEX` trx ID 130718516 lock_mode X
レコード ロック、ヒープ番号 1 物理レコード: n_fields 1; コンパクト フォーマット; 情報ビット 0
 0: 長さ 8; 16 進数 73757072656d756d; asc 上限;;
 
レコード ロック、ヒープ番号 3 物理レコード: n_fields 5; コンパクト フォーマット; 情報ビット 0
 0: 長さ 6; 16 進数 000000000301; 昇順 ;;
 1: 長さ 6; 16 進数 000007ca9b17; 昇順 ;;
 2: 長さ 7; 16 進数 9000000144011e; asc D ;;
 3: 長さ 4; 16 進数 80000002; 昇順 ;;
 4: 長さ 2; 16 進数 6262; 昇順 bb;;
 
レコード ロック、ヒープ番号 5 物理レコード: n_fields 5; コンパクト フォーマット; 情報ビット 0
 0: 長さ 6; 16 進数 000000000300; 昇順 ;;
 1: 長さ 6; 16 進数 000007ca9b34; 昇順 4;;
 2: 長さ 7; 16 進数 1f000002092075; asc u;;
 3: 長さ 4; 16 進数 80000001; 昇順 ;;
 4: 長さ 2; 16 進数 6262; 昇順 bb;;
 
*** (2) このロックが許可されるのを待機しています:
レコード ロック スペース ID 64 ページ番号 3 n ビット 80 テーブル `test`.`aa` のインデックス `GEN_CLUST_INDEX` トランザクション ID 130718516 ロック モード X 待機中
レコード ロック、ヒープ番号 7 物理レコード: n_fields 5、コンパクト フォーマット、情報ビット 0
 0: 長さ 6; 16 進数 000000000200; 昇順 ;;
 1: 長さ 6; 16 進数 000007ca9b33; 昇順 3;;
 2: 長さ 7; 16 進数 1e000001d53057; asc 0W;;
 3: 長さ 4; 16 進数 80000001; 昇順 ;;
 4: 長さ 2; 16 進数 6161; asc aa;;
 
*** トランザクションをロールバックします (2)
...............

#デッドロック レコードには最新のデッドロック情報のみが記録されます。各デッドロック情報をエラー ログに保存するには、次のパラメータを有効にします。
mysql> 'innodb_print_all_deadlocks' のような変数を表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| innodb_print_all_deadlocks | オフ |
+----------------------------+-------+
 
 
#上記の[ステップ 3: ウィンドウ 1]でリソースを要求できない場合、デフォルトの 50 秒後にロック待機タイムアウトが発生します。
mysql> 'innodb_lock_wait_timeout' のような変数を表示します。 
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
 
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。
 
 
#グローバル変数ロック待機タイムアウトを60秒に設定します（新しい接続が有効になります）
#mysql> セッションを innodb_lock_wait_timeout=50 に設定します。 
mysql> グローバル innodb_lock_wait_timeout を 60 に設定します。 
 
 
#上記のテストでは、トランザクション内のステートメントがタイムアウトすると、そのステートメントのみがロールバックされ、トランザクションの整合性が破壊されます。このトランザクションをロールバックするには、次のパラメータを有効にします。
mysql> 'innodb_rollback_on_timeout' のような変数を表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| innodb_rollback_on_timeout | オフ |
+----------------------------+-------+

[mysqld]
ログエラー=/var/log/mysqld3306.log
innodb_lock_wait_timeout=60 #ロック要求タイムアウト（秒）
innodb_rollback_on_timeout = 1 #トランザクション内のステートメントのロック要求がタイムアウトすると、トランザクション全体がロールバックされます innodb_print_all_deadlocks = 1 #すべてのデッドロックはエラーログに保存されます

#ブロックされたセッションを手動で削除する場合は、Command='Sleep'、State なし、Info なし、trx_weight が最小のセッションを削除します。
プロセスリストを表示します。
INFORMATION_SCHEMA.INNODB_TRX から trx_mysql_thread_id、trx_state、trx_started、trx_weight を選択します。