【技術Tips】MySQL(InnoDB)でバルクインサート時にデッドロックが発生した場合に検討するべきこと

こんにちは！TECH Street編集部です。

TECH Streetでは、コミュニティメンバーが日々の学びや知見を「技術Tips」カテゴリで発信しています。

今回のテーマ

データベースの開発や運用において、サービスが成長していくのは嬉しいですが、同時にシステムがその成長に対応できるように常に改善していかなければなりません。特に、「本来起こってはいけないデッドロックが発生した場合の対処方法」を共有しておくことは、問題が起きた時に役立つと思います。

そこで、今回は「MySQL(InnoDB)でバルクインサート時にデッドロックが発生した場合に検討するべきこと」について、お届けします。

結論

• ギャップロックが発生しないよう、バルクインサートの順番を整理する
• 上記でも解消しない場合、影響を考慮しつつトランザクション分離レベルを緩和することを検討する

デッドロックの原因

デッドロックとは

デッドロックの概念に関しては、以下の記事がわかりやすく解説してくださっているので、説明はこちらに譲ります。

デッドロックとは

こちらにも書いてある通り、お互いがロック解除待ち状態となり、どちらも処理が進行できなくなる状態が発生した場合、デッドロックの状態となります。ロックにも種類があり、行ロック（レコードロック）やテーブルロックなどがありますが、今回取り上げたいのはギャップロックです。

ギャップロックとは

こちらも説明は以下の記事に任せます。

良く分かるMySQL Innodbのギャップロック - Qiita

こちらにもある通り、ギャップロックとはインデックスとインデックスの間にかけられるロックのことです。なぜ数あるロックのうちギャップロックを特別に取り上げたのかというと、今回のテーマであるバルクインサートと関連が深いからです。

上記の記事では、SELECT FOR UPDATEをインデックスの間で行うとギャップロックが取られ、その範囲にはINSERTが行えなくなる事例を取り上げていました。SELECT FOR UPDATEで明示的にロックを取得しているので、こうした事象が起こるのは納得が行きますよね。ただ、これがバルクインサートのように一見ロックを取得しに行っていないように見える処理においてもギャップロックが起こりうるのが注意点です。

対応策1: バルクインサートの順番を整理する

まずバルクインサートでギャップロックが起こるメカニズムを紹介している、下記の記事に目を通していただけたらと思います。

MySQLのBULK INSERTでデッドロックを回避する - Qiita

端的に言うと、バルクインサートの順番をあべこべにした結果、ギャップロックの取り合いが発生しているということです。ただ、逆に言えばインサート順をインデックスに沿う形にすることで、ギャップロックの発生を抑制することが可能です。もしかしたら今手元で発生している問題は、こちらの方法で解決するかもしれません。

Goで書くと、以下のようにバルクインサートに使うスライスの中身を昇順にする対応が可能です。

package main

import "sort"

type user struct {
    ID   int
    Name string
    Age  int
}

func main() {
    users := []user{
        {
            ID:   1,
            Name: "Tom",
            Age:  20,
        },
        {
            ID:   2,
            Name: "Alice",
            Age:  23,
        },
        {
            ID:   3,
            Name: "Bob",
            Age:  19,
        },
    }

    // バルクインサート時にギャップロックによるデッドロックが発生しないよう昇順にソート
    sort.Slice(users, func(i, j int) bool {
        a, b := users[i], users[j]
        return a.ID < b.ID
    })
}

下はPythonでの実装例です。

class User:
    def __init__(self, id: int, name: str, age: int) -> None:
        self.id = id
        self.name = name
        self.age = age

users = [
    User(1, 'Tom', 20),
    User(2, 'Alice', 23),
    User(3, 'Bob', 19)
]

### バルクインサート時にギャップロックによるデッドロックが発生しないよう昇順にソート
users.sort(key=lambda user: user.id)

対応策2: トランザクション分離レベルを緩和する

上記の対応でも解消しなかった場合、次の手段としてトランザクション分離レベルを緩和することが考えられます。トランザクション分離レベルに関しては下記の記事がわかりやすいと思います。

トランザクション分離レベルについてのまとめ - Qiita

ダーティリード、リピータブルリード、ファントムリードをちゃんと理解してからトランザクション分離レベルを理解しよう - Qiita

MySQL(InnoDB)のデフォルトのトランザクション分離レベルはREPEATABLE READです。なぜトランザクション分離レベルの緩和を検討する必要があるかというと、REPEATABLE READはインサート処理においてデフォルトでインデックス間のギャップロックを取得する仕様となっているからです(Oracle, PostgreSQL, SQL Server等はデフォルトのトランザクション分離レベルがREAD COMMITTEDとなっています。)。端的に言うと、ギャップロックを取得しないようにしよう、と言うわけです。今回はInnoDBを例に取って対応策を検討してみたいと思います。

