テーブルロックを発生させずにmysqldumpを実行する
MySQLのDB定義を更新する前に、念のための mysqldump を行うことになり、テーブルロックが発生しないようダンプする方法について調査していました。
正確に理解しきれていない部分もあるのですが、調べた結果を簡単にまとめます。
–single-transactionの使用
MySQLのストレージエンジンがInnoDBの場合、 mysqldump 時に --single-transaction というオプションをつけることで、ロックを発生させずにダンプができるようです。
–single-transactionとは
--single-transaction をつけて実行すると、ダンプ前に REPEATABLE READ というモードが有効になります。
–single-transaction
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction
REPEATABLE READ は分離レベルという設定の一つで、同一トランザクション内で一貫性を保つために、事前に作成されたスナップショットを読みにいきます。
REPEATABLE READ
This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read.
つまり --single-transaction をつけることで、スナップショットに対してダンプをとる形になり、テーブルロックがかからない（かける必要がない）仕組みです。
InnoDBについて
ついでにInnoDBについても調べました。
InnoDBはMySQLとMariaDBで採用されているストレージエンジン。
MySQLでは少なくとも5.6以降、標準のストレージエンジンとしてInnoDBが採用されています。
InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL’s default table type.
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine.
https://dev.mysql.com/doc/refman/5.6/en/innodb-introduction.html
–skip-lock-tablesについて
さらに調べると --skip-lock-tables というオプションがありました。
いかにもテーブルロックを回避できそうな名前のオプションなのですが、これは --opt など、 --lock-tables が暗黙的に有効になるケースにおいて、ロックを打ち消すためのオプションになります。
バージョンによっては、 --opt はデフォルトで有効になっているようなので、 --skip-lock-tables も合わせて使うと良さそうです。
Some options, such as
--opt, automatically enable--lock-tables. If you want to override this, use--skip-lock-tablesat the end of the option list.https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_lock-tables
The –opt option (and hence –quick) is enabled by default,
MySQL < 5.5の場合
MySQLのバージョンが5.5以前の場合は、ストレートエンジンとしてMyISAMが使われているため、 --single-transaction が効かないようです。
ですので、MySQL < 5.5を利用しているシステムでは別の方法でダンプする必要があります。
まとめ
mysqldump 時のテーブルロックを回避する方法について調べ、まとめました。
MySQL 5.6以降はストレージエンジンとしてInnoDBが採用されているため、 --single-transaction を使うことでテーブルロックを発生させずにダンプ可能です。
