InnoDBデータベースでmysqldumpする時は、single-transactionとskip-lock-tablesのオプションをつけよう

MySQLでInnoDBのデータベースをダンプしようとした時の話。

mysqldump -uhogehoge -pfugafuga -h masyus.work > dump.sql

シンプルに書くとこんな感じのコマンドになるかと思うが、実はちょっとしたLOCKの罠があったので解説してみる。検証したMySQLバージョンは5.7.26、データベースはInnoDBを使用。

暗黙で--lock-tablesが有効になっている

もっと細かく言うと、

--opt

というオプションがデフォルトで有効になっている。--optは複数のオプションを一括で有効にするオプションで、詳細は端折るがその複数のオプションの中の1つが--lock-tablesというわけだ。

参考）

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_opt

mysqldump時に--lock-tablesが有効になっていると、dumpしている最中のテーブルにREAD LOCKがかかる。LOCKには2種類あり、READとWRITEの2つがある：

1. READ LOCKの挙動

READ LOCKを取得したセッション：

• 対象テーブルに対し、読み取り(select)ができる
• 対象テーブルに対し、書き込み(insert/update/delete)ができない

それ以外のセッション：

• 対象テーブルに対し、読み取り(select)ができる
• 対象テーブルに対し、書き込み(insert/update/delete)ができない

2. WRITE LOCKの挙動

WRITE LOCKを取得したセッション：

• 対象テーブルに対し、読み取り(select)ができる
• 対象テーブルに対し、書き込み(insert/update/delete)ができる

それ以外のセッション：

• 対象テーブルに対し、読み取り(select)ができない
• 対象テーブルに対し、書き込み(insert/update/delete)ができない

参考）

https://qiita.com/a-nishimura/items/8325ecad3edb2660abf1

--lock-tablesが有効になっていると困ること

READ LOCKがかかると

mysqldumpしているセッション以外のセッションでinsert/update/deleteができなくなる

という事態が発生する。

たとえばWebサービス運営中に、どうしてもmysqldumpしたいニーズが発生してdumpしたとする。すると、crudのうちのr以外の処理が止まってしまうというわけ。こわっ（もっと怖いのは、他セッションがselectすらできなくなるWRITE LOCKだけど）。

そもそも何故--lock-tablesがデフォルトで指定されているのか

たぶん、MyISAMに寄せた話なのだと思われる。MyISAMのデータベースではトランザクション機能がないため、ある瞬間のtable内情報をスナップショットのようにしてごにょごにょするためにはLOCKさせるしかないのではないかと解釈している。

が、後発で生まれたInnoDBのデータベースの場合は話が変わってくる。トランザクション機能があるからだ。ってことは、

トランザクション特有の何らかのオプションが使えるのであれば、--lock-tablesがそもそも不要になるのではないか？

という話になっていく。下記でさらに踏み込む。

--single-transactionオプションを活用する

mysqldump時に

--single-transaction

を指定すると、REPEATABLE READが適用できるようになる。REPEATABLE READは分離レベルの1つで、簡単に言うと

一貫性読み取り（ある瞬間のスナップショットデータに対して読み込みする）を可能にする

ことができるようになる。つまり、

--single-transactionを指定することで、その時点でのスナップショットデータをdump対象にとりつつ、わざわざtableをREAD LOCKさせることなくdumpできる

というわけだ。

その性質上、dump中にinsertされたデータもdumpデータに入れ込みたい場合はこの限りではないわけだが、そもそもそんなケースはほぼ希だと思われるのでここでは深く立ち入らないこととする。

参考）

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0062

https://qiita.com/song_ss/items/38e514b05e9dabae3bdb

https://www.infiniteloop.co.jp/blog/2020/02/mysql-repeatable-read-and-rocksdb-optimistictransaction/

LOCKしないようにするには--skip-lock-tablesオプションを使う

mysqldump中にREAD LOCKさせないようにするには、

--skip-lock-tables

を指定するだけでOK。

改良したmysqldumpコマンドはこちら

これで、mysqldump中にWebサービスが止まるようなことは無くせる。トランザクション万歳！