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

by yoku0825

Slide 1

Slide 1 text

MySQLのロックの種類とその競合 2024/06/20 GMOペパボ

Slide 2

Slide 2 text

TL;DR • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さくなる

Slide 3

Slide 3 text

こんにちは • yoku0825＠とある企業のDBA o オラクれない oポスグれない o マイエスキューエる • 生息域 o Twitterだったもの: yoku0825 o Blog: 日々の覚書 o 日本MySQLユーザ会 o MySQL Casual oOracle ACE Pro: MySQL

Slide 4

Slide 4 text

メタデータロック • テーブル構造に対してかけるロック o 構造に対してかけるロックなのでデータ量によらず一定速度 o ロックの速度が一定というだけでロック後の処理の速度はまた別問題 • ロックの期間はトランザクションの終了まで • COMMITまたはROLLBACKするまでロックは残留する

Slide 5

Slide 5 text

メタデータロック • ALTER TABLE中にDROP TABLE o ALTERが終わり次第DROPされるであろう • DROP TABLE中にALTER TABLE oDROPが終わり次第Table doesn't existのエラーになるであろう • これらが待たされるのは直観的 o排他メタデータロック同士の競合

Slide 6

Slide 6 text

メタデータロック • SELECTが走っている真っ最中にDROP TABLE o まあクエリが終わるまで待たされるかな？ • UPDATEが走っている真っ最中にDROP TABLE oまあクエリが終わるまで待たされるかな？ • BEGIN; SELECT が終わってCOMMITしてない間にDROP TABLE o どう思います？

Slide 7

Slide 7 text

メタデータロック 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'; ### ??

Slide 8

Slide 8 text

メタデータロック • SELECTが走っている真っ最中にDROP TABLE o 待たされる • UPDATEが走っている真っ最中にDROP TABLE o待たされる • BEGIN; SELECT が終わってCOMMITしてない間にDROP TABLE o 待たされる

Slide 9

Slide 9 text

メタデータロック • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタデータロック」が必要 o オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」を取った後に「共有メタデータロック」にフォールバックして最後にもう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 o ALGORITHM=COPYの場合は「共有リードメタデータロック」だけと競合しないメタデータロックという状態になる（謎い）

Slide 10

Slide 10 text

メタデータロック • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER TABLE/DROP TABLEが自分を追い越さないようにするためにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタデータロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクションの中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い

Slide 11

Slide 11 text

メタデータロック(ALTER単体) 拡大して説明するものなのでこのまま見るものではない

Slide 12

Slide 12 text

メタデータロック(DML単体) 拡大して説明するものなのでこのまま見るものではない

Slide 13

Slide 13 text

メタデータロック(混ぜるな危険) 拡大して説明するものなのでこのまま見るものではない

Slide 14

Slide 14 text

メタデータロックのデモ ### connection_1 BEGIN; SELECT * FROM t1 LIMIT 1; ### connection_2 ALTER TABLE t1 Engine = InnoDB; ### connection_3 SELECT * FROM t1 LIMIT 1;

Slide 15

Slide 15 text

メタデータロックのデモ ### 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';

Slide 16

Slide 16 text

メタデータロックのデモ ### 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';

Slide 17

Slide 17 text

メタデータロックのデモ ### 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 ..?

Slide 18

Slide 18 text

メタデータロックのデモ ### 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;

Slide 19

Slide 19 text

メタデータロック(again) • CREATE TABLE, DROP TABLE, ALTER TABLEは「排他メタデータロック」が必要 o オンラインALTER TABLEと呼ばれるものは「排他メタデータロック」を取った後に「共有メタデータロック」にフォールバックして最後にもう一度「排他メタデータロック」を必要とする ▪ 「共有メタデータロック」の間は読み書き可能 oALGORITHM=COPYの場合は「共有リードメタデータロック」だけと競合しないメタデータロックという状態になる（謎い）

Slide 20

Slide 20 text

