磯野ー、MySQLのロック競合を表示しようぜー CfP3本目でタイトルを考えるのに疲れ切っていた 2020/02/10 yoku0825 PHPerKaigi 2020

TL;DR 主にInnoDBのロックを「表示する」方法を紹介します グラフ(not チャート)化するとかしません しても実務上有用ではなかったので ‐ InnoDBのロックが見えるような魔眼の育て方の話もしません ‐ MySQL 8.0からの performance_schema.data_locks はいいぞ ロックの動き自体はそう変わっていないので、勉強用には8.0!! ‐ 残念ながら前置きが長い… 1/37

＼こんにちは／ yoku0825＠とある企業のDBA オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 2/37

基礎知識 3/37

基礎知識 MySQLの持つロックの種類 サーバーコアで実装されているロック ‐ ストレージエンジンで実装されているロック 広義にはプラグインで実装されているロック ‐ 4/37

サーバーコアで実装されているロック サーバーコアで実装されているロックの競合はサーバーコアの機能で観測可能 たとえば FLUSH TABLES WITH READ LOCK と競合するクエリーはは SHOW PROCESSLIST で Waiting for global read lock として観測可能 ‐ 逆に、サーバーコアで実装されていないロックの競合はサーバーコアの機能からは 観測ができない 典型的にはInnoDBの行ロック競合は SHOW PROCESSLIST では Sending data としか表示されな い ‐ 5/37

サーバーコアで実装されているロック | stage/sql/System lock | | stage/mysys/Waiting for table level lock | | stage/sql/Waiting for global read lock | | stage/sql/Waiting for backup lock | | stage/sql/Waiting for tablespace metadata lock | | stage/sql/Waiting for schema metadata lock | | stage/sql/Waiting for table metadata lock | | stage/sql/Waiting for stored function metadata lock | | stage/sql/Waiting for stored procedure metadata lock | | stage/sql/Waiting for trigger metadata lock | | stage/sql/Waiting for event metadata lock | | stage/sql/Waiting for commit lock | | stage/sql/User lock | | stage/sql/Waiting for locking service lock | | stage/sql/Waiting for spatial reference system lock | | stage/sql/Waiting for acl cache lock | | stage/sql/Waiting for column statistics lock | | stage/sql/Waiting for resource groups metadata lock | | stage/sql/Waiting for foreign key metadata lock | | stage/sql/Waiting for check constraint metadata lock 6/37

ストレージエンジンで実装されているロック ストレージエンジンで実装されているロックの競合はストレージエンジンの機能で のみ観測可能 InnoDBの information_schema.INNODB_LOCK_WAITS (8.0でなくなりましたが) ‐ innodb_status_output_locks をONにした上で SHOW ENGINE INNODB STATUS の TRANSACTIONS セ クション ‐ ストレージエンジンでの実装がサーバーコアの実装を呼び出している場合もある MyISAMの読み書きロックはサーバーコアのテーブルロックを呼び出している ‐ 7/37

今日のターゲット MySQLの持つロックの種類のうち サーバーコアで実装されているロック ‐ ストレージエンジン InnoDB で実装されているロック 広義にはプラグインで実装されているロック ‐ 8/37

InnoDBで主に問題になるロック 「ネクストキー」ロック 「インデックスレコード」ロック + 「ギャップ」ロック ‐ 「インデックスレコード」ロック 「ギャップ」ロック 「行」ロック クラスターインデックス上の「インデックスレコード」ロック ‐ 9/37

ネクストキーロックとギャップロック 詳しいことは他所に任せて飛ばします LT：MySQLのギャップロックとネクストキーロックについて - Speaker Deck 10/37

InnoDBで主に問題になるロックに対して思考を停止したくなる理由 直観的に「ネクストキー」ロックがわかりづらい 「インデックスレコード」ロック + 「ギャップ」ロックなのは良いとして、 空振りした場合はどこが「インデックスレコード」になるの？ ギャップってどっち側のギャップだっけ？ ‐ セカンダリーインデックスを使ってルックアップしたロック対象は セカンダリー インデックス上でネクストキーロックしてから 行をロックする これがファントムリードを避けるキモなんだけど難しい ‐ 11/37

大事なこと は 12/37

仕組みそのものをすぐに理 解できなくても、外側から 観測して自分の何とかなる 世界に持ってくること 13/37

ゆえに 14/37

ロックを表示 させま SHOW 15/37

