MySQLのロックの種類とその競合

2024/06/20 GMOペパボ

Transcript

1. MySQLのロックの種類と その競合 2024/06/20 GMOペパボ
2. TL;DR • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さ くなる
3. こんにちは • yoku0825＠とある企業のDBA o オラクれない oポスグれない o マイエスキューエる • 生息域 o Twitterだったもの: yoku0825 o Blog: 日々の覚書 o 日本MySQLユーザ会 o MySQL Casual oOracle ACE Pro: MySQL
4. メタデータロック • テーブル構造に対してかけるロック o 構造に対してかけるロックなのでデータ量によらず一定速度 o ロックの速度が一定というだけでロック後の処理の速度はまた別問題 • ロックの期間はトランザクションの終了まで • COMMITまたはROLLBACKするまでロックは残留する
5. メタデータロック • ALTER TABLE中にDROP TABLE o ALTERが終わり次第DROPされるであろう • DROP TABLE中にALTER TABLE oDROPが終わり次第Table doesn't existのエラーになるであろう • これらが待たされるのは直観的 o排他メタデータロック同士の競合
6. メタデータロック • SELECTが走っている真っ最中にDROP TABLE o まあクエリが終わるまで待たされるかな？ • UPDATEが走っている真っ最中にDROP TABLE oまあクエリが終わるまで待たされるかな？ • BEGIN; SELECT が終わってCOMMITしてない間にDROP TABLE o どう思います？
7. メタデータロック mysql84 18> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql84 18> SELECT name, population FROM country WHERE code = 'JPN'; +-------+------------+ | name | population | +-------+------------+ | Japan | 126714000 | +-------+------------+ 1 row in set (0.00 sec) mysql84 19> DROP TABLE country; ### もし待たされなかったとしたら、↓のSELECTはREPEATABLE-READで何が見えるべき？ mysql84 18> SELECT name, population FROM country WHERE code = 'JPN'; ### ??
8. メタデータロック • SELECTが走っている真っ最中にDROP TABLE o 待たされる • UPDATEが走っている真っ最中にDROP TABLE o待たされる • BEGIN; SELECT が終わってCOMMITしてない間にDROP TABLE o 待たされる
9. メタデータロック • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタ データロック」が必要 o オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」 を取った後に「共有メタデータロック」にフォールバックして最後に もう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 o ALGORITHM=COPYの場合は「共有リードメタデータロック」だけと 競合しないメタデータロックという状態になる（謎い）
10. メタデータロック • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必 要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER TABLE/DROP TABLEが自分を追い越さないようにするた めにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタ データロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクション の中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い
11. メタデータロック(ALTER単体) 拡大して説明するものなのでこのまま見るものではない
12. メタデータロック(DML単体) 拡大して説明するものなのでこのまま見るものではない
13. メタデータロック(混ぜるな危険) 拡大して説明するものなのでこのまま見るものではない
14. メタデータロックのデモ ### connection_1 BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_2 ALTER TABLE t1 Engine = InnoDB; ### connection_3 SELECT * FROM t1 LIMIT 1;
15. メタデータロックのデモ ### connection_1 SHOW PROCESSLIST; SELECT object_type, object_name, lock_type, lock_duration, lock_status, processlist_id FROM performance_schema.metadata_locks JOIN performance_schema.threads ON owner_thread_id = thread_id WHERE object_schema = 'd1';
16. メタデータロックのデモ ### connection_1 COMMIT; BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_3 SELECT * FROM t1 LIMIT 1; -- again ### connection_1 SHOW PROCESSLIST; SELECT object_type, object_name, lock_type, lock_duration, lock_status, processlist_id FROM performance_schema.metadata_locks JOIN performance_schema.threads ON owner_thread_id = thread_id WHERE object_schema = 'd1';
17. メタデータロックのデモ ### connection_1 BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_2 ALTER TABLE t1 ADD COLUMN dt DATETIME, ALGORITHM= INPLACE; ### connection_3 SELECT * FROM t1 LIMIT 1; -- dt column ..? ### connection_1 COMMIT; BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_3 SELECT * FROM t1 LIMIT 1; -- dt column ..?
18. メタデータロックのデモ ### connection_1 BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_2 ALTER TABLE t1 ADD COLUMN dt2 DATETIME, ALGORITHM= INSTANT; ### connection_3 SELECT * FROM t1 LIMIT 1; -- dt2 column ..? ### connection_1 COMMIT; BEGIN; SELECT * FROM t1 LIMIT 1;
19. メタデータロック(again) • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタ データロック」が必要 o オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」 を取った後に「共有メタデータロック」にフォールバックして最後に もう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 oALGORITHM=COPYの場合は「共有リードメタデータロック」だけと 競合しないメタデータロックという状態になる（謎い）
20. メタデータロック(again) • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必 要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER TABLE/DROP TABLEが自分を追い越さないようにするた めにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタ データロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクション の中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い
21. メタデータロック (混ぜるな危険 - again)
22. メタデータロック • タイムアウトの閾値は `lock_wait_timeout` , NOT `innodb_lock_wait_timeout` • デフォルトは31536000秒 = 365日 = 1年! • これを小さくすることで「後ろから追いかけてくるトランザク ションを待たせる時間」を短くできるけれど、ALTER TABLE 終了時も同じタイムアウトの値が使われるので、数時間かけて 進めたALTER処理がAbortさせられてしまうリスクはある
23. ひとやすみ
24. InnoDBロック • 行ロックとか呼ばれるけれど実際はインデックスロックなアレ o InnoDBの行データ本体もB+Treeインデックスの形を取る ▪ そのB+TreeのキーになるのがPRIMARY KEYまはたUNIQUE KEY NOT NULL(PKE) • どっちも無ければ暗黙の行ID(6バイトのやつ) • インデックスレコードそれぞれにロックを置いていくので、 ロックをかけるトータルのレコード数がロックにかかる時間に 影響する
25. InnoDBロック • ロックの強さは「排他ロック」, 「共有ロック」, 「ロックな し」の3種 o 更新ステートメントまたは `FOR UPDATE` による排他ロック o `FOR SHARE`, `INSERT INTO .. SELECT ..`, `CREATE TABLE .. AS SELECT ..` またはトランザクション分離レベルによる共有ロック o それ以外はSELECTはロックフリー
26. InnoDBロック • ロックの範囲は「ギャップなしロック」, 「ギャップロック」, 「レ コードロック」の3種 o インデックスレコードそのものだけをロック o インデックスレコードの手前の隙間だけをロック o インデックスレコードとその手前のギャップを同時にロック o InnoDBで単に「レコードロック」と言った場合、ギャップを含む方の意味。 ▪ 我々が直感的にイメージするのは「ギャップなしロック」の方であり違和感に注意 ▪ とはいえ「レコードロック」単体で取る処理はあんまり無い • ネクストキーロックは「レコードロック」+「次のレコードのギャッ プロック」
27. InnoDBロック kenchanのレコードロック pyama86のギャップなしロック tnmtのギャップ(のみ)ロック yoku0825のネクストキーロック (yoku0825のレコードロック + supremumのギャップロック)
28. InnoDBロック • インデックス上には常に2つの疑似レコードが存在する o`infimum` .. 無限小 o `supremum` .. 無限大 • 上から "kenchan" のレコードロック, "pyama86" のギャップなし ロック, "tnmt" のギャップロック, "yoku0825" のネクストキー ロック
29. InnoDBロック kenchanのレコードロック pyama86のギャップなしロック tnmtのギャップ(のみ)ロック yoku0825のネクストキーロック (yoku0825のレコードロック + supremumのギャップロック)
30. InnoDBロック • "kenchan" がレコードロックされている間、無限小とkenchanの 間のギャップはロックされている o ということは無限小とkenchanの間に入る "antipop" はINSERTできない のか？ o できない
31. InnoDBロック ここのギャップがロックされているので antipopは入れない
32. InnoDBロック • "kenchan" がレコードロックされている間、kenchanとpyama86 の間はロックされていない o ということはその間に入る "kurotaky" はINSERTできるのか？ o できる
33. InnoDBロック ここのギャップはロックされていないので kurotakyは入れる
34. InnoDBロック • ここまで説明しておいてなんですけど、レコードロックは単体 ではほとんど使われていない o ほとんどはネクストキーロックまたはギャップなしロック o 同じクエリでもトランザクション分離レベルとそのインデックスがユ ニークかどうかによってバリエーションがある
35. CM MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ
36. InnoDBのインデックス構造 mysql84 11> SHOW CREATE TABLE city¥G *************************** 1. row *************************** Table: city Create Table: CREATE TABLE `city` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
37. InnoDBのインデックス構造 +------+---------------------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+---------------------+-------------+----------+------------+ | 1532 | Tokyo | JPN | Tokyo-to | 7980230 | | 1533 | Jokohama [Yokohama] | JPN | Kanagawa | 3339594 | | 1534 | Osaka | JPN | Osaka | 2595674 | | 1535 | Nagoya | JPN | Aichi | 2154376 | | 1536 | Sapporo | JPN | Hokkaido | 1790886 | +------+---------------------+-------------+----------+------------+
38. InnoDBのインデックス構造
39. InnoDBのインデックス構造 WHERE ID = 1533
40. InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886
41. InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886
42. InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886
43. InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886
44. いわゆるInnoDBの二度引き • セカンダリインデックスのツリーから対応するプライマリー キーの値を取り出す • 取り出した値を使ってプライマリーキーのツリーから対応する 行を取り出す • 実はInnoDBのロックも同じ手順でロックをかける
45. InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE
46. InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE
47. InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE
48. InnoDBの二度引きロック • セカンダリキーはそのまま(?) ネクストキーロックを取る • プライマリーキー側は対応する行のギャップなしロックを取る o ネクストキーロックではないので `ID: 1532.5` (整数型だけどお察しく ださい) のようなレコードは(セカンダリインデックスのロックを忘れ たことにすれば)INSERTし得る ▪ 実際問題CountryCodeのロックが実質全ての値をロックしているので実世界の INSERTとしては成立しない
49. InnoDBロックのデモ ### connection_1 BEGIN; SELECT * FROM city WHERE countrycode = 'JPN' AND population = 1790886 FOR UPDATE; SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id);
50. InnoDBロックのデモ ### connection_2 BEGIN; SELECT * FROM city WHERE id = 1532; -- Lock free SELECT * FROM city WHERE id = 1532 FOR UPDATE; -- timeout SELECT * FROM city WHERE id = 1533 FOR UPDATE; -- timeout SELECT * FROM city WHERE id = 3794 FOR UPDATE; -- granted
51. InnoDBロックのデモ ### connection_1 SELECT * FROM city WHERE countrycode = 'USA' FOR UPDATE; ### connection_2 SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id) WHERE lock_status <> 'GRANTED'; SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id) WHERE lock_data LIKE '%USA%';
52. InnoDBロックのデモ ### connection_3 BEGIN; SELECT * FROM city WHERE countrycode = 'USA' FOR UPDATE; -- Lock wait timeout SELECT * FROM city WHERE id = 3793 FOR UPDATE; -- timeout because connection_1 ### connection_2 SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id) WHERE lock_data = '3793';
53. InnoDBロックのデモ ### connection_2 SELECT * FROM city WHERE id = 1532 FOR UPDATE; SHOW ENGINE INNODB STATUS¥G
54. Deadlock detected
55. READ-COMMITTEDの二度引きロック • `transaction_isolation = 'READ-COMMITTED'` の場合はロックの 範囲が減る • セカンダリキーのロックがギャップなしロックに軽減される （前後のギャップがロックフリーになる） • プライマリーキー側は対応する行のギャップなしロックを取っ た後に…
56. CM MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ
57. READ-COMMITTEDの二度引きロック
58. READ-COMMITTEDの二度引きロック
59. READ-COMMITTEDの二度引きロック • `transaction_isolation = 'READ-COMMITTED'` の場合はロックの 範囲が減る • セカンダリキーのロックがギャップなしロックに軽減される （前後のギャップがロックフリーになる） • プライマリーキー側は対応する行のギャップなしロックを取っ た後に 条件にマッチしない行のロックをリリースする o あくまでも 一度ロックを置いてから要らないものだけリリースする
60. READ-COMMITTEDロックのデモ ### connection_1 SET SESSION transaction_isolation = 'READ-COMMITTED'; BEGIN; SELECT * FROM city WHERE countrycode = 'JPN' AND population = 1790886 FOR UPDATE; SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id);
61. READ-COMMITTEDロックのデモ ### connection_2 SET SESSION transaction_isolation = 'READ-COMMITTED'; BEGIN; SELECT * FROM city WHERE id = 1533 FOR UPDATE; -- granted (in Repeatable-Read, blocked) SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id);
62. READ-COMMITTEDロックのデモ ### connection_1 COMMIT AND CHAIN; SELECT * FROM city WHERE countrycode = 'JPN' AND population = 1790886 FOR UPDATE; -- timeout ### connection_2 SELECT processlist_id, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks JOIN performance_schema.threads USING(thread_id);
63. InnoDBロックの粒度を細かくする • セカンダリキーのネクストキーロック + セカンダリキーに対応 するクラスタインデックスのギャップなしロック • ということはセカンダリキーの検索性が上がればロックの範囲 は小さくできる • たとえば `WHERE CountryCode = 'JPN' AND population = 1790886` を仕留める `INDEX(CountryCode, Population)`
64. InnoDBロックの粒度を細かくする
65. InnoDBロックの粒度を細かくする WHERE CountryCode = 'JPN' AND population = 1790886 vs INDEX(CountryCode, Population)
66. Conclusion • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さ くなる
67. Any questions and/or Suggestions?