ひだまりソケットは壊れない

ソフトウェア開発に関する話を書きます。 最近は主に Android アプリ、Windows アプリ (UWP アプリ)、Java 関係です。

まじめなことを書くつもりでやっています。 適当なことは 「一角獣は夜に啼く」 に書いています。

トップ > MySQL

MySQL (InnoDB) とトランザクション分離レベル・ロック

MySQL SQL データベース

(注記) 去年書いて下書き状態になってた記事をそのまま公開した。 MySQL 8.0 公開前に書いた内容なので MySQL 5.7 について言及しているが、多分 MySQL 8.0 でも基本的には変わらない気がする。

背景・目的

DB 上に指定の ID の行が存在していれば UPDATE して、存在していなければ INSERT したい、ということは web アプリケーションを書いているとよくあること。 SQL:2003 標準には MERGE が導入されていて、Oracle Database なんかだと MERGE を使用できるようだが、MySQL では使えない (バージョン 5.7 時点)。 (代わりに INSERT ... ON DUPLICATE KEY UPDATE がある。)

また、複数のテーブルで 1 つの集約 *1 を表現していて、集約の更新時に不整合が起きないようにロックをかけたいということも多い。

といったあたりで、MySQL (InnoDB) における参照や挿入、更新とトランザクション分離レベルやロックについて調べたのでまとめておく。 (初心者が調べてみたぐらいの内容なので、指摘やコメント等頂けると嬉しいです！)

調べた結果どうしたらいいかの個人的見解

いろいろ調べた結論としては、個人的には以下のような感じがいいのかなーと思っている。

  • 参照系ではトランザクション分離レベルを REPEATABLE READ にする。 → 参照だけであれば一貫性のある読み取りができる。
    • REPEATABLE READ で一貫性のある読み取りができるのは InnoDB の実装に依存した挙動で SQL 仕様としてはファントムリードが起こり得るので、実装依存にしてよいと判断するかどうかは状況次第だと思うけども、多くの場合はそうするのが開発コスト的には一番安い気がする。
  • 単一の集約に対する更新系 (新しい集約の新規作成や既存の集約の更新; INSERT or UPDATE) では、トランザクション分離レベルを READ COMMITTED にして *2、集約ルート (に対応するテーブルの行) の存在確認を SELECT FOR UPDATE で行う。
    • 存在すればそのままロックを保持して更新処理を行う。 → 集約ルートに対する排他ロックを獲得しているので、他のトランザクションによる更新と衝突しない。
    • 存在しなければ INSERT する。 Duplicate error になったら (すなわち同時に実行されていた他のトランザクションによる挿入が行われた場合は) トランザクション自体を再開してやり直すか、エラーということでそのまま処理を終了する。 → こちらも挿入に成功すれば排他ロックを獲得するので、そのまま処理を進めて良い。 Duplicate error になった場合には対象行の共有ロックを獲得してしまうため、やり直す場合にはトランザクション自体をやり直さなければならない *3。
      • トランザクション自体のやり直しをどう設計するのかは難しいけど、トランザクションの扱いをアプリケーションレイヤの責務にすることが多いと思うので、アプリケーションレイヤでやるのがいいのかも。 他の方法として、INSERT ON DUPLICATE KEY UPDATE を使うとかは有り。
    • 実装依存でも良ければ INSERT ... ON DUPLICATE KEY UPDATE で良い場面が多そう。
    • パフォーマンスを気にしなくてもいいならトランザクション分離レベルを SERIALIZABLE にするとかも有りかもしれない。
    • (だいたい songmu さんが書いてるとおりの結論ではある。)

参考ページ

MySQL (InnoDB) のロックについて

以下、自分が知らなくて調べた話をだらだら書いておく。

ロックの種類

MySQL 5.7 の英語ドキュメントにまとまっている。

インテンションロック

特殊なテーブルレベルのロックとしてインテンションロックがある。 これらはテーブルレベル・行レベルといった複数粒度のロックの共存のために存在するっぽい。

  • インテンション共有 (Intention shared; IS) ロックは、他のトランザクションによるテーブルレベルの排他ロックと衝突する。 (行レベルの共有ロックの前に獲得される。)
  • インテンション排他 (Intention exclusive; IX) ロックは、他のトランザクションによるテーブルレベルの共有ロックや排他ロックと衝突する。 (行レベルの排他ロックの前に獲得される。)