InnoDBで主に問題になるロックの表示上の問題 “RECORD LOCK” は単に「ロック」を指しているだけっぽい( 「ネクストキー」 ロックも「インデックスレコード」ロックも「ギャップ」ロックも “RECORD LOCK”) 何も記載がなかった時は「ネクストキー」ロック “locks rec but not gap” は「ネクストキー」ロック - 「ギャップ」ロック = 「イ ンデックスレコード」ロック “locks gap before rec” は「ギャップ」ロック 16/37

脳トレ mysql80 40> SHOW CREATE TABLE card\G *************************** 1. row *************************** Table: card Create Table: CREATE TABLE `card` ( `suite` varchar(8) NOT NULL, `number` tinyint unsigned NOT NULL, PRIMARY KEY (`suite`,`number`), KEY `suite` (`suite`), KEY `number` (`number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql80 40> SELECT * FROM card WHERE number = 10 FOR UPDATE; +---------+--------+ | suite | number | +---------+--------+ | club | 10 | | diamond | 10 | | heart | 10 | | spade | 10 | +---------+--------+ 4 rows in set (0.00 sec) mysql80 41> SELECT * FROM card WHERE suite = 'spade' AND number = 10 FOR UPDATE; ERROR: 1205: Lock wait timeout exceeded; try restarting transaction 17/37

方法1 SET GLOBAL innodb_status_output_locks = ON してから SHOW ENGINE INNODB STATUS mysql80 94> SET GLOBAL innodb_status_output_locks = ON; Query OK, 0 rows affected (0.00 sec) mysql80 94> SHOW ENGINE INNODB STATUS\G .. 18/37

SHOW ENGINE INNODB STATUS ------------ TRANSACTIONS ------------ .. ---TRANSACTION 192750, ACTIVE 283 sec 4 lock struct(s), heap size 1136, 9 row lock(s) MySQL thread id 40, OS thread handle 140237461325568, query id 839 localhost root TABLE LOCK table `d1`.`card` trx id 192750 lock mode IX RECORD LOCKS space id 30 page no 6 n bits 120 index number of table `d1`.`card` trx id 192750 lock_mode X Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 7370616465; asc spade;; Record lock, heap no 24 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 4; hex 636c7562; asc club;; Record lock, heap no 37 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 6865617274; asc heart;; Record lock, heap no 50 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 7; hex 6469616d6f6e64; asc diamond;; RECORD LOCKS space id 30 page no 4 n bits 120 index PRIMARY of table `d1`.`card` trx id 192750 lock_mode X locks rec but not gap Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 5; hex 7370616465; asc spade;; 1: len 1; hex 0a; asc ;; 2: len 6; hex 00000002f08c; asc ;; 3: len 7; hex 82000000d00110; asc ;; .. RECORD LOCKS space id 30 page no 6 n bits 120 index number of table `d1`.`card` trx id 192750 lock_mode X locks gap before rec Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0b; asc ;; 1: len 4; hex 636c7562; asc club;; 19/37

SHOW ENGINE INNODB STATUS(pros) オプション1つだけでOFF/ON切り替え可能 SET GLOBAL innodb_status_output_locks = ON ‐ 実は古い(5.1 Plugin InnoDB)バージョンからずっと使える SET GLOBAL は使わないんだけど5.5とそれ以前でもやり方調べればできる ‐ 20/37

SHOW ENGINE INNODB STATUS(cons) 人間が読むべきものではない気がする Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 1; hex 0a; asc ;; 1: len 5; hex 7370616465; asc spade;; RECORD LOCKS space id 30 page no 4 n bits 120 index PRIMARY of table `d1`.`card` trx id 1 92750 lock_mode X locks rec but not gap Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 5; hex 7370616465; asc spade;; 1: len 1; hex 0a; asc ;; 2: len 6; hex 00000002f08c; asc ;; 3: len 7; hex 82000000d00110; asc ;; 21/37

方法2 information_schemaの INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS あたりを 駆使する MySQL InnoDBにおけるロック競合の解析手順 - SH2の日記 ‐ 5.7とそれ以降なら sys.innodb_lock_waits ビューがほぼ同じ中身を提供してくれる ‐ 8.0では使えない ⇒ 方法3 へ ‐ INNODB_LOCKS は「競合したロック」しか出力してくれないので、競合していない ロックがどれだけあろうとわからない 22/37

information_schemaを駆使する mysql57 55> SELECT * FROM sys.innodb_lock_waits\G *************************** 1. row *************************** wait_started: 2020-02-07 18:48:57 wait_age: 00:00:10 wait_age_secs: 10 locked_table: `d1`.`card` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 6473 waiting_trx_started: 2020-02-07 18:48:57 waiting_trx_age: 00:00:10 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 56 waiting_query: SELECT * FROM card WHERE suite ... ' AND number = '10' FOR UPDATE waiting_lock_id: 6473:51:3:11 waiting_lock_mode: X blocking_trx_id: 6471 blocking_pid: 2 blocking_query: NULL blocking_lock_id: 6471:51:3:11 blocking_lock_mode: X blocking_trx_started: 2020-02-07 18:36:41 blocking_trx_age: 00:12:26 blocking_trx_rows_locked: 9 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 2 sql_kill_blocking_connection: KILL 2 1 row in set, 3 warnings (0.01 sec) 23/37

information_schemaを駆使する(pros) 5.5とそれ以降であれば特別な手順が何もいらない 古い(5.1 Plugin InnoDB)バージョンからずっと使われてきた SQLでアクセスするため、 WHERE 句とかでフィルターがかけやすい これならロック待ちグラフも書ける 24/37

information_schemaを駆使する(cons) 競合しているロックしか見えない 「ロックを表示」ではなく「ロック競合の表示」 ‐ クエリーが走っている最中でないとクエリーが表示されない ロックを取った後アプリケーション側で待機しているようなのはクエリーが見えない ‐ MySQL 8.0ではこのテーブルは廃止になった ⇒ 方法3. へ 25/37

方法3 performance_schema の data_locks を使う 8.0から追加された ‐ 8.0の sys.innodb_lock_waits はこっちのテーブルを使うように変更されて、ほぼ同じ出力を得 られる (= こっちは相変わらず「ロック競合」の表示) ‐ 26/37

performance_schema.data_locks mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | | d1 | card | number | RECORD | X,GAP | GRANTED | 11, 'club' | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 12 rows in set (0.00 sec) 27/37

performance_schema.data_locks(pros) クソ見やすい SQLなのでフィルターもやりやすい performance_schema.threads あたりとJOINすればロックを待っているクエリーも参照できる ‐ performance_schema.events_statements_history (longも) あたりとJOINすれば、実行が終 わったクエリーの情報でも参照できる ‐ ロックが競合してなくても全部表示してくれる 28/37

performance_schema.data_locks(cons) performance_schemaがオフだと使えない performance_schemaのON/OFFはmysqld再起動が必要 ‐ 29/37

見えると何が良いことがあるの？ 例えば「ロック競合を減らすためにトランザクション分離レベルをREAD- COMMITTEDに下げる」 mysql80 96> SET SESSION transaction_isolation = 'READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql80 96> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql80 96> SELECT * FROM card WHERE number = 10 FOR UPDATE; +---------+--------+ | suite | number | +---------+--------+ | club | 10 | | diamond | 10 | | heart | 10 | | spade | 10 | +---------+--------+ 4 rows in set (0.00 sec) 30/37

READ-COMMITTED mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X,REC_NOT_GAP | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 11 rows in set (0.00 sec) 31/37

REPEATABLE-READ mysql80 97> SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks; +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'spade', 10 | | d1 | card | NULL | TABLE | IX | GRANTED | NULL | | d1 | card | number | RECORD | X | GRANTED | 10, 'spade' | | d1 | card | number | RECORD | X | GRANTED | 10, 'club' | | d1 | card | number | RECORD | X | GRANTED | 10, 'heart' | | d1 | card | number | RECORD | X | GRANTED | 10, 'diamond' | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'spade', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'club', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'heart', 10 | | d1 | card | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'diamond', 10 | | d1 | card | number | RECORD | X,GAP | GRANTED | 11, 'club' | +---------------+-------------+------------+-----------+---------------+-------------+---------------+ 12 rows in set (0.00 sec) 32/37

READ-COMMITTED vs REPEATABLE-READ セカンダリーインデックス ( number )がネクストキーロックからインデックスレ コードロックに ギャップロックがなくなった ‐ (11, club) の手前のギャップをロックしないのでだいぶ直観的 ‐ 見えれば比べられる トランザクション分離レベルの話だけじゃなくて、「このクエリーとこっちのクエリーはどっ ちがロックの競合が少なくなるか？」も比べられる ‐ 33/37

ところで 逆に、サーバーコアで実装されていないロックの競合はサーバーコアの機能か らは観測ができない performance_schemaってサーバーコアの実装のはずなんだけど (performance_schemaストレージエンジンはperformance_schema機能で記録 されるデータの格納用ストレージエンジン)どうやってInnoDBのロックに手を出し てるんだろう… 34/37

謎が増えた 35/37

まとめ 主にInnODBのロックを「表示する」方法を紹介しました MySQL 8.0からの performance_schema.data_locks はいいぞ ロックの動き自体はそう変わっていないので、勉強用には8.0!! ‐ 36/37

Any Questions and/or Suggestions? 37/37