Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

2024/06/20 GMOペパボ

yoku0825

July 17, 2024
Tweet

More Decks by yoku0825

Other Decks in Technology

Transcript

  1. TL;DR • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o

    ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さ くなる
  2. こんにちは • yoku0825@とある企業のDBA o オラクれない oポスグれない o マイエスキューエる • 生息域

    o Twitterだったもの: yoku0825 o Blog: 日々の覚書 o 日本MySQLユーザ会 o MySQL Casual oOracle ACE Pro: MySQL
  3. メタデータロック • ALTER TABLE中にDROP TABLE o ALTERが終わり次第DROPされるであろう • DROP TABLE中にALTER

    TABLE oDROPが終わり次第Table doesn't existのエラーになるであろう • これらが待たされるのは直観的 o排他メタデータロック同士の競合
  4. メタデータロック 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'; ### ??
  5. メタデータロック • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタ データロック」が必要 o

    オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」 を取った後に「共有メタデータロック」にフォールバックして最後に もう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 o ALGORITHM=COPYの場合は「共有リードメタデータロック」だけと 競合しないメタデータロックという状態になる(謎い)
  6. メタデータロック • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必 要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER

    TABLE/DROP TABLEが自分を追い越さないようにするた めにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタ データロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクション の中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い
  7. メタデータロックのデモ ### connection_1 BEGIN; SELECT * FROM t1 LIMIT 1;

    ### connection_2 ALTER TABLE t1 Engine = InnoDB; ### connection_3 SELECT * FROM t1 LIMIT 1;
  8. メタデータロックのデモ ### 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';
  9. メタデータロックのデモ ### 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';
  10. メタデータロックのデモ ### 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 ..?
  11. メタデータロックのデモ ### 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;
  12. メタデータロック(again) • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタ データロック」が必要 o

    オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」 を取った後に「共有メタデータロック」にフォールバックして最後に もう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 oALGORITHM=COPYの場合は「共有リードメタデータロック」だけと 競合しないメタデータロックという状態になる(謎い)
  13. メタデータロック(again) • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必 要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER

    TABLE/DROP TABLEが自分を追い越さないようにするた めにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタ データロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクション の中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い
  14. メタデータロック • タイムアウトの閾値は `lock_wait_timeout` , NOT `innodb_lock_wait_timeout` • デフォルトは31536000秒 =

    365日 = 1年! • これを小さくすることで「後ろから追いかけてくるトランザク ションを待たせる時間」を短くできるけれど、ALTER TABLE 終了時も同じタイムアウトの値が使われるので、数時間かけて 進めたALTER処理がAbortさせられてしまうリスクはある
  15. InnoDBロック • 行ロックとか呼ばれるけれど実際はインデックスロックなアレ o InnoDBの行データ本体もB+Treeインデックスの形を取る ▪ そのB+TreeのキーになるのがPRIMARY KEYまはたUNIQUE KEY NOT

    NULL(PKE) • どっちも無ければ暗黙の行ID(6バイトのやつ) • インデックスレコードそれぞれにロックを置いていくので、 ロックをかけるトータルのレコード数がロックにかかる時間に 影響する
  16. InnoDBロック • ロックの強さは「排他ロック」, 「共有ロック」, 「ロックな し」の3種 o 更新ステートメントまたは `FOR UPDATE`

    による排他ロック o `FOR SHARE`, `INSERT INTO .. SELECT ..`, `CREATE TABLE .. AS SELECT ..` またはトランザクション分離レベルによる共有ロック o それ以外はSELECTはロックフリー
  17. InnoDBロック • ロックの範囲は「ギャップなしロック」, 「ギャップロック」, 「レ コードロック」の3種 o インデックスレコードそのものだけをロック o インデックスレコードの手前の隙間だけをロック

    o インデックスレコードとその手前のギャップを同時にロック o InnoDBで単に「レコードロック」と言った場合、ギャップを含む方の意味。 ▪ 我々が直感的にイメージするのは「ギャップなしロック」の方であり違和感に注意 ▪ とはいえ「レコードロック」単体で取る処理はあんまり無い • ネクストキーロックは「レコードロック」+「次のレコードのギャッ プロック」
  18. InnoDBロック • インデックス上には常に2つの疑似レコードが存在する o`infimum` .. 無限小 o `supremum` .. 無限大

    • 上から "kenchan" のレコードロック, "pyama86" のギャップなし ロック, "tnmt" のギャップロック, "yoku0825" のネクストキー ロック
  19. 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)
  20. 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 | +------+---------------------+-------------+----------+------------+
  21. InnoDBの二度引きロック • セカンダリキーはそのまま(?) ネクストキーロックを取る • プライマリーキー側は対応する行のギャップなしロックを取る o ネクストキーロックではないので `ID: 1532.5`

    (整数型だけどお察しく ださい) のようなレコードは(セカンダリインデックスのロックを忘れ たことにすれば)INSERTし得る ▪ 実際問題CountryCodeのロックが実質全ての値をロックしているので実世界の INSERTとしては成立しない
  22. 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);
  23. 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
  24. 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%';
  25. 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';
  26. InnoDBロックのデモ ### connection_2 SELECT * FROM city WHERE id =

    1532 FOR UPDATE; SHOW ENGINE INNODB STATUS¥G
  27. READ-COMMITTEDの二度引きロック • `transaction_isolation = 'READ-COMMITTED'` の場合はロックの 範囲が減る • セカンダリキーのロックがギャップなしロックに軽減される (前後のギャップがロックフリーになる)

    • プライマリーキー側は対応する行のギャップなしロックを取っ た後に 条件にマッチしない行のロックをリリースする o あくまでも 一度ロックを置いてから要らないものだけリリースする
  28. 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);
  29. 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);
  30. 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);
  31. Conclusion • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o

    ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さ くなる