MySQL8.0 で起こる謎のデッドロックの条件を調べてみた

この記事は Akatsuki Advent Calendar 2018 の13日目の記事です。
前回は csouls さんの GAE/Go & CircleCI でカナリアリリースをする でした。

はじめに

この記事ではMySQLのロック待ちが特定条件下でデッドロックになるという不思議な現象を検証していきます。

経緯

  • MySQL8.0を利用したアプリの開発で謎のデッドロックが発生していた
  • 開発当時(2018年10月末頃)、原因を調査したが結局分からなかった

目的

  • デッドロックが発生する条件を絞り込み、類似のケースが起きないように調査結果を共有する

注意事項

  • MySQLとは?デッドロックとは?というような基本的な知識の説明はしません。
  • 調査が進み次第、更新するかもしれません。

発生環境

  • OS: Windows10 64bit ( 恐らく Mac でも発生 )
  • Version: 8.0.13 MySQL Community Server
  • DB: InnoDB
  • トランザクション分離レベル: REPEATABLE READ

現象の再現

準備

まず、MySQLを起動して適当なデータベースを作成し、id と score という2つの整数のカラムを持つ users テーブルを作成します。

CREATE TABLE users  
(  
  id    INTEGER PRIMARY KEY,  
  score INTEGER  
);  

次に、users テーブルにレコードを作成します。5つのレコードを id: 10 から飛び飛びに入れます(スコアはまあ適当で)。

INSERT INTO users  
(id, score) VALUES  
(10, 95),  
(15, 67),  
(20, 82),  
(25, 93),  
(30, 87);  

最後に、スコアにindexを張ります。

CREATE INDEX index_users_on_score ON users(score);  

これで準備は整いました。

再現1

MySQLコマンドラインツールを2つ同時に起動し、ロック待ちが発生するクエリを発生させてみましょう。
まずトランザクション1で id が 18~23 の user を選択し、悲観的ロックをかけます。

  • トランザクション1
    BEGIN;  
    SELECT * FROM users WHERE id BETWEEN 18 AND 23 FOR UPDATE;  
    +----+-------+  
    | id | score |  
    +----+-------+  
    | 20 |    82 |  
    +----+-------+  

トランザクション2でも同様に id が 18~23 の user を選択し、悲観的ロックをかけようとしますが、ロック同士が競合するのでトランザクション2ではレコードを取得できません。

  • トランザクション2
    BEGIN;  
    SELECT * FROM users WHERE id BETWEEN 18 AND 23 FOR UPDATE;  
    (ロック待ち)  

トランザクション1は無事ロックを取得できたので、データを更新していきます。
が、更新の位置が悪いと謎のデッドロックが発生します。

  • トランザクション1

    INSERT INTO users VALUES (18, 75);  
  • トランザクション2

    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  

!?
こいつです。出ました。
原因は良く分からないのですが、ロック待ちの状態からデッドロックが発生してしまいます。
ちなみにトランザクション1で以下のクエリを実行した場合にはデッドロックは発生しません。

  • トランザクション1(デッドロック発生せず)
    INSERT INTO users VALUES (14, 75);  
    INSERT INTO users VALUES (21, 75);  
    INSERT INTO users VALUES (26, 75);  
    INSERT INTO users VALUES (31, 75);  

しかし、次のクエリを実行するとデッドロックが発生します。

  • トランザクション1(デッドロック発生)
    INSERT INTO users VALUES (16, 75);  
    INSERT INTO users VALUES (17, 75);  
    INSERT INTO users VALUES (18, 75);  
    INSERT INTO users VALUES (19, 75);  

この状況を図で表してみましょう。
id の範囲指定のため、 index は PRIMARY KEY が使われています。(EXPLAINでも確認しました)

id が 18~23 をロックしたのでロック範囲はこんな感じ(?)。

ですが、MySQL のネクストキーロックによって実際のロック範囲はこうなります。

で、今回 INSERT を行うとデッドロックが起きる範囲はここです。

再現2

また、score を使った index の方でも試してみましょう。
まずトランザクション1で score が 85~90 の user を選択し、悲観的ロックをかけます。

  • トランザクション1
    BEGIN;  
    SELECT * FROM users WHERE score BETWEEN 85 AND 90 FOR UPDATE;  
    +----+-------+  
    | id | score |  
    +----+-------+  
    | 30 |    87 |  
    +----+-------+  

トランザクション2でも同様に score が 85~90 の user を選択し、悲観的ロックをかけようとし、ロック待ちの状態にします。

  • トランザクション2
    BEGIN;  
    SELECT * FROM users WHERE score BETWEEN 85 AND 90 FOR UPDATE;  
    (ロック待ち)  

何となく想像は付くかもしれませんが、デッドロックが起こらないクエリ、起こるクエリは次のようになります。

  • トランザクション1(デッドロック発生せず)

    INSERT INTO users VALUES (任意, 81以下);  
    INSERT INTO users VALUES (19以下, 82);  
    INSERT INTO users VALUES (31以上, 87);  
    INSERT INTO users VALUES (任意, 88以下);  
  • トランザクション1(デッドロック発生)

    INSERT INTO users VALUES (21以上, 82);  
    INSERT INTO users VALUES (任意, 83~86);  
    INSERT INTO users VALUES (29以下, 87);  

さっきと同様で図にまとめると以下のようになります。

INSERT を行うとデッドロックになる範囲は以下の通り。

おわりに

この記事ではMySQL8.0が特定条件下でデッドロックが起きてしまう現象の条件を調査してみました。
もう少し原因を考察したり、条件をもっと詳しく絞ったりしたかったのですが、今回の記事はここで終わりたいと思います。
MySQL8.0をデフォルト設定で使用して、何か良く分からないデッドロックが発生したなあ?という時には参考にしてみてください!