インデックスに対するロック

InnoDB では、インデックスに対するロックによって行レベルロックを実現している *5。 MySQL の公式ドキュメントやかみぽさんの解説がわかりやすい。

  • レコードロック (Record Locks) : インデックスレコードに対するロック。
  • ギャップロック (Gap Locks) : インデックスレコード間の隙間 (gap) に対するロック。 隙間は、複数のインデックス値にかかることもあるし、単一のインデックス値や空のところにさえもかかりうる *6。 ギャップロックは、他のトランザクションが同じ隙間のギャップロックを獲得することは防がない。 対象の隙間へのレコードの挿入のみが防がれる。 (つまり、共有ギャップロック (gap S-lock) も排他ギャップロック (gap X-lock) も効果は同じ。)
  • ネクストキーロック (Next-Key Locks) : レコードロックと、そのレコードの前の隙間に対するギャップロックの組み合わせ。
Insert Intention Locks

INSERT 操作時に、行の挿入前に獲得されるギャップロックの一種として、挿入インテンションロック (Insert Intention Locks) がある。

『MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary』 におけるデッドロックは、別のトランザクションが行を挿入して獲得した排他レコードロックと、挿入しようとしている行のための挿入インテンションロックの衝突が 2 箇所で起こってしまう、ということなのかな？ と思ったりしたけど、InnoDB monitor の出力を見た感じでは共有レコードロック待ちになってたので挿入インテンションロック待ちとは違ってそうだった。 挿入インテンションロックは純粋にギャップロックとのみ衝突するもので、レコードロックとは衝突しない模様。

ギャップロックとファントム行、あるいはデッドロック

ファントムリードを防ぐために

ギャップロックと挿入インテンションロックのデッドロック : mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する | ++頭道++
mysqlのネクストキーロックと挿入インテンションギャップロックのデッドロックを確認する | ++頭道++ → この人の解釈では DELETE により指定の ID に対するロックができる (存在しないレコードに対するロックという解釈をしている) となっているが、私の手元の MySQL 5.7.20 で試した限りでは、存在しない ID を指定しての DELETE でギャップロックが獲得されていた。

下書きはココで終わっていた

多分他にも書きたいことがありそうだったけどもはや何も覚えてないのでこのまま公開する。

*1:DDD でいうところの集約。 Aggregates。

*2:この理由は主にギャップロック無効化のため。 ギャップロック有効だとデッドロックしうる; REPEATABLE READ でもギャップロック無効化できるしそうする方がいいのかもしれない

*3:そうしなければ、複数トランザクションで同一行に対する duplicate error が起こった場合にトランザクションをやり直さないと排他ロックの獲得ができず、デッドロックになる

*4:複数のトランザクションが DELETE するとそれぞれのトランザクションがギャップロックを獲得し、その後の INSERT がそれぞれ待ち状態になってしまう。

*5:という理解であってるよね？

*6:空のインデックスでも全体に対するギャップロックがあり得る、ってことかな？

PostgreSQL と H2 Database の両方で使える SEQUENCE 値生成方法 (MySQL の AUTO_INCREMENT 的なやつ)

SQL H2 Database PostgreSQL MySQL データベース

SQL DB に新しい行を挿入したときに自動的に ID 値を生成して欲しいことはままある。 このような処理をしてくれるテーブル定義についての話。

MySQL だと AUTO_INCREMENT を使いがち

MySQL を使ってる場合は AUTO_INCREMENT で済ませちゃうことが多い (H2 Database でも使用可)。

