DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識

みなさん、おはようございます！ CARTA fluct エンジニア の なっかー@konsent_nakka です。

CARTA TECH BLOG アドベントカレンダー 12/14ということで、普段DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識をざっとまとめてみました。 とりあえずこれだけ読んでおけば最低限は困らない、もし何か困った時にはあそこで出てきた内容をもう少し深く調べて見るか、というきっかけになれば良いなと思います。

厳密な定義よりも普段DBを扱う中でロックについてあまり意識したことがないような人にもすっと入ってくるように簡単な表現を優先して書いていますがご了承ください。

目次

• 留意事項
• 排他ロックと共有ロック
• トランザクション分離レベル
• SELECTのロックレベルを変更する
  • 共有ロック: LOCK IN SHARE MODE
  • 排他ロック: FOR UPDATE
  • REPEATABLE READ
  • SERIALIZABLE
• ロックの種類
  • レコードロック
  • ギャップロック
  • ネクストキーロック
  • その他
• ロックの取り方によってよくハマるデッドロックの例
  • 共有ロック同士のデッドロックについて
  • DELETE & INSERT ロジックを書いた時に発生するデッドロック
• 参考

留意事項

この記事で基本的にMySQL(InnoDB)5.xでの話になりますので、MyISAMだったり他のDBだと状況が変わってくると思いますのでお気をつけください。

※この記事において2つのプロセスからsqlを実行する例を記述しますが、その時のプロセス1はmysql>、プロセス2はMYSQL>と記述します

排他ロックと共有ロック

まずロックには排他ロック(X)と共有ロック(S)があります。 非常に簡単に説明するとSELECT文を実行する時は共有ロックを、UPDATE/DELETE/INSERT文を実行する時は排他ロックを取得して他のSQL実行を一部制限してくれます。¹

排他ロック: 基本的には他のSQL実行をブロックするロック
共有ロック: 排他ロックを取得するようなSQL実行をブロックするが、共有ロックを取得するSQL実行までは許可するロック

	共有ロック	排他ロック
共有ロック	OK	NG
排他ロック	NG	NG

トランザクション分離レベル

トランザクション分離レベルとはトランザクションにおいて各命令によってどの程度厳密にロックを取るのかを設定するものです。 DB関連用語で頻出するいわゆるACID特性のIsolationに関係する部分です。

InnoDBでは4 つのトランザクション分離レベルが存在し一貫性が緩い順に、READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE が対応されています。 InnoDBのデフォルトではREPEATABLE READになっています。

個人的には特殊な要件がない限りはデフォルトのREPEATABLE READで問題ないと思っています。 覚えておいた方が良いトランザクション分離レベルは基本2つでREPEATABLE READ / SERIALIZABLEあたりでしょうか。 デフォルトがREPEATABLE READで、さらにトランザクションを厳密化したい場合においてSERIALIZABLEを検討する流れになりそうです。 そもそもUPDATEなどをしないアプリケーションだとREAD UNCOMMITTEDだったりREAD COMMITTEDでよかったりするかもしれませんね。

	ダーティーリード	非リピータブルリード	ファントムリード
READ UNCOMMITTED	発生する	発生する	発生する
READ COMMITTED	/	発生する	発生する
REPEATABLE READ	/	/	発生する(限定的に発生しない)
SERIALIZABLE	/	/	/

REPEATABLE READのファントムリードについては後述 ※ダーティーリード、非リピータブルリード、ファントムリードについて知りたい方はこちらの記事がわかりやすいので参照ください

SELECTのロックレベルを変更する

SELECT文にはロック読み取りという追加構文が2種類あります。 DBから特定のデータを取得してアプリケーションで処理をした後にデータを更新したりするようなことがよくあると思いますが、簡単にいうとそのようなDB->アプリケーション->DBと他のプロセスから邪魔されずに実行したい場合に利用する構文になっています。 そしてその構文には2種類存在します。

例によって共有ロック用と排他ロック用の２つです。 もちろんですがこれらのロック読み取りはロックを取得するのでロック読み取りSELECT同士でも通常のロック関係どおりブロックが発生します。

追加するとInnoDB(REPEATABLE READ)の場合はロック読み取りも結構罠があるんですがこれに関しては後述します。

共有ロック: LOCK IN SHARE MODE

このロック読み取りを使用するとトランザクションが終了されるまで共有ロックを継続して取得し続けます。 例えば他のプロセスから対象のデータを取得されるのはいいが更新削除はされたくない時に使用します。

mysql> BEGIN;

mysql> SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

-- 他プロセスからの共有ロックは取得可能
-- MYSQL> SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

-- 他のプロセスから上記のデータを更新、削除しようとしても共有ロックを取得されているのでブロックされる
-- MYSQL> UPDATE t SET val = 2 WHERE id = 1;

mysql> COMMIT;
-- トランザクションが終了すると共有ロックが解除される