メタデータロック(again) • UPDATE/INSERT/DELETEは「共有書き込みメタデータロック」が必要 o 更新ステートメントであっても「共有」メタデータロック o 更新ステートメントは「テーブル定義」を更新するわけではないから o 追いかけてくるALTER TABLE/DROP TABLEが自分を追い越さないようにするためにロックが必要 • クエリの実行中だけでなく「トランザクションの間中」共有メタデータロックを取る o バッチ的にスキャンするクエリとも相性が悪いし、1つのトランザクションの中でデータを取ってから外部のAPIを叩いて待つものとも相性が悪い

Slide 21

Slide 21 text

メタデータロック (混ぜるな危険 - again)

Slide 22

Slide 22 text

メタデータロック • タイムアウトの閾値は `lock_wait_timeout` , NOT `innodb_lock_wait_timeout` • デフォルトは31536000秒 = 365日 = 1年! • これを小さくすることで「後ろから追いかけてくるトランザクションを待たせる時間」を短くできるけれど、ALTER TABLE 終了時も同じタイムアウトの値が使われるので、数時間かけて進めたALTER処理がAbortさせられてしまうリスクはある

Slide 23

Slide 23 text

ひとやすみ

Slide 24

Slide 24 text

InnoDBロック • 行ロックとか呼ばれるけれど実際はインデックスロックなアレ o InnoDBの行データ本体もB+Treeインデックスの形を取る ▪ そのB+TreeのキーになるのがPRIMARY KEYまはたUNIQUE KEY NOT NULL(PKE) • どっちも無ければ暗黙の行ID(6バイトのやつ) • インデックスレコードそれぞれにロックを置いていくので、ロックをかけるトータルのレコード数がロックにかかる時間に影響する

Slide 25

Slide 25 text

InnoDBロック • ロックの強さは「排他ロック」, 「共有ロック」, 「ロックなし」の3種 o 更新ステートメントまたは `FOR UPDATE` による排他ロック o `FOR SHARE`, `INSERT INTO .. SELECT ..`, `CREATE TABLE .. AS SELECT ..` またはトランザクション分離レベルによる共有ロック o それ以外はSELECTはロックフリー

Slide 26

Slide 26 text

InnoDBロック • ロックの範囲は「ギャップなしロック」, 「ギャップロック」, 「レコードロック」の3種 o インデックスレコードそのものだけをロック o インデックスレコードの手前の隙間だけをロック o インデックスレコードとその手前のギャップを同時にロック o InnoDBで単に「レコードロック」と言った場合、ギャップを含む方の意味。 ▪ 我々が直感的にイメージするのは「ギャップなしロック」の方であり違和感に注意 ▪ とはいえ「レコードロック」単体で取る処理はあんまり無い • ネクストキーロックは「レコードロック」+「次のレコードのギャップロック」

Slide 27

Slide 27 text

InnoDBロック kenchanのレコードロック pyama86のギャップなしロック tnmtのギャップ(のみ)ロック yoku0825のネクストキーロック (yoku0825のレコードロック + supremumのギャップロック)

Slide 28

Slide 28 text

InnoDBロック • インデックス上には常に2つの疑似レコードが存在する o`infimum` .. 無限小 o `supremum` .. 無限大 • 上から "kenchan" のレコードロック, "pyama86" のギャップなしロック, "tnmt" のギャップロック, "yoku0825" のネクストキーロック

Slide 29

Slide 29 text

Slide 30

Slide 30 text

InnoDBロック • "kenchan" がレコードロックされている間、無限小とkenchanの間のギャップはロックされている o ということは無限小とkenchanの間に入る "antipop" はINSERTできないのか？ o できない

Slide 31

Slide 31 text

InnoDBロックここのギャップがロックされているので antipopは入れない

Slide 32

Slide 32 text

InnoDBロック • "kenchan" がレコードロックされている間、kenchanとpyama86 の間はロックされていない o ということはその間に入る "kurotaky" はINSERTできるのか？ o できる

Slide 33

Slide 33 text

InnoDBロックここのギャップはロックされていないので kurotakyは入れる

Slide 34

Slide 34 text

