ねこものがたり

いちにちいっぽ

MySQLのオンラインDDLでロックがかからないことを確認する

MySQL SQL Database

最初に結論

ALTER statements ALGORITHM=INPLACE, LOCK=NONE;

こんなふうにオプション付きALTER文を実行して結果にエラーがなければオンラインDDLでロックがかからず実行できると言うこと。 SQLを走らせてみなくても、リファレンスマニュアルにまとまっているので、それを確認することもできる。

この記事の動機

お仕事でRailsアプリケーションを書いています。 時々migrationを書くんですが、先日「それって本番デプロイ時にロックかからん？大丈夫？」って聞かれました。結果的にロックはかからないとわかったのですが、以下の課題を自分自身に感じました。

  • migrationのスクリプトだけをみて実行時にロックがかかるかどうかの判別するのが難しい
    • 実行してみて結果を確認する必要がある
      • やってみないとわからない運用は辛いけど、確認作業をしないで事故が起きるのはもっと辛い

そういう課題を感じたので、最終的には運用を楽にしたいという思いがありますが、そのためにもこの事象についてよく理解しておきたいと思ったので記事にしておきます。

そもそもDDLって？

データベース言語（ＳＱＬ）の体系　DDL/DML/DCL *1を引用します。

データ定義言語（ＤＤＬ：Data Definition Language）
データベースのデータ構造を定義する言語
CREATE（表の創成、定義）、DROP（表の削除）など

データ操作言語（ＤＭＬ：Data Manipulation Language） 既存表からのデータ検索・追加・更新・削除を行うための言語
SELECT（検索）、INSERT（追加）、UPDATE（更新）、DELETE（削除）など

データ制御言語（ＤＣＬ：Data Control Language） データベースへのアクセス制御に関する言語 GRANT（アクセス権付与）やREVOKE（権限破棄）など

MySQLの用語説明を見てみるとこうありました。

CREATE、ALTER、および DROP ステートメントのすべての形式を含みます。TRUNCATE ステートメントも含まれます。

DDLとはテーブル定義に関係するSLQのことと思えば良さそうです。

オンラインDDLとは

MySQL5.6時点でのオンラインDDLやInnoDB とオンライン DDLではこのように説明されています。

DDL (主に ALTER TABLE) 操作中の InnoDB テーブルのパフォーマンス、並列性、および可用性を改善する機能。

テーブルへのアクセスを完全にブロックするか (LOCK=EXCLUSIVE 句)、クエリーを許可するが、DML は許可しないか (LOCK=SHARED 句)、またはテーブルへの完全なクエリーおよび DML アクセスを許可するか (LOCK=NONE 句) どうかを選択することによって、DDL 操作中のパフォーマンスと並列性のバランスを調整できます。LOCK 句を省略するか、または LOCK=DEFAULT を指定すると、MySQL は、操作の種類に応じてできるだけ高い並列性を許可します。

DDLの実行はMySQL5.5以前ではテーブルコピー方式（全て既存をコピーした上で新しいものに置き換える形）だったそうです。 それがインプレース方式（既存のものを直接変更する形）になったことがオンラインDDLと呼ばれる仕様の最大の特徴の1つと言えそうです。

この変更の方式と合わせて、DDL実行中にDMLを実行できるか・ロックをかけるかなどを制御できることもオンラインDDLの特徴の1つのようです。

オンラインDDLでロックがかからないことを確認する

MySQL5.6ではオンラインDDLの登場に伴い、オプションとしてALGORITHM句とLOCK句が導入されたようです。 第30回 InnoDBオンラインDDLについて：MySQL道普請便り｜gihyo.jp … 技術評論社にまとまっていたそれぞれのオプションの意味を引用します。

LOCK句はテーブルへのクエリと並列DML操作のレベルを制御可能です。

オプション 説明
NONE クエリーと並列DML操作を許可
SHARED クエリーを許可・並列DML操作をブロック
EXCLUSIVE クエリーと並列DML操作をブロック
DEFAULT 使用可能なもっとも低いレベルのロックを使用
LOCK句省略 DEFAULTと同じ

ALGORITHM句はインプレース方式かテーブルコピー方式の制御が可能です。

オプション 説明
INPLACE インプレース方式
COPY テーブルコピー方式
DEFAULT インプレース方式を優先
ALGORITHM句省略 DEFAULTと同じ

オンラインDDLでロックがかからないことを確認するためにこの2つのオプションを利用します。

まず、オンラインDDLはDDLをインプレース方式で実行することを特徴としているのでALGORITHM=INPLACEを指定します。

ロックがかかるかどうかも同様にオプションを指定しますが、ここでは「ロックがかからないこと」を期待したいと思いますので、LOCK=NONEを指定します。

つまり

ALTER statements ALGORITHM=INPLACE, LOCK=NONE;

とオプションを指定して実行することでオンラインDDLでロックがかからないことを確認できます。 このSQLを実行してエラーになればそもそもオンラインDDLには対応していないか、ロックがかかってしまうと発見することができます。

SQLを実行しなくても公式にはまとまっている!

毎回SQL実行して確認するのも面倒だなーと思っていたらきちんと公式リファレンスにまとまっていました。一通り目を通して脳内インデックスは貼っておくとして、ひとまずは何かあればこちらを参照するのが良さそうです。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.11.1 オンライン DDL の概要

終わりに

以上、MySQLのオンラインDDLでロックがかからないことを確認する方法についてまとめてみました。

冒頭で述べたように、これをmigrationを作成するたびに気にしないといけないのは少々煩わしいです。 従って次のアクションとしては、この理解をもとに、どのように運用していくと安全(かつ楽か)と言うのを考えていきたいと思います。

*1:木暮仁さんのご自身の活動や大学での講義内容がまとまったサイトです。特に参考にしたページは"文系（非ＩＴ系）大学生を対象にした授業で用いていた内容"ということで説明が非常に簡潔でわかりやすい。