前に筆者が担当した第145回に続いて、
今回も引き続きMySQL 8.
data_lock_waits
テーブル
MySQL 8.data_
テーブルは、
ロック待ちが発生していると、
mysql> SELECT * FROM performance_schema.data_lock_waits\G *************************** 1. row *************************** ENGINE: INNODB REQUESTING_ENGINE_LOCK_ID: 140492041772440:5:4:2:140491937095392 REQUESTING_ENGINE_TRANSACTION_ID: 3093 REQUESTING_THREAD_ID: 47 REQUESTING_EVENT_ID: 119 REQUESTING_OBJECT_INSTANCE_BEGIN: 140491937095392 BLOCKING_ENGINE_LOCK_ID: 140492041773296:5:4:2:140491937101616 BLOCKING_ENGINE_TRANSACTION_ID: 3092 BLOCKING_THREAD_ID: 48 BLOCKING_EVENT_ID: 25 BLOCKING_OBJECT_INSTANCE_BEGIN: 140491937101616 1 row in set (0.01 sec)
なお、innodb_
を見るのが良いので、
data_
テーブルにつきましては、
sys.innodb_lock_waitsビュー
このviewはdata_
テーブルとdata_
テーブルなどをJOINして、
以下のようなSQLを実行して、
Session1 | Session2 |
---|---|
BEGIN; | |
UPDATE t0 SET col1=9 WHERE id=1; | |
BEGIN; | |
UPDATE t0 SET col1=10000 WHERE id=1;(Waiting) |
その状態で、
mysql> SELECT * FROM sys.innodb_lock_waits\G *************************** 1. row *************************** wait_started: 2021-05-30 01:37:22 wait_age: 00:00:03 wait_age_secs: 3 locked_table: `i`.`t0` locked_table_schema: i locked_table_name: t0 locked_table_partition: NULL locked_table_subpartition: NULL locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 3094 waiting_trx_started: 2021-05-30 01:37:22 waiting_trx_age: 00:00:03 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 8 waiting_query: update i.t0 set col1=10000 where id=1 waiting_lock_id: 140492041772440:5:4:2:140491937095392 waiting_lock_mode: X,REC_NOT_GAP blocking_trx_id: 3092 blocking_pid: 9 blocking_query: NULL blocking_lock_id: 140492041773296:5:4:2:140491937101616 blocking_lock_mode: X,REC_NOT_GAP blocking_trx_started: 2021-05-30 01:34:01 blocking_trx_age: 00:03:24 blocking_trx_rows_locked: 1 blocking_trx_rows_modified: 1 sql_kill_blocking_query: KILL QUERY 9 sql_kill_blocking_connection: KILL 9
このうち項目のいくつかを抜粋して紹介します。
- wait_
age_ secs - ロックが待機された時間
(秒) - locked_
table - ロックされたスキーマを含むテーブル
- locked_
index - ロックされたインデックスの名前
- locked_
type - 待機中のロックのタイプ
- waiting_
query - ロックを待機しているステートメント
- waiting_
lock_ mode - 待機ロックのモード
- waiting_
trx_ rows_ locked - 待機中のトランザクションによってロックされた行数
- waiting_
trx_ rows_ modified - 待機中のトランザクションによって変更された行数
- blocking_
pid - ロックを保持しているprocess_
id - blocking_
query - ブロックしているトランザクションが実行しているステートメント
- しかし、
これはそのステートメントが実行中であればブロックしているステートメントが表示されますが、 すでに流れてしまった (実行が完了しているがcommitされていない) 状態ではNULLであったり、 sys. innodb_ lock_ waitsからSELECTしたタイミングで実行されているステートメントが表示されるのでご注意ください。 - sql_
kill_ blocking_ connection - ロックを保持しているprocess_
idを強制終了するために実行する KILLステートメント - どのセッションが原因でロック待機が発生しているのか、
そのprocess_ idやそれを強制終了するためのSQLなど表示してくれます。
ロックの原因となっているSQLを確認する
前述のblocking_
はすでにそのSQLが流れてしまうと確認できないと説明しました。ここではちょっとした工夫によりblocking_
準備として、
mysql> UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_statements_history_long'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
このevents_
N個と表したのはデフォルトでは-1
- max_
connections - table_
definition_ cache - table_
open_ cache
これにより100、
以下、
mysql> show variables like '%performance_schema_events_statements_history_long_size%'; +--------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------+-------+ | performance_schema_events_statements_history_long_size | 10000 | +--------------------------------------------------------+-------+
注意として、
そして、blocking_
を表示できるようになります。
SELECT t.PROCESSLIST_ID,concat('KILL ',t.PROCESSLIST_ID,';') Kill_Query, CASE WHEN esh.SQL_TEXT IS NOT NULL THEN esh.SQL_TEXT WHEN esc.SQL_TEXT IS NOT NULL THEN esc.SQL_TEXT ELSE eshl.SQL_TEXT END as BLOCKING_SQLTEXT FROM (SELECT block.BLOCKING_THREAD_ID,block.BLOCKING_EVENT_ID FROM performance_schema.data_lock_waits block LEFT JOIN performance_schema.data_lock_waits req ON block.BLOCKING_THREAD_ID=req.REQUESTING_THREAD_ID AND block.BLOCKING_EVENT_ID=req.REQUESTING_EVENT_ID WHERE req.REQUESTING_THREAD_ID IS NULL AND req.REQUESTING_EVENT_ID IS NULL GROUP BY 1,2 ) wait JOIN performance_schema.threads t ON wait.BLOCKING_THREAD_ID=t.THREAD_ID LEFT JOIN performance_schema.events_statements_history esh ON wait.BLOCKING_THREAD_ID=esh.THREAD_ID AND (wait.BLOCKING_EVENT_ID - 1)=esh.EVENT_ID LEFT JOIN performance_schema.events_statements_current esc ON wait.BLOCKING_THREAD_ID=esc.THREAD_ID AND (wait.BLOCKING_EVENT_ID - 1)=esc.EVENT_ID LEFT JOIN performance_schema.events_statements_history_long eshl ON wait.BLOCKING_THREAD_ID=eshl.THREAD_ID AND (wait.BLOCKING_EVENT_ID - 1)=eshl.EVENT_ID;
先ほど実行した例を用いて、
+----------------+------------+---------------------------------+ | PROCESSLIST_ID | Kill_Query | BLOCKING_SQLTEXT | +----------------+------------+---------------------------------+ | 9 | KILL 9; | UPDATE t0 SET col1=9 WHERE id=1 | +----------------+------------+---------------------------------+
このように、blocking_
が表示されるので、
こちらのSQLの例では、
まとめ
第145回と今回で、
もし、