MySQL Casual Talks #6 LT

next-key lock id:karupanerura

だれ？

❖ id:karupanerura ❖ Perl/JS/Java/MySQL/... ❖ Gotanda.pm author

❖ 9月に2回目やるよ ❖ YAPC::Asia 2014

❖ perl5 meta programming ❖ Web Application Engineer

みなさん

今日も元気に

Transactionかけてますか！

Transaction分離レベル(1)❖ READ UNCOMMITED

❖ COMMITされていないデータが取れるよ

❖ 並列性能は最強だがデータの一貫性は保証されない

❖ READ COMMITED

❖ COMMITされたデータがその瞬間から取れるよ

❖ 他のTransactionの変更も読む事になる

❖ 一貫性は保証されないが若干まともになる

Transaction分離レベル(2)❖ SERIALIZE

❖ 同時に複数のTransactionは走らせないよ

❖ 一貫性は保証されるけど並列性能が下がる

❖ REPEATABLE READ

❖ 同じTransaction内では常に同じデータが取れるよ

❖ 他のTransactionの更新を無視する

❖ ファントムリードが発生する

ファントムリード？

ファントムリード

❖ 他のTransactionによって挿入されたデータが読めてしまう現象

❖ さっきまで無かった筈の行がTransactionの途中から出現したりする

❖ REPEATABLE READの場合、挿入は影響してしまう

これは困った！

SERIALIZEを使うしかないのか！？

InnoDBの場合

InnoDBの場合

next-key lockがあるよ！

MySQLのlock❖ record lock

❖ 単一のindex recordのロック

❖ e.g.) PRIMARY KEYが1のヤツ

❖ gap lock

❖ index recordの間、先頭、末尾のロック

❖ e.g. 1) PRIMARY KEYが1~3の間のロック

❖ e.g. 2) PRIMARY KEYが3以上のロック

next-key locking

❖ REPEATABLE READで一貫性を保証するためのlock

❖ record lockとその前のgap lockを取得する

❖ gapのlockによって行の挿入を防ぐ

勘所

❖ index record lockである

❖ covering indexの場合などはlock範囲が異なる ❖ http://blog.kamipo.net/entry/2013/12/03/235900

❖ クエリによってロック範囲が細かく異なる

❖ MySQLの公式のドキュメントが詳しい ❖ InnoDB 内で各種 SQL ステートメントによって設定されるロック

❖ http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-locks-set.html

図解(FOR UPDATEなケース)

schema

mysql> CREATE TABLE `mysqlcasual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` int(11) NOT NULL, `col2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_col1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

datamysql> SELECT * FROM mysqlcasual; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | 2 | 0 | | 2 | 4 | 0 | | 3 | 6 | 0 | | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | +----+------+------+

WHERE col1 = 6+-----+------+------+ | id | col1 | col2 | +-----+------+------+ | 1 | 2 | 0 | | 2 | 4 | 0 | | 3 | 6 | 0 | | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | +-----+------+------+

WHERE col1 > 6+-----+------+------+ | id | col1 | col2 | +-----+------+------+ | 1 | 2 | 0 | | 2 | 4 | 0 | | 3 | 6 | 0 | |(gap)| (gap)| (gap)| | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | |(gap)| (gap)| (gap)| +-----+------+------+

WHERE col1 >= 6+-----+------+------+ | id | col1 | col2 | +-----+------+------+ | 1 | 2 | 0 | | 2 | 4 | 0 | |(gap)| (gap)| (gap)| | 3 | 6 | 0 | | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | |(gap)| (gap)| (gap)| +-----+------+------+

WHERE col1 < 6+-----+------+------+ | id | col1 | col2 | +-----+------+------+ |(gap)| (gap)| (gap)| | 1 | 2 | 0 | | 2 | 4 | 0 | |(gap)| (gap)| (gap)| | 3 | 6 | 0 | | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | +-----+------+------+

WHERE col1 <= 6+-----+------+------+ | id | col1 | col2 | +-----+------+------+ |(gap)| (gap)| (gap)| | 1 | 2 | 0 | | 2 | 4 | 0 | | 3 | 6 | 0 | |(gap)| (gap)| (gap)| | 4 | 8 | 0 | | 5 | 10 | 0 | | 6 | 12 | 0 | | 7 | 14 | 0 | | 8 | 16 | 0 | +-----+------+------+

まとめ

まとめ❖ 試してみた感じこういう挙動だと思った

❖ わかってないかもしれない

❖ そろそろtwitterでマサカリが飛ぶ頃

❖ SHOW ENGINE INNODB STATUS ¥G

❖ 読むの経験と力が要りそう

❖ 鍵本片手に読まないとよくわからない

❖ カジュアルに可視化したい

おわり