LT：MySQLのギャップロックとネクストキーロックについて

2019年4月19日に開催された「中国地方DB勉強会 in 沖縄」のLTでの発表資料です。
https://dbstudychugoku.connpass.com/event/127067/

Transcript

1. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQLのギャップロックと ネクストキーロックについて Yoshiaki Yamasaki / 山﨑 由章 MySQL Global Business Unit MySQL Senior Solution Engineer updated : 2019/04/21
2. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Safe Harbor Statement 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。 また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするも のではない為、購買決定を行う際の判断材料になさらないで下さい。 オラクル製品に関して記載されている機能の開発、リリースおよび時期については、 弊社の裁量により決定されます。 2
3. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. LTの目的 • MySQLのギャップロック、ネクストキーロックに対する誤解を解く • MySQLのギャップロック、ネクストキーロックの必要性を理解してもらう 3
4. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. LTの目的 • MySQLのギャップロック、ネクストキーロックに対する誤解を解く – 他DBと比較する場合、REPEATABLE READ同士で比較すればMySQLも ギャップロックを取得しない(≒ネクストキーロックも取得しない) • MySQLのギャップロック、ネクストキーロックの必要性を理解してもらう – ロック方式でファントムリードを防ぐために使われる(※) 4 ※SELECTのみのトランザクションであれば、ロック方式ではなくMVCC(Multi Version Concurrency Control)により ファントムリードを防げるという話もありますが、今回のLTではMVCCの説明までは含まれていません。 またどこかの機会でMVCCやアプリ視点の話も含めた説明が出来ればとも思っています。
5. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 5 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
6. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 6 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
7. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. InnoDBのレコードレベルのロックの種類 • レコードロック – インデックスレコードに対するロック – テーブルにインデックスが定義されていなくてもレコードロックは 常にインデックスをロックする • 主キーがクラスターインデックスになっていることが関係しているが詳細な説明はここでは割愛 • ギャップロック – 後述 • ネクストキーロック – 後述 7 ※MySQL 5.6 リファレンスマニュアル / 14.2.6 InnoDB のレコード、ギャップ、およびネクストキーロック https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html
8. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 8 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
9. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. ギャップロックとは？ • 以下のギャップに対するロック – レコードとレコードの間に対するロック – インデックスの先頭レコードの前に対するロック – インデックスの最後のレコードの後に対するロック 9
10. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. ギャップのイメージ図 • ID列にインデックスがあり、ID=10、20、30、40の行が存在する場合、 以下のギャップが存在する – レコードとレコードの間に対するロック（※1） – インデックスの先頭レコードの前に対するロック（※2） – インデックスの最後のレコードの後に対するロック（※3） 10 ID=30 ID=10 ID=20 ID=40 ※2 ※1 ※3 ※1 ※1
11. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. ネクストキーロックとは？ • レコードに対するロックと、その手前のギャップロックを合わせたもの • ネクストキーロックにより、ファントムリード(後述)が防げる 11 ID=30 ID=10 ID=20 ID=40 ID=10に対する ネクストキーロック （ID=10より小さい値も ロックに含まれる） 無限大の値に対する ネクストキーロック ID=30に対する ネクストキーロック
12. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 12 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
13. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. ギャップロックの発生を抑える方法 • トランザクション分離レベルをREAD COMMITTEDに変更すれば、 ギャップロックを取得しない 13 MySQL 8.0 Reference Manual / 15.7.1 InnoDB Locking / Gap Locks https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks => Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking. There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.
14. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. その他の影響 • トランザクション分離レベルをREAD COMMITTEDに変更すれば、 インデックスを使わない更新処理によるロックも行ロックになる (本資料の22～24ページ参照) 14 MySQL 8.0 Reference Manual / 15.7.1 InnoDB Locking / Gap Locks https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks => Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking. There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.
15. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 15 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
16. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルとは？ • トランザクションを複数同時に実行した場合に、それぞれのトランザクション がどのように干渉するかについて4段階で定義したもの • ANSI/ISO SQL標準では以下の4つの分離レベルが定義されている – READ UNCOMMITTED (コミットしていないデータも読取る) – READ COMMITTED (コミット済みデータを読取る) – REPEATABLE READ (反復可能読取り) – SERIALIZABLE (直列化可能) 16
17. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルによる違い • それぞれのトランザクション分離レベルでは、以下の問題が発生する 可能性がある 17 分離レベル ダーティーリード ファジーリード ファントムリード READ UNCOMMITTED 〇 〇 〇 READ COMMITTED × 〇 〇 REPEATABLE READ × × 〇 SERIALIZABLE × × ×
18. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルによる違い • ダーティーリード – トランザクションBでコミットしていない変更データをトランザクションAで 読み取ってしまう • ファジーリード(ノンリピータブルリード) – トランザクションAで同じデータを繰り返し読み取った時に、違う結果を 読み取ってしまう • ファントムリード – トランザクションAである範囲のレコードに対して繰り返し読み取りを行った時に、 トランザクションBで追加/削除したデータが反映されてしまう 18
19. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルによる違い • ダーティーリード – トランザクションBでコミットしていない変更データをトランザクションAで 読み取ってしまう • ファジーリード(ノンリピータブルリード) – トランザクションAで同じデータを繰り返し読み取った時に、違う結果を 読み取ってしまう • ファントムリード – トランザクションAである範囲のレコードに対して繰り返し読み取りを行った時に、 トランザクションBで追加/削除したデータが反映されてしまう ⇒範囲に対して他のトランザクションからデータが追加/削除されないことを 保証するためには、範囲に対するロック(ギャップロック)が必要 19
20. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. InnoDBにおけるトランザクション分離レベル • InnoDBのデフォルト分離レベルはREPEATABLE READ • REPEATABLE READであるが、ファントムリードも発生しない実装になっている – トランザクション内でノンロッキングリードとロッキングリードが混在した場合には ファントムリードが発生する可能性もあるので注意(※) • 任意で他の分離レベルにも変更できる – 参考：MySQL道普請便り：第47回 トランザクション分離レベルを変更する https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0047 20 ※参考情報： 漢のコンピュータ道：InnoDBのREPEATABLE READにおけるLocking Readについての注意点 http://nippondanji.blogspot.com/2013/12/innodbrepeatable-readlocking-read.html
21. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ 21 InnoDBのレコードレベルのロックの種類 ギャップロック、ネクストキーロックとは？ ギャップロックの発生を抑える方法 トランザクション分離レベルとは？ InnoDBのレコードロックの注意事項 1 2 3 4 5
22. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. InnoDBのレコードロックの注意事項 • デフォルトのトランザクション分離レベルであるREPEATABLE READの場合、 インデックスを使わない更新処理によるロックは、テーブルの全件を ロックすることになり、結果テーブルロックになってしまう – 更新処理の内部処理でアクセスした行に対してロックを取得するため 22
23. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 例）テーブルロックになってしまう例 • 環境 23 mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> select * from world.City limit 3; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | +----+----------------+-------------+---------------+------------+ 3 rows in set (0.00 sec) mysql> show create table world.City¥G *************************** 1. row *************************** Table: City Create Table: CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) 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=latin1 1 row in set (0.00 sec)
24. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 例）テーブルロックになってしまう例 • 実行例 24 緑色：トランザクション1 黄色：トランザクション2 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> update world.City set Population=186800 where Name='Herat'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql>mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> mysql> mysql> update world.City set Population=237500 where ID=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction インデックスを使わずにID=3の行を更新 トランザクション1がID=3以外の行に対してもロックを取得して いるため、ID=1の行に対する更新処理もロック待ちしてしまう READ COMMITTEDであれば、 このロック待ちはおこらない 時間軸
25. Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 25