この構文はMySQL8でFOR SHAREという構文にアップグレードされているので詳しく知りたい方はこちらを参照ください。

排他ロック: FOR UPDATE

このロック読み取りを使用するとトランザクションが終了されるまで排他ロックを継続して取得し続けます。 例えばDBから対象のデータを取得してアプリケーションで処理をした後にその値を使用して対象データを更新したい場合などに使用します。

mysql> BEGIN;

mysql> SELECT * FROM t WHERE id = 1 FOR UPDATE;

-- 他のプロセスから上記のデータを更新、削除しようとしても排他ロックを取得されているのでブロックされる
-- MYSQL> UPDATE t SET val = 2 WHERE id = 1;

mysql> UPDATE t SET val = :updated_data WHERE id = 1;

mysql> COMMIT;
-- トランザクションが終了すると排他ロックが解除される

REPEATABLE READ

このレベルはInnoDBのデフォルトで一番使われているものになっています。 そのため基本的なところは説明しません。 しかしInnoDBにおいてこのレベルは結構罠が多いのでその辺りを重点的に書いていきます。

REPEATABLE READのファントムリードについて限定的に発生しないと書いたことについて説明します。 InnoDBに存在するConsistent readsによってファントムリードが防がれます。 このconsistent readsはそれぞれのトランザクションにおいて最初の読み取り(SELECT)が発生したタイミングでスナップショットを作成し、別のプロセスからデータ追加された場合でもスナップショットを読み取るために追加されたデータを読むことありません。

consistent readsにおいて気をつけるポイントは２つあります。

まずスナップショットを作成するタイミングは最初の読み取り時点なので、別プロセスから既にデータ追加されている場合はそれも含めてスナップショットとしてしまいます。 直感的にはトランザクションを開始したタイミングだと思ってしまいそうなので間違えないように気をつける必要があります。