InnoDBロック • ここまで説明しておいてなんですけど、レコードロックは単体ではほとんど使われていない o ほとんどはネクストキーロックまたはギャップなしロック o 同じクエリでもトランザクション分離レベルとそのインデックスがユニークかどうかによってバリエーションがある

Slide 35

Slide 35 text

CM MySQL運用・管理［実践］入門〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ

Slide 36

Slide 36 text

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)

Slide 37

Slide 37 text

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 | +------+---------------------+-------------+----------+------------+

Slide 38

Slide 38 text

InnoDBのインデックス構造

Slide 39

Slide 39 text

InnoDBのインデックス構造 WHERE ID = 1533

Slide 40

Slide 40 text

InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886

Slide 41

Slide 41 text

InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886

Slide 42

Slide 42 text

InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886

Slide 43

Slide 43 text

InnoDBのインデックス構造 WHERE CountryCode = 'JPN' AND population = 1790886

Slide 44

Slide 44 text

いわゆるInnoDBの二度引き • セカンダリインデックスのツリーから対応するプライマリーキーの値を取り出す • 取り出した値を使ってプライマリーキーのツリーから対応する行を取り出す • 実はInnoDBのロックも同じ手順でロックをかける

Slide 45

Slide 45 text

InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE

Slide 46

Slide 46 text

InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE

Slide 47

Slide 47 text

InnoDBの二度引きロック WHERE CountryCode = 'JPN' AND population = 1790886 FOR UPDATE

Slide 48

Slide 48 text

InnoDBの二度引きロック • セカンダリキーはそのまま(?) ネクストキーロックを取る • プライマリーキー側は対応する行のギャップなしロックを取る o ネクストキーロックではないので `ID: 1532.5` (整数型だけどお察しください) のようなレコードは(セカンダリインデックスのロックを忘れたことにすれば)INSERTし得る ▪ 実際問題CountryCodeのロックが実質全ての値をロックしているので実世界の INSERTとしては成立しない

Slide 49

Slide 49 text

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);

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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%';

Slide 52

Slide 52 text

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';

Slide 53

Slide 53 text

InnoDBロックのデモ ### connection_2 SELECT * FROM city WHERE id = 1532 FOR UPDATE; SHOW ENGINE INNODB STATUS¥G

Slide 54

Slide 54 text

Deadlock detected

Slide 55

Slide 55 text

READ-COMMITTEDの二度引きロック • `transaction_isolation = 'READ-COMMITTED'` の場合はロックの範囲が減る • セカンダリキーのロックがギャップなしロックに軽減される（前後のギャップがロックフリーになる） • プライマリーキー側は対応する行のギャップなしロックを取った後に…

Slide 56

Slide 56 text

CM MySQL運用・管理［実践］入門〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ

Slide 57

Slide 57 text

READ-COMMITTEDの二度引きロック

Slide 58

Slide 58 text

READ-COMMITTEDの二度引きロック

Slide 59

Slide 59 text

Slide 60

Slide 60 text

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);

Slide 61

Slide 61 text

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);

Slide 62

Slide 62 text

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);

Slide 63

Slide 63 text

InnoDBロックの粒度を細かくする • セカンダリキーのネクストキーロック + セカンダリキーに対応するクラスタインデックスのギャップなしロック • ということはセカンダリキーの検索性が上がればロックの範囲は小さくできる • たとえば `WHERE CountryCode = 'JPN' AND population = 1790886` を仕留める `INDEX(CountryCode, Population)`

Slide 64

Slide 64 text

InnoDBロックの粒度を細かくする

Slide 65

Slide 65 text

InnoDBロックの粒度を細かくする WHERE CountryCode = 'JPN' AND population = 1790886 vs INDEX(CountryCode, Population)

Slide 66

Slide 66 text

Conclusion • MySQLには大きく分けて2つのロックがあります o InnoDBレベルのロック o メタデータロック • 「刺さった」は大体ロックの競合です o ロックの範囲を理解して、ロックの競合が最小になるようにスキーマ/ 操作を考えていくのが唯一にして最大の防御策 o インデックスは選択性を高くすれば高くするほどロックの範囲が小さくなる

Slide 67

Slide 67 text

Any questions and/or Suggestions?