デッドロックが発生したDB(MySQL)内のターミナル上で以下のコマンドを実行します。

SHOW ENGINE INNODB STATUS\\G

出力結果は以下の通りです(MySQLドキュメントからの抜粋です)。

~~
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...
~~

SHOW ENGINE INNODB STATUSに関しては、以下の記事が参考になると思います。

なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳

お気づきかもしれませんが、RECORD LOCKS space id 31 page no 3 n bits 72 indexPRIMARYof tabletest.childtrx id 8731 lock_mode X locks gap before rec insert intention waitingと書かれているように、ギャップロックを取得しようとしてロック待ち状態になっているのがわかると思います。インデックスの順番通りにバルクインサートしているのにも関わらず、デッドロックが回避できない、となった場合は、繰り返しになりますが、トランザクション分離レベルの緩和を検討してみると良いと思います。

それでは、GoとPythonそれぞれを例に挙げて対応策を紹介したいと思います。

• Go(Gorm)

package main

import (
    "database/sql"

    "gorm.io/gorm"
)

type SQLHandler struct {
    conn *gorm.DB
}

func (handler *SQLHandler) BeginTx(opts ...*sql.TxOptions) *gorm.DB {
    var opt *sql.TxOptions
    if len(opts) > 0 {
        opt = opts[0]
    }
    return handler.conn.Begin(opt)
}

func doSomething(tx *gorm.DB) error

func (handler *SQLHandler) DoSomething() {
    // トランザクションの開始
    // バルクインサート時のギャップロック発生を抑制するため、トランザクション分離レベルをデフォルトのREPEATABLE READからREAD COMMITTEDに変更
    tx := handler.BeginTx(&sql.TxOptions{Isolation: sql.LevelReadCommitted})

    // データベース上で何かしらの処理を実行
    err := doSomething(tx)
    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }()
}

実装例を挙げると上記のようになると思います。tx := handler.BeginTx(&sql.TxOptions{Isolation: sql.LevelReadCommitted})のように、オプション用の構造体にトランザクション分離レベルをフィールドで指定したものを引数に渡せば良いです。ここで作られたトランザクションが影響する処理は、全てトランザクション分離レベルがREAD COMMITTEDとなり、バルクインサートによるギャップロックを抑制できます。

• Python(SQLAlchemy)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

connection_string = f"mysql://{DB_USERNAME}:{DB_PASSWORD}@{DB_ENDPOINT}:{DB_PORT}/{DB_DBNAME}?charset=utf8mb4"
engine = create_engine(
    connection_string,
    isolation_level="READ COMMITTED",
)
factory = sessionmaker(bind=engine)
session = factory()

create_engine関数の引数にisolation_level="READ COMMITTED"を追加するのみの対応で大丈夫です。Go(Gorm)の例と同様、ここで作られたセッションが影響する処理は全てトランザクション分離レベルがREAD COMMITTEDとなり、バルクインサートによるギャップロックを抑制できます。

注意点

では最初からトランザクション分離レベルを緩和すれば良いのでは？と思うかもしれませんが、緩和する際には注意点があります。それは、トランザクション分離レベルをREPEATABLE READからREAD COMMITTEDに緩和することによって、ファジーリード（ノンリピータブルリード）とファントムリードを許容していると言う点です(*1)。

例えば、ファジーリードによってトランザクション実行中でも他のトランザクションがコミットした特定のレコードに対する変更結果は即時反映されることになります。対象として扱うレコードが互いに独立している場合は問題ないですが、例えば同じレコードの値を参照して集計などを行いたい場合は、他のトランザクションのコミット結果の影響を受けてしまうことになります。

トランザクション分離レベルを緩和する際は、影響を受ける処理がその緩和により許容できるものかどうかの検討を慎重に行う必要があります。まずはバルクインサートの順番を整理し、それでもデッドロックの解消が見込めない場合の代替案として検討するのが良いと思います。

*1: REPEATABLE READにおいても、ファントムリードは許容されています。

まとめ

今回は「バルクインサートでデッドロックが発生した場合に検討するべきこと」という題目で記事を書かせていただきました。サービスがスケールするに従って予想だにしなかった箇所がボトルネックとなり障害が発生することは少なからずあると思います。その際にこちらの記事が対応策立案の一助になれば幸いです。

記事執筆者

梅本 誠也 さん

パーソルキャリア株式会社
テクノロジー本部 デジタルテクノロジー統括部 デジタルソリューション部 クライアントエンジニアグループ

韓国で5年間正規留学し、その間に業務委託で機械学習とデータエンジニアリング方面の開発を経験。新卒でアプリケーションエンジニアとしてフロントエンド、バックエンド、インフラを幅広く経験。パーソルキャリア入社後はデータエンジニアとして、社内のデータ分析基盤の構築と運用保守を担当。一方で、生成系AIを用いたアプリケーション開発にも携わっている。