mysql> CREATE TABLE t (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;

mysql> INSERT INTO t (id) VALUES(1);

mysql> BEGIN;

-- 別プロセスからデータ追加
MYSQL> INSERT INTO t (id) VALUES(2);

-- consistent readsによってこの時点までのスナップショットが作られるので既に追加されているデータはファントムリードしてしまう
mysql> SELECT * FROM t;
/*
+----+
| id |
+----+
|  1 |
|  2 |
+----+
*/

もうひとつの気をつけるポイントはロック読み取りを使用するとファントムリードが発生してしまうことです。 以下に例を記述します。

mysql> CREATE TABLE t (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;

mysql> INSERT INTO t (id) VALUES(1);

mysql> BEGIN;

mysql> SELECT * FROM t;
/*
+----+
| id |
+----+
|  1 |
+----+
*/

-- 別プロセスからデータ追加
MYSQL> INSERT INTO t (id) VALUES(2);

-- 元プロセスからはconsistent readsによってファントムリードが起きない
mysql> SELECT * FROM t;
/*
+----+
| id |
+----+
|  1 |
+----+
*/

-- ロック読み取りをするとファントムリードが発生する(SHARE MODEでも同様)
mysql> SELECT * FROM t FOR UPDATE;
/*
+----+
| id |
+----+
|  1 |
|  2 |
+----+
*/

SERIALIZABLE

このレベルはREPEATABLE READと似ていますがSELECTが常にLOCK IN SHARE MODEと同一になるため厳密性が増します。

ロックの種類

ここまで排他ロック、共有ロックなどの基本的に意識すべきロックの話は出てきましたがこれ以上にロックには細かい種類が存在します。 ロックの影響範囲だったり、何をもとにロックするかなどです。 あまり詳細に書きすぎても仕方ないので大雑把に列挙するような形で紹介します。

レコードロック

文字通りレコード(行)をロックするものです。 例えばレコードが一意に決まるようなprimary key検索での更新なんかは内部でこれが使われています。 他にも複合uniqueに対しての検索でもレコードロックが使われたりします。

ギャップロック

ギャップロックとは言葉では分かりづらいですが範囲ロックと言い換えてもいいでしょう。 WHERE id < 5とかした時にidが5超過のものは範囲的にロックするといったところです。

INSERTする時にidが1のものしか入ってないとしてidが5のものを入れる時は1~5の範囲をギャップロックされたりします。(厳密には違いますが) こういうギャップが被ることによって想定していないブロックが発生すると言ったことも多くあるのでそう言うものもあると認識しておくと理解の役に立つと思います。

ネクストキーロック

これはかなり分かりづらく実際同じSQLでもレコードロックとギャップロックが使われるときは場合によりけりだったりするのでそこまで細かく意識する必要があるタイミングは少ないでしょう。

非常に簡単に言うとギャップロックの範囲 + 境界値部分の次のキーを余分にロック取得するものです。 この辺りのデータをロックしたいってことはその前後もデータが追加されたりするんじゃないか？といった意図でロックされていると思っていいです。

その他

INSERT時に使用されるインテンションロックというものがあったり、SELECT UPDATEなど特殊な合わせSQLでは場合によって排他ロックだったり共有ロックだったり、状況によってロックの形式が大きく変わるものがあります。 この辺りに関して全てここに記すには余白が狭すぎます。

ロックの取り方によってよくハマるデッドロックの例

共有ロック同士のデッドロックについて

mysql> BEGIN;
mysql> SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

MYSQL> SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

-- DEAD LOCK !
mysql> DELETE FROM t WHERE id = 1;

共有ロックの上から共有ロックは取得できますが、その上から専有ロックを取得することはできません。 一つのプロセスで実行されるだけなら普通にロック解除を待つだけなので問題ありませんが二つのプロセスから実行される場合は問題があります。

解決策としては専有ロックを取得するようなSQLを実行するならSELECT ~ FOR UPDATEを使用することです。

DELETE & INSERT ロジックを書いた時に発生するデッドロック

mysql> CREATE TABLE summary (
    id INT NOT NULL AUTO_INCREMENT,
    val INT NOT NULL,
    month DATE NOT NULL,
    UNIQUE val_month (val, month),
    PRIMARY KEY (id)
) ENGINE = InnoDB;
mysql> CREATE TABLE details (
    id INT NOT NULL AUTO_INCREMENT,
    summary_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (summary_id)
        REFERENCES summary(id)
) ENGINE = InnoDB;

mysql> INSERT INTO summary (id, val, month) VALUES(1, 1, '2022-10-01'), (2, 1, '2022-11-01');
mysql> INSERT INTO details (id, summary_id) VALUES(1, 2), (2, 2);
-- ここまでは初期データ登録

mysql> BEGIN;
mysql> SELECT * FROM summary WHERE val = 1 AND month = '2022-11-01' FOR UPDATE;
mysql> SELECT * FROM details WHERE summary_id = 2 FOR UPDATE;

-- ブロックされて待機中
MYSQL> BEGIN;
MYSQL> SELECT * FROM summary WHERE val = 1 AND month = '2022-11-01' FOR UPDATE;

mysql> DELETE FROM details WHERE id = 1;
mysql> DELETE FROM details WHERE id = 2;
mysql> DELETE FROM summary WHERE id = 2;

-- Dead lock !
mysql> INSERT INTO summary (val, month) VALUES(1, '2022-11-01');
mysql> INSERT INTO details (summary_id) VALUES(3);

このデッドロックはロックの種類を細かく把握しないと理解できないデッドロックになっています。

解決策は2つあります。

• SELECT ~ FOR UPDATEでid指定で取得するようにする(INSERTと被らないため)
• リトライ制御をする

一つ目の解決策としてはロックを取得するSELECT ~ FOR UPDATEのタイミングでval_monthのロックを使用しないようにすることです。 今回の場合、簡単に言うとSELECT * FROM summary WHERE id = 1 FOR UPDATEでidロックを使用するようにすると問題なく動きます。

もしくはリトライ戦略です。 デッドロックにより失敗したら再実行することによって究極何度もやれば確実に1つづつは成功していくので同時並列数が少ない、もしくはデッドロックの可能性がある程度以内であればリトライ作戦もいいと思います。 そもそもデッドロックが多すぎるようであれば処理のやり方に問題がありそうな気もしますが。
ここでの注意点はデッドロックを検知するために他のエラーまでtry catchで全部吸収しちゃうと重要なエラーを握りつぶしてしまう可能性があるのでデッドロックかどうかはちゃんと判別しましょう。

その他

排他ロックと共有ロックがあるというのは何となく分かっているけど細かい仕組みは理解していないせいで謎のロックやデッドロックが発生して困るというパターンが結構ありますよね。 とりあえずそう言うものがあるということだけでも知っておくと全然違うので今回の記事が少しでも参考になると良いなという気持ちと自分の勉強を兼ねて書きましたが参考になると嬉しいです。

それと今回のロックについての知識とは少し離れますが、ロック待ちのタイムアウト(innodb_lock_wait_timeout)なんかもデフォルトだと50秒くらいに設定されているんですがこれも5秒とか10秒とか短い値に設定し直しておくのもCPUだったり処理時間に結構影響するので良さそうでした。

参考

• 「失敗から学ぶRDBの正しい歩き方」著者: 曽根 壮大 さん
• https://dev.mysql.com/doc/refman/5.6/ja/innodb-lock-modes.html
• https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html
• https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html
• https://blog.tiqwab.com/2018/06/10/innodb-locking.html
• https://zudoh.com/mysql/research-next-key-lock-and-insert-intention-gap-lock

---

1. INSERT文は正確にはもっとややこしいロックの挙動をしています。詳細に知りたい場合はインテンションロックで調べてください。↩