giftee engineer blog

MySQLでロックをかけずにレコードを更新する方法

2021-12-16

MySQLでロックをかけずにレコードを更新するためのアレコレ

こんにちは！ ギフティでエンジニアをしている堀井です！

ギフティ初のフルリモート社員として入社しまして、当初は社内をざわつかせたもの(主観)ですが、最近は普通の人として日々を過ごしています。

実は最近、DBでいろいろとヒヤッとする場面があったのですが、そのときに色々やったことを備忘録 兼 今後同じ悩みを抱えた方への共有として記事に残そうと思います。

背景

ギフティが提供しているeGiftのシステムは、おかげさまで100万ユーザーを超えていまして、ギフトのテーブルレコード数は実に4000万レコード(!)にも及びます。

これほど大きなテーブルになってくると、全検索すると数分以上かかるため、SQLの実行(特に条件による絞り込み)には細心の注意を払わないといけません。

なのですが…

本番DBのレコードを直接書き換える？ できらあ！

日々ビジネスの最前線という戦場で戦うエンジニアの皆さんは経験があると思いますが、サービスを長く運用していると、本番DBのデータをアプリケーション外で修正する作業が時たま発生することがあります。

例えばeGiftサービスで言うと

  • 不正な決済の疑いがあるデータが見つかったので、そのギフトは使えないようにしてほしい
  • アプリケーションのバグによってデータに不整合が生まれてしまったので修正したい などです。

こういった要望に対して、我々エンジニアはできるだけアプリケーションの機能やAPIを用意することで万全の対策を取れるよう日夜戦い続けていますが、あまりにも人生は短く、そしてプログラミングは人類にはあまりにも過ぎた力です。

その結果、「本番DBに対してその場で作ったSQLを流す」といった、温もりあふれる手運用、職人の技といってもいいでしょう、が発生することになります。

「本番DBに対してその場で作ったSQLを流す」ことの怖さについては、色々と理由がありますが、そのうちの1つに以下の状況があります。

  1. SQLを流してレコードを更新する
  2. 更新中にアプリケーションから同じレコードに更新アクセスが来る(アプリケーション側はロック待ちになる)
  3. 更新処理が終わらずアプリケーションでエラーになる

今回の記事ではこれが起きないためにできることを色々と試してみたので、紹介していこうと思います。

なお、MySQLのレコードロックについてはこの記事では割愛しますが、MySQLの大事な概念なので調べてみてください。

ロックをかけない or かける時間を短くするためにできること

サブクエリを使わずにJOINする

1つ目のアプローチは、ロックをかける時間を短くする & ロックがかかる範囲を短くするアプローチです。そのため、ロックうんぬんに関係なくSQLを高速化したいときにも使える手法です。

SQLで範囲検索をしたいときによくやるのが相関サブクエリですが、とある記事によると、相関サブクエリはだいたいJOINに書き直せるようです。
https://www.slideshare.net/techblogyahoo/mysql-58540246

我らがMySQL公式もおっしゃっています。
https://dev.mysql.com/doc/refman/5.6/ja/rewriting-subqueries.html

基本的にJOINのほうが高速(なことが多い)、ロック範囲も狭い(ことが多い)です。 あとこれは個人的な意見ですが、JOINのほうがクエリが見やすい気がします。

具体的なSQLを書くと、以下のようになります。

SELECT id
FROM table1
WHERE id IN(
    SELECT id
    FROM table2
    WHERE name = '太郎'
);

↓

SELECT id
FROM table1
INNER JOIN table2 ON table2.id = table1.id
WHERE table2.name = '太郎';

書き直せなかった or 書き直すの面倒なときは？

そんなあなたに一時テーブル

一時テーブルとは

セッション内でだけ保持されるテーブルです。
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0107

サブクエリでSELECTしたIDを一時テーブルに入れて、そこからWHERE INできるすごいやつです。

SQL例を書いてみると、

