MySQLのロック機構を深掘り - 月間14万件の入居申込データを安全に扱うための最適化戦略

はじめに

賃貸募集支援事業プロダクト開発チームのかげやまです! 普段は申込受付くんの開発をしています!

申込受付くんでは申込みに関わる様々なデータを扱っており、その数も日を追うごとに増加しています。参考までに、月間WEB入居申込件数は最大月で約14万件に及びます。

そのためDBの操作には細心の注意を払う必要があります。

今回は、危うくデッドロックやタイムアウトを起こしかねなかった実際のタスクから、MySQLのロック仕様について深ぼってみます。

こんな方に読んでほしい

MySQLのトランザクションやロックの仕組みをより深く理解したいエンジニアの方
デッドロックやタイムアウトといったデータベースのパフォーマンス問題に悩まされている開発者の方
大規模データを扱うアプリケーションでの効率的なデータ操作手法を模索している方

動作環境

MySQLバージョン: 8.0.39

ストレージエンジン: InnoDB

トランザクション分離レベル: REPEATABLE-READ

検証用サンプルデータ

実務で使用したデータは使用できないので、似た形式のテーブルとレコードを作成します。

関連テーブルに外部キー制約を付けるか問題はありますが、今回は外部キー制約は付与せず単純にインデックスを付与します。

/* parentsテーブル作成 */
CREATE TABLE parents (
  id INT NOT NULL,
  PRIMARY KEY(id)
);

/* parentsテーブルのレコードを追加 */
INSERT INTO parents VALUES (10); 
INSERT INTO parents VALUES (15); 
INSERT INTO parents VALUES (20); 
INSERT INTO parents VALUES (25); 
INSERT INTO parents VALUES (30);
INSERT INTO parents VALUES (35);
INSERT INTO parents VALUES (40);

/* childrenテーブル作成 */
CREATE TABLE children (
  id INT NOT NULL AUTO_INCREMENT,
  parent_id INT NOT NULL,
  PRIMARY KEY(id),
  KEY idx_parent_id (parent_id)
);

/* childrenテーブルのレコードを追加 */
INSERT INTO children (parent_id) VALUES (10);
INSERT INTO children (parent_id) VALUES (20);
INSERT INTO children (parent_id) VALUES (30);
INSERT INTO children (parent_id) VALUES (40);

タスク要件について

あるレコードと、それに関連する別テーブルのレコードを一括削除するバッチ処理の実装が要件でした。

また、関連テーブルは約50テーブルほどあり、関連レコードの一部だけ削除されることが無いように1つのトランザクションで担保する必要がありました。

前述のサンプルデータで例えると、parents.id = 20のレコードを削除するために、children.parent_id = 20のレコードも削除しなくてはならないイメージです。

単純に上記の要件を実現すると以下のようなクエリになると思います。

START TRANSACTION;

DELETE FROM children where parent_id = 20;
/* 実際には他にも関連テーブルのレコードを削除するクエリが入る */
/* DELETE FROM ~~ */
/* DELETE FROM ~~ */
/* DELETE FROM ~~ */
DELETE FROM parents where id = 20;

COMMIT;

1つ目のDELETE FROM children where parent_id = 20;の実行自体はすぐに終了しますが、取得したロックはトランザクションが終了するまで保持されます。

また、この際childrenテーブルで取得されるロックが問題になるのはparent_id = 20へのinsertのみではありません。

そのため、トランザクション内の処理が増えるほどロック時間が増加し、ロックが競合する可能性も高くなります。

今回はこのDELETE FROM children where parent_id = 20;のロック範囲を深ぼってみます。

ロックの競合を起こしてみる

まずは以下のクエリを実行してみます。

/* trx1 */
START TRANSACTION;

DELETE FROM children where parent_id = 20;

次に別のトランザクションで以下のクエリを実行します。当然ですが、parent_id = 20へのinsertはtrx1のロックと競合しinsertが出来ません。

/* trx2 */
START TRANSACTION;

/* insertできない */
INSERT INTO children (parent_id) VALUES (20);

では次のクエリはどうでしょうか?

/* trx2 */
START TRANSACTION;

INSERT INTO children (parent_id) VALUES (15);
INSERT INTO children (parent_id) VALUES (25);

こちらもtrx1のロックと競合し、insertが出来ません。

ロックの範囲を確認する

ではtrx1で取得されているロックの範囲を確認していきます。

SELECT
  OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, LOCK_MODE, LOCK_DATA
FROM
  performance_schema.data_locks
WHERE
  LOCK_TYPE = 'RECORD';

/* 実行結果 */
+-------------+-----------+-------------+---------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | LOCK_MODE     | LOCK_DATA |
+-------------+-----------+-------------+---------------+-----------+
| children    | RECORD    | GRANTED     | X             | 20, 2     |
| children    | RECORD    | GRANTED     | X,REC_NOT_GAP | 2         |
| children    | RECORD    | GRANTED     | X,GAP         | 30, 3     |
+-------------+-----------+-------------+---------------+-----------+