MySQLで発生し得る思わぬデッドロックと対応方法

はじめに

この記事は実際の業務で発生した MySQL のデッドロックとそのいくつかの回避方法や対応方法を（テーマは変えて）手元で実行できるコードを用いて解説する記事です。具体的には「トランザクション張っておけば大丈夫」と思ってませんか？ バグの温床になる、よくある実装パターンの記事で紹介されている「1on1 チャットサービス」で紹介されているデッドロックとデータベースレイヤでは同じ状況だったのですが、記事で紹介されている方法とは別の方法でデッドロックを回避する必要があったため、同じ状況に遭遇した人の助けになればという思いで記事を書きました。また、こちらの記事が無ければ私自身も現象を理解するのにもっと苦労したと思うので、この場を借りてお礼申し上げます！

出金サービス履歴登録サービスを例に考える

コードと説明が https://github.com/shuntagami/withdrawal_service においてあるので先にこちらにざっと目を通しておいてください。README.mdに記載の通り非常に単純なシステムです。API 実装とテストには Golang を使い、ORM としてGormを使っていますが、ほとんどが生の SQL を実行しているだけなので Go 言語を使ったことがなくても理解いただける内容ではあると思います。

基本方針

「ユーザーの出金合計金額が 10 万円を超えないか確認 => 出金履歴を登録」という流れが基本方針です。しかしながら、例えば「6 万円を出金する」というリクエストが同時に複数あった場合に、確実に一つのリクエストのみが成功して合計出金金額が 10 万円を超えないようにするには何らかの工夫をする必要があります。その方法の一つとしてデータベースのトランザクションがあるのですが、「トランザクション張っておけば大丈夫」と思ってませんか？ バグの温床になる、よくある実装パターンの題名の通り、「トランザクションを張れば大丈夫」というわけではありません。

デッドロックの発生し得る実装

私がこの問題に遭遇する前に最初に行った実装が https://github.com/shuntagami/withdrawal_service/pull/2 にあります。(以下主要ロジックを抜粋)

// 一日の最大出金金額を超えてないかチェックして出金履歴を登録するトランザクション開始
tx := db.Conn.Begin()

// ユーザーの合計出金金額をSELECT...FOR UPDATEで取得
var sumAmount sql.NullInt64
if err := tx.Raw("SELECT SUM(amount) FROM histories WHERE user_id = ? FOR UPDATE", req.UserID).Scan(&sumAmount).Error; err != nil {
  tx.Rollback()
  c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
  return
}

// 最大出金金額を超えていたら400を返す
if int(sumAmount.Int64)+req.Amount > model.AmountLimit {
  tx.Rollback()
  c.JSON(http.StatusBadRequest, gin.H{"msg": "over the amount limit 100,000"})
  return
}

// 出金履歴登録
if err := tx.Create(&history).Error; err != nil {
  tx.Rollback()
  c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
  return
}

// トランザクションコミット
if err := tx.Commit().Error; err != nil {
  tx.Rollback()
  c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
  return
}

例えば「既に合計 1 万円の出金を行ったユーザーが 6 万円の出金リクエストを同時に二つ行おうとした場合」は以下のようになります。

図 1 合計 10 万を超える出金リクエストがあった場合はロールバックする

図 1 のB2の判定で「7 万円+6 万円」で NG となりロールバックされるので問題なさそうです。早速ですがテストを実行してみましょう。（実際に手元で実行したい方はリポジトリを clone してdocker comose upコマンドで DB サーバーと API サーバーを起動してください）

$ gh pr checkout 2 # 作業ブランチに移動
$ go test ./... -count 10 # ホットリロードが完了してからテストを実行してください

おそらく 10 回のうち何回かはテストが fail することが確認できると思います。エラーログを見るといくつかのリクエストがError 1213: Deadlock found when trying to get lock; try restarting transactionというエラーメッセージで fail しているのがわかると思います。

なぜデッドロックが発生してしまうのでしょうか？実はこのデッドロックの発生原因は https://zenn.dev/tockn/articles/4268398c8ec9a9#デッドロックの原因を探る と全く同じで、「SELECT...FOR UPDATE 句の取得結果が NULL であった場合にネクストキーロックとして user_id の「-∞ ～ +∞（ギャップ上）」へ排他ロックがかかる。さらにギャップ X ロックの効果はギャップ S ロックと同じである^[1]ため、この共有ロックが重複した場合にデッドロックとなり得る。」というのが原因となります。（図 2）共有ロックは重複してかけることができるので図 1 のように B1 が待ち状態にならないことに注意してください。

図 2 デッドロックの発生

デッドロックの解消・対応方法と問題点（本題）

ようやく本題です。https://zenn.dev/tockn/articles/4268398c8ec9a9#解決方法-1 では以下の以下の 4 つの対応方法が紹介されています。

• 再試行する
• 分離レベルを下げる
• テーブルロックする
• 諦める（デッドロックを許容する）

どれを選択するべきかは状況により異なるとは思いますが、現実世界での運用を考えると「そもそもデッドロックを発生させたくない」という要件状況もあると思います。さらにパフォーマンスを考慮するとテーブルごとロックするのは避けたいとします。とすると、「分離レベルを下げる」というのが選択肢としてはよさそうですが、「トランザクションの分離レベルを変えることのトレードオフ」について考慮する必要があります。この辺の説明はトランザクション分離レベルについてのまとめにわかりやすくまとまっていると思います。そして、今回題材としている「出金履歴登録サービス」では問題となるケースが存在します。早速試してみましょう。（https://github.com/shuntagami/withdrawal_service/pull/3 に実装があります）