CREATE TABLE "foo" (
  "id" BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

PostgreSQL のための AUTO_INCREMENT 以外の方法

ただ、PostgreSQL では AUTO_INCREMENT が使えない。 代わりに使える方法を 2 つ書いておく。

BIGSERIAL 型を使う

PostgreSQL で、テーブル定義時に型として BIGSERIAL 型を使用すると内部的に SEQUENCE を利用するようになる。

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
PostgreSQL: Documentation: 10: 8.1. Numeric Types

上の例では SERIAL 型が使われているが、これは INTEGER 相当なので、BIGINT 相当のサイズにしたければ BIGSERIAL を使用すること。

ちなみに BIGSERIAL は H2 Database でも使用できる (ちゃんとしたドキュメントは見当たらなかった)。 MySQL でも SERIAL は使用できる (MySQL の場合は SERIAL で BIGINT になる) が、BIGSERIAL は使えない (MySQL 8.0 で検証)。

外部 SEQUENCE 生成子を使う

標準 SQL に従った方法はこれっぽい。 上の SERIAL 型を使った場合と等価な例に載っているような形になる。 ただ、H2 Database は SEQUENCE の所有カラムを明示できないようなので、そこは指定しないでおく。 (H2 Database でも使いたいので。)

CREATE SEQUENCE "foo_id_seq";
CREATE TABLE "foo" (
    "id" bigint NOT NULL DEFAULT nextval('foo_id_seq')
);

参考

MySQL Connector/J 5.1 系では useLegacyDatetimeCode=false にしよう

Java MySQL ライブラリ

JDBC で MySQL に接続するときに使用する MySQL Connector/J (mysql:mysql-connector-java) の話。 サーバー・クライアントのタイムゾーン設定が違っている場合にどう対応するのがいいか。

結論

  • MySQL Connector/J 6 (まだ開発版だけど) 以降は自動でやってくれるので気にする必要はない。
  • MySQL Connector/J 5.1 では URL に useLegacyDatetimeCode=false を入れて、時刻周りの新しい処理が動くようにしろ。
    • 新しい処理では、タイムゾーンの変換を一貫性をもってやってくれるようになる。
    • 『Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and statements that consistently handles time zone conversions from client to server and back again』
    • 参考 : 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
    • 5.1 系ではデフォルトでは互換のために新しい処理は動かないようになっているので、明示的に新しい処理を使うように URL で指定する必要がある。
    • バージョン 5.1.6 で導入された機能なので、それより古いものでは使えない。

問題

そもそもどういう問題に遭遇したのか。

MySQL Connector/J とタイムゾーン

おわり

タイムゾーンはライブラリ側がちゃんと面倒見てくれるだろう、と思って気にしなかったら、環境を変えて Java アプリケーションと MySQL サーバーのタイムゾーン設定がずれたときにいきなり想定しない動作になったりするので気を付けましょう。

MySQL では GROUP BY 句を使用した場合でも集約されていない列を結果に含めることができる

SQL MySQL

表題どおり。

普通、SQL で GROUP BY 句を使用して SELECT する場合、結果に含めることができる列は GROUP BY 句に含まれる列か集約関数 (aggregate functions) を適用した列のみです。 しかし、MySQL の場合はそうではなく、GROUP BY 句にも含まれていないし集約関数も通していない列を結果に含めることができます。

仕事で触ってるプログラムの中にそういうクエリを発行する SQL があって 「マジか!!」 って思ったのですが、調べてみるとパフォーマンスのためにそういう拡張がなされているとのことでした。

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html

例

CREATE TABLE example_table ( a INT, b INT, c INT );

上のように作ったテーブルが存在する場合、列 a でグループ化してそれぞれの a の値に対する b の値の最大値を求めるクエリは下のようになります。 この SQL は PostgreSQL でも MySQL でも普通に結果を返してくれます。

SELECT a, MAX(b) FROM example_table GROUP BY a;

さらに、結果に列 c を含めようとすると、下のようなクエリになります。

SELECT a, MAX(b), c FROM example_table GROUP BY a;

PostgreSQL でこのクエリを投げると、列 c が GROUP BY にも含まれないし集約関数を通しているわけでもないので以下のようにエラーになります。 (PostgreSQL 9.1 で確認。)

ERROR:  column "example_table.c" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT a, MAX(b), c FROM example_table GROUP BY a;
                          ^

一方で MySQL の場合はエラーにならずに結果を返してくれます。 (MySQL 5.5 で確認。) MySQL サーバーは、集約されていない列の値に関しては各グループの中から自由に選ぶ (The server is free to choose any value from each group) とのことです。

ONLY_FULL_GROUP_BY モード (追記)

集約関数を通していない値が結果に含まれるような SQL を拒否させるための ONLY_FULL_GROUP_BY という SQL MODE があります。 5.7.5 以降ではデフォルトで有効になっているみたいですね。