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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Safe Harbor Statement 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするものではない為、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます。 2

Slide 3

Slide 3 text

Slide 4

Slide 4 text

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やアプリ視点の話も含めた説明が出来ればとも思っています。

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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に対するネクストキーロック

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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.

Slide 14

Slide 14 text

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.

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルとは？ • トランザクションを複数同時に実行した場合に、それぞれのトランザクションがどのように干渉するかについて4段階で定義したもの • ANSI/ISO SQL標準では以下の4つの分離レベルが定義されている – READ UNCOMMITTED (コミットしていないデータも読取る) – READ COMMITTED (コミット済みデータを読取る) – REPEATABLE READ (反復可能読取り) – SERIALIZABLE (直列化可能) 16

Slide 17

Slide 17 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルによる違い • それぞれのトランザクション分離レベルでは、以下の問題が発生する可能性がある 17 分離レベルダーティーリードファジーリードファントムリード READ UNCOMMITTED 〇〇〇 READ COMMITTED × 〇〇 REPEATABLE READ × × 〇 SERIALIZABLE × × ×

Slide 18

Slide 18 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. トランザクション分離レベルによる違い • ダーティーリード – トランザクションBでコミットしていない変更データをトランザクションAで読み取ってしまう • ファジーリード(ノンリピータブルリード) – トランザクションAで同じデータを繰り返し読み取った時に、違う結果を読み取ってしまう • ファントムリード – トランザクションAである範囲のレコードに対して繰り返し読み取りを行った時に、トランザクションBで追加/削除したデータが反映されてしまう 18

Slide 19

Slide 19 text

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. InnoDBのレコードロックの注意事項 • デフォルトのトランザクション分離レベルであるREPEATABLE READの場合、インデックスを使わない更新処理によるロックは、テーブルの全件をロックすることになり、結果テーブルロックになってしまう – 更新処理の内部処理でアクセスした行に対してロックを取得するため 22

Slide 23

Slide 23 text

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)

Slide 24

Slide 24 text

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であれば、このロック待ちはおこらない時間軸