$ gh pr checkout 3 # 作業ブランチに移動
$ go test ./... -count 10 # ホットリロードが完了してからテストを実行してください

テストを pass したのではないでしょうか？次に、main_test.goを以下のように編集してください。

diff --git a/main_test.go b/main_test.go
index f005b85..38ed731 100644
--- a/main_test.go
+++ b/main_test.go
@@ -89,7 +89,7 @@ func request(uID int) (*http.Request, error) {
        buffer := bytes.NewBuffer(make([]byte, 0, 128))
        if err := json.NewEncoder(buffer).Encode(History{
                UserID: uID,
-               Amount: 10000,
+               Amount: 60000,
        }); err != nil {
                return nil, err
        }

一回のリクエストの出金金額を 1 万円から 6 万円に変更しました。とはいえ、「出金金額合計は最大 10 万円」という仕様は変わらず満たす必要があります。実際にテストを実行してみましょう。

go test ./... -count 10

以下のようにテストは fail したのではないでしょうか？10 万円を超える出金がされてしまっています。

 main_test.go:84:
        	Error Trace:	/Users/shuntagami/projects/withdrawal_service/main_test.go:84
        	Error:      	Should be true
        	Test:       	TestCreateHistory
        	Messages:   	user:1 amount 120000 over the amountLimit 100000

分離レベルを下げ、ギャップロックを無効化することでデッドロックを回避できたものの、SELECT...FOR UPDATE句の取得結果が NULL であった場合にロックがかけられない（ロックする行がない）ため、trasactionBにおけるB1で取得した user1 の合計送金金額とB3にて insert する直前の合計金額が異なるために 10 万円を超えて出金が行われてしまいました。（図 3）このように「あるトランザクションでの書き込みが他のトランザクション中の検索クエリの結果を変化させてしまう効果」をファントムと呼び^[2]、このファントムを許可することが前述の「トランザクションの分離レベルを変えることのトレードオフ」の一つです。

図 3 合計 10 万円を超えた出金が行われる

じゃあどうするか（結論: 親テーブルの行ロックを事前に取得する）

https://github.com/shuntagami/withdrawal_service/pull/1 に実装があります。

今回の場合だと「出金金額合計 10 万円」というのはユーザー単位の話なので、「事前に user リソースの行ロックを取得しておけば良い」というのが結論となります。つまり、SELECT SUM(amount) FROM histories WHERE user_id = ? FOR UPDATEの箇所でhistoriesテーブルに対して行ロックをかけるのではなくSELECT id FROM users WHERE id = ? FOR UPDATEというクエリによって該当ユーザーの行ロックを取得すればいいわけです。また、主キーで選択したユーザーは一位に定まるため、1 行ロックのみで済み、ギャップロックも発生しません^[3]。

// 事前にuserレコードのロックを取得する
if err := tx.Exec("SELECT id FROM users WHERE id = ? FOR UPDATE", req.UserID).Error; err != nil {
	tx.Rollback()
	c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
	return
}

// ユーザーの合計出金金額を取得(userに対してロックをかけたのでhistoriesテーブルに対する行ロックは不要)
var sumAmount sql.NullInt64
if err := tx.Raw("SELECT SUM(amount) FROM histories WHERE user_id = ?", req.UserID).Scan(&sumAmount).Error; err != nil {
	tx.Rollback()
	c.JSON(http.StatusInternalServerError, gin.H{"msg": err.Error()})
	return
}

// 最大出金金額を超えていたら400を返す
if int(sumAmount.Int64)+req.Amount > model.AmountLimit {
	tx.Rollback()
	c.JSON(http.StatusBadRequest, gin.H{"msg": "payment required"})
	return
}

$ gh pr checkout 1 # 作業ブランチに移動
$ go test ./... -count 10 # ホットリロードが完了してからテストを実行してください

何回実行しても、また出金金額を変えたとしてもテストを pass するはずです。

トランザクションやロックを使わないアプローチ（余談）

https://github.com/shuntagami/withdrawal_service/pull/4 に実装があります。

var m sync.Mutex

func CreateHistory(c *gin.Context) {
	m.Lock()
	defer m.Unlock()
	// 以下省略。排他制御ができているのでトランザクションを張らなくてよい。

実は今回扱ったテーマに関して「テストをパスさせる」ということに焦点を当てると DBMS のトランザクションやロックを使わずに、Go 言語のsync.Mutexによる排他制御によりアプリケーションレベルで制御することも可能です。しかしながら、この方法には以下のような問題点があります。

• 全ての処理を直列で実行するのでパフォーマンスが悪そう
• API サーバーに冗長性を持たせていた場合、「1 サーバーごとの排他制御」となってしまうので意味をなさない

1 点目のパフォーマンスに関しては計測しないと断定はできないですし、計測した結果許容範囲であるということもありますが、2 点目はつまり「全くスケールできない」という近年のアプリケーションとしては採用が難しい問題があるので余談としました。

感想等

今回紹介したデッドロックの発生というのは同時リクエストのテストを書くまでは、発見はおろか、知識としても持っていませんでした。トランザクションを使った処理やお金相当を扱う絶対にバグを避けたい処理では「同時リクエストが発生した際のテスト」というのを書くことをオススメします。また、そのような処理（非同期処理）を簡単に書ける Go 言語は（他にも理由はありますが）やはり魅力的だなあと改めて感じました！

脚注

1. https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html

2. データ指向アプリケーションデザイン 7.2.4.3

3. https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html