-- 一時テーブル作成
CREATE TEMPORARY TABLE `tmp_ids`
(
    primary key table1_id (id), // 省略可能のはず
    INDEX table1_id_index (id)
)
SELECT *
FROM table1
WHERE id IN(
    SELECT id
    FROM table2
    WHERE name = '太郎'
);

-- これだとJOINできる(インデックスも効く)
SELECT id
FROM table1
INNER JOIN tmp_ids ON tmp_ids.table1_id = table1.id

ここまで使うとだいたいJOINにできるはずです。

トランザクション分離レベルを変更する(劇薬)

2つ目のアプローチは、ロックをかけないようにするアプローチです。 後述しますが、こちらはリスクがあるので使用には注意が必要です。

MySQLにおけるロックの範囲

MySQLにはネクストキーロックという概念があります。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html

詳細はここでは割愛しますが、簡単に言うとMySQLでは「検索したレコード以外のレコードもロックされる」可能性があります。なのでそれも考慮した上でSQLを構築する必要があるのですが、このネクストキーロックは非常に複雑なもので、これを完全に理解するのは人類には不可能と言われています(主観)。

ただし、トランザクション分離レベルを変更すると、このネクストキーロックを一時的にかからない状態にすることができます。

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

他のトランザクションへ影響を与える度合いのことです。

参考: https://qiita.com/song_ss/items/38e514b05e9dabae3bdb

簡単に言うと、トランザクションAとトランザクションBが同時に一つのテーブルを操作したときにどうなる？ が変わります。このレベルはMySQLがREPEATABLE READ、その他のDB(Oracleとか)はREAD COMMITTEDがデフォルトになっていて、MySQLがネクストキーロックが機能しているのはこのレベル設定によります。

ちなみになぜMySQLだけ違うの？ という点は歴史の話になるのでここでは割愛します。

変更するとどうなる？

トランザクション分離レベルをREPEATABLE READからREAD COMMITTEDに変更してみます。

【注意！】変更はセッション単位にしましょう

グローバルに設定すると、アプリケーション側で思わぬ挙動になる可能性があります。

これによってネクストキーロックがされなくなり、検索範囲 = ロック範囲になります。

トランザクション分離レベルの変更(セッション中のみ)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • トランザクション分離レベルがREPEATABLE READだと、対象レコード外のレコードもロックされる

REPEATABLE READ

  • トランザクション分離レベルをREAD COMMITTEDに変更すると対象レコードのみのロックになる！

READ COMMITTED

デメリットは？

非常に強力なトランザクション分離レベルの変更ですが、デメリットとして、ファジーリード、ファントムリードが起きてしまいます。

これは簡単に言うと、他のトランザクションの影響を受けやすくなります。 (Aがテーブル見てるときにBがコミットすると、Aの方にもコミットが反映されるようになります。)
https://qiita.com/song_ss/items/38e514b05e9dabae3bdb

上記のような理由から、グローバルにトランザクション分離レベルを落とすのは推奨しません。しかし「本番DBに対してその場で作ったSQLを流す」という圧倒的逆境においては有効な手かもしれません。

まさに「毒をもって毒を制す」ですね。

まとめ

この記事では安全に (ロックをかけないよう) にMySQLテーブル操作をするためのTipsを紹介しました。

まとめると、

  • サブクエリを使わずにJOINする

    • ノーリスク(のはず)で高速化 & ロック狭くできる
  • JOINでうまく書けない時には一時テーブルを使うとJOINにできる

  • それでも足りない場合はトランザクション分離レベルをREAD COMMITTEDに落とす

    • あくまで一時的に使う
    • 検索範囲 = ロック範囲になるので、意図しないロックはなくなるはず になります。

ギフティでは

  • 本番DBに日々もりもりSQLを投げられている方
  • そもそも本番DBに直接SQL投げるなんてどうかしている、当然自動化でしょという方 のどちらも大募集していますので、よろしくお願いします！
採用情報giftee.com運営会社