tech.kayac.com Advent Calendar 2015 8日目担当の @m0t0k1ch1 です。
今年は MySQL さんにいろいろ泣かされた1年でした。特に、あの夏、幾夜をともにした実行計画のゆらぎは一生忘れな(ry
さて。。。今回は、そんな中でも誰もが一度は嵌るであろう（？）MySQL（InnoDB）の REPEATABLE READ の嵌りどころについて自分なりにまとめてみようと思います。
この記事が目指すこと
MySQL の各トランザクション分離レベルの嵌りどころについては、現状でも種々のブログ記事などでまとめられており、そんなに真新しい話ではないと思います。が、過去の自分のように、嵌ったことがない人間からすると、
- 話としてはなんとなく理解できる
- が、抽象的でいまいち身につかない
というのが現実でした。嵌りどころに実際に嵌った今読み返してみると、「あーーー、、、そういうことだったのか。。。」という思いなのですが。。。
なので、今回は、嵌りどころを理解する前の自分を読者として想定し、そんな自分でも理解できるよう、具体例に落としてわかりやすく説明することを目的としたいと思います。
この記事を通して、表題の通りのことが実現できたら幸いです。
前提
この記事における検証は、以下の環境で実行しています。
- MySQL のバージョン：5.6.27
- ストレージエンジン：InnoDB
- トランザクション分離レベル：REPEATABLE READ
$ mysql --version mysql Ver 14.14 Distrib 5.6.27, for osx10.11 (x86_64) using EditLine wrapper
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
そもそも REPEATABLE READ とは？
現状、MySQL 5.5.5 以降におけるデフォルトのストレージエンジンは InnoDB であり、その InnoDB のデフォルトのトランザクション分離レベルは REPEATABLE READ となっています。
ここではトランザクション分離レベルについて詳しく説明しませんが、以下を読み進めるにあたっては、
- 今の MySQL はデフォルトで REPEATABLE READ という設定になっている
くらいの理解でよいかと思います。
もっと深みに足を踏み入れたい方は、まず、伝説の いちりんめも に目を通すとよいかと思います。
REPEATABLE READ の嵌りどころとは？
さて、そのデフォルトの設定における嵌りどころであり（何気なく MySQL を使っている人は誰でも嵌る可能性がある）、今回説明したいのが以下です。
- トランザクション開始後の最初の読み取りによってスナップショットが確立されるため、同一トランザクション内では他のトランザクションによる更新の影響を受けずに、最初に確立したスナップショットのデータを参照することができる（一貫性読み取り）
- 「SELECT ~ FOR UPDATE を用いた locking read」と「non-locking read」で結果が異なる（完全な一貫性読み取りではない）
後者についてもう少し詳細に説明すると、以下のようになります。
- 「SELECT ~ FOR UPDATE を用いた locking read」は、一貫性読み取り とは違い、スナップショットが確立されていたとしても、他のトランザクションによる更新の影響を受ける（ファントムリード）
このあたりについては、まさに同じことが こちらのエントリ にも書かれています。
まず、locking read については軽く説明した方がよいと思うので以下で少し触れ、その後、コマンドラインで実際の挙動を確認してみます。
locking read について
SELECT ~ FOR UPDATE を用いた locking read の使いどころとして、基本的には以下のようなニーズがある場合が想定されます。このようなニーズは普段コードを書いているときにもよく出てくるのではないでしょうか？
- あるトランザクション内において、他のトランザクションからの更新をブロックしたい
locking read を用いることで、複数のトランザクションが同一のデータにほぼ同時に更新をかけるような場合にも、上記のように他のトランザクションからの更新をブロックしつつ、それぞれの処理を排他的に行うことができます。
コマンドラインで検証してみる
後でもっと具体的な例を出しますが、一旦コマンドラインで嵌りどころの挙動を確かめてみます。
対象とするテーブルはこんな感じです。
mysql> DESC user; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | team_id | int(11) unsigned | NO | | 0 | | | name | varchar(191) | NO | | | | +---------+------------------+------+-----+---------+----------------+
以下、トランザクション A（：TA）とトランザクション B（：TB）という2つのトランザクションからクエリを実行していると思ってください。
一貫性読み取り
以下のように、TA がコミットされる前に TB で読み取りを行っていた場合、TA による更新は TB には反映されません（TA がコミットされる前に TB で読み取りを行っていなかった場合は、TA による更新が反映されます）。
TA> BEGIN; TB> BEGIN; TB> SELECT * FROM user WHERE id = 1; +----+---------+-----------+ | id | team_id | name | +----+---------+-----------+ | 1 | 1 | ぽよこ | +----+---------+-----------+ TA> UPDATE user SET name = 'ぴよこ' WHERE id = 1; TA> COMMIT; TB> SELECT * FROM user WHERE id = 1; +----+---------+-----------+ | id | team_id | name | +----+---------+-----------+ | 1 | 1 | ぽよこ | +----+---------+-----------+ TB> COMMIT;
完全な一貫性読み取りではない
以下のように、TA がコミットされる前に TB で読み取りを行っていた場合であっても、SELECT ~ FOR UPDATE による locking read の場合は、TA による更新が TB 内で参照できます。
TA> BEGIN; TB> BEGIN; TB> SELECT * FROM user WHERE id = 1; +----+---------+-----------+ | id | team_id | name | +----+---------+-----------+ | 1 | 1 | ぽよこ | +----+---------+-----------+ TA> UPDATE user SET name = 'ぴよこ' WHERE id = 1; TA> COMMIT; TB> SELECT * FROM user WHERE id = 1 FOR UPDATE; +----+---------+-----------+ | id | team_id | name | +----+---------+-----------+ | 1 | 1 | ぴよこ | +----+---------+-----------+ TB> COMMIT;
具体例に落として検証してみる
以上で嵌りどころに関する挙動についてはざっくり説明できたと思います。
次は、一貫性読み取り に関して、この記事の目的に沿ってもう少し具体的なケースで検証してみます。
お題
今から、MySQL を使って以下のようなアプリケーションをつくるとします。
- 指定したチームに紐づくタスクが全て完了状態であった場合、新しいタスクを追加する
- 1つのチームは複数のユーザーから構成される
- 1つのチームは複数のタスクを所持している
- それぞれのタスクにはその状態（完了・未完了）を管理するフラグが定義されている
テーブル構成は以下のようになっているとします。user は先ほどコマンドラインでの検証に使ったものと同じです。
mysql> DESC team; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(191) | NO | | | | +-------+------------------+------+-----+---------+----------------+ mysql> DESC user; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | team_id | int(11) unsigned | NO | | 0 | | | name | varchar(191) | NO | | | | +---------+------------------+------+-----+---------+----------------+ mysql> DESC team_task; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | team_id | int(11) unsigned | NO | | 0 | | | is_completed | tinyint(4) | NO | | 0 | | +--------------+------------------+------+-----+---------+----------------+
検証
データを用意する
チーム「ぽよぽよ」に3人のユーザー（ぽよこ・ぽよすけ・ぽよひこ）が所属しているとします。タスクはまだ1つもありません。
mysql> SELECT * FROM team; +----+--------------+ | id | name | +----+--------------+ | 1 | ぽよぽよ | +----+--------------+ mysql> SELECT * FROM user WHERE team_id = 1; +----+---------+--------------+ | id | team_id | name | +----+---------+--------------+ | 1 | 1 | ぽよこ | | 2 | 1 | ぽよすけ | | 3 | 1 | ぽよひこ | +----+---------+--------------+ mysql> SELECT COUNT(*) FROM team_task WHERE team_id = 1; +----------+ | COUNT(*) | +----------+ | 0 | +----------+
検証用のコードを書いてみる
今回は、チーム「ぽよぽよ」に所属する3人のユーザー（ぽよこ・ぽよすけ・ぽよひこ）がほぼ同じタイミングでお題のアプリケーションを利用した場合を想定して、検証用のコードを Golang でざっくり書いてみます。
できあがったのが以下です。上記の想定ケースは goroutine を用いてシミュレーションしています。
package main
import (
"database/sql"
"log"
"sync"
_ "github.com/go-sql-driver/mysql"
"github.com/shogo82148/txmanager"
)
func tryAddTask(dbm txmanager.DB, userID int) error {
var q string
// トランザクションを開始する
tx, _ := dbm.TxBegin()
defer tx.TxFinish()
// user_id から team_id を取得する
var teamID int
q = "SELECT team_id FROM user WHERE id = ?"
if err := tx.QueryRow(q, userID).Scan(&teamID); err != nil {
tx.TxRollback()
return err
}
// 重複して team_task の追加が行われないように team を locking read する
q = "SELECT id FROM team WHERE id = ? FOR UPDATE"
if err := tx.QueryRow(q, teamID).Scan(&teamID); err != nil {
tx.TxRollback()
return err
}
// 未完了の team_task の数を取得する
var taskNum int
q = "SELECT COUNT(id) FROM team_task WHERE team_id = ? AND is_completed = 0"
if err := tx.QueryRow(q, teamID).Scan(&taskNum); err != nil {
tx.TxRollback()
return err
}
// 未完了の team_task の数を出力する
log.Printf("taskNum: %d", taskNum)
// 未完了の team_task の数が 0 だった場合は team_task を追加する
if taskNum == 0 {
q = "INSERT INTO team_task (team_id, is_completed) VALUES (?, 0)"
if _, err := tx.Exec(q, teamID); err != nil {
tx.TxRollback()
return err
}
}
// トランザクションをコミットする
return tx.TxCommit()
}
func main() {
db, err := sql.Open("mysql", "root:@/test")
if err != nil {
log.Fatal(err)
}
defer db.Close()
dbm := txmanager.NewDB(db)
// 3人のユーザーがほぼ同時にアプリケーションを利用した場合を想定
var wg sync.WaitGroup
for i := 1; i <= 3; i++ {
wg.Add(1)
go func(userID int) {
if err := tryAddTask(dbm, userID); err != nil {
log.Fatal(err)
}
wg.Done()
}(i)
}
wg.Wait()
return
}
検証用のコードを走らせてみる
正常に動きそうな雰囲気の漂う上記のコードを main.go として保存して実際に走らせてみます。
きちんと排他的に処理を行うことができていれば、「3人のユーザーがほぼ同時に tryAddTask を実行しても、追加されるタスクは1つだけ」というのが期待される挙動となります。
ところが、、、
$ go run main.go 2015/12/08 01:02:25 taskNum: 0 2015/12/08 01:02:25 taskNum: 0 2015/12/08 01:02:25 taskNum: 0
mysql> SELECT * FROM team_task WHERE team_id = 1; +----+---------+--------------+ | id | team_id | is_completed | +----+---------+--------------+ | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 0 | +----+---------+--------------+
このように、タスクが3つ追加されてしまいました。
検証用のコードの問題点
ここまで読んでくださった方なら何を見落としているかもうわかっていると思いますが、検証用のコードは以下の点をうまく考慮できていないコードとなっています。
- トランザクション開始後、team を locking read する前に user を参照してしまっているため、この時点でスナップショットが確立される
team を locking read しているため、一見、排他的に処理が行えているように見えますが、user を参照した時点で確立されたスナップショットから 一貫性読み取り が行われるため、team_task の数を取得しているクエリは最初に確立されたスナップショットのデータを参照します。
つまり、3人のユーザーがほぼ同時に tryAddTask を実行した場合、
- それぞれのトランザクションがほぼ同時に開始する
- team_task の追加がコミットされる前にそれぞれのトランザクションでスナップショットが確立される
- 一貫性読み取り により、それぞれのトランザクション内では team_task がまだ追加されていないと判断される
- team_task が重複して追加される
このようなことが起こる可能性が十分にあるということになります。
検証用のコードを修正してみる
ここまでで得た知見を踏まえると、例えば（user.team_id が更新されない前提がある場合には）、
- トランザクションが開始する前に user を参照して user.team_id を取得し、トランザクション開始直後に team を locking read する
ように修正することで、期待通りの動きをさせることができると考えられます。
実際に上記の修正を加えたコードを書いてみます。
package main
import (
"database/sql"
"log"
"sync"
_ "github.com/go-sql-driver/mysql"
"github.com/shogo82148/txmanager"
)
func tryAddTask(dbm txmanager.DB, userID int) error {
var q string
// user_id から team_id を取得する
var teamID int
q = "SELECT team_id FROM user WHERE id = ?"
if err := dbm.QueryRow(q, userID).Scan(&teamID); err != nil {
return err
}
// トランザクションを開始する
tx, _ := dbm.TxBegin()
defer tx.TxFinish()
// 重複して team_task の追加が行われないように team を locking read する
q = "SELECT id FROM team WHERE id = ? FOR UPDATE"
if err := tx.QueryRow(q, teamID).Scan(&teamID); err != nil {
tx.TxRollback()
return err
}
// 未完了の team_task の数を取得する
var taskNum int
q = "SELECT COUNT(id) FROM team_task WHERE team_id = ? AND is_completed = 0"
if err := tx.QueryRow(q, teamID).Scan(&taskNum); err != nil {
tx.TxRollback()
return err
}
// 未完了の team_task の数を出力する
log.Printf("taskNum: %d", taskNum)
// 未完了の team_task の数が 0 だった場合は team_task を追加する
if taskNum == 0 {
q = "INSERT INTO team_task (team_id, is_completed) VALUES (?, 0)"
if _, err := tx.Exec(q, teamID); err != nil {
tx.TxRollback()
return err
}
}
// トランザクションをコミットする
return tx.TxCommit()
}
func main() {
db, err := sql.Open("mysql", "root:@/test")
if err != nil {
log.Fatal(err)
}
defer db.Close()
dbm := txmanager.NewDB(db)
// 3人のユーザーがほぼ同時にアプリケーションを利用した場合を想定
var wg sync.WaitGroup
for i := 1; i <= 3; i++ {
wg.Add(1)
go func(userID int) {
if err := tryAddTask(dbm, userID); err != nil {
log.Fatal(err)
}
wg.Done()
}(i)
}
wg.Wait()
return
}
一度 team_task を全て削除した後で修正したコードを走らせると、以下のような結果が得られます。
$ go run main.go 2015/12/08 01:25:09 taskNum: 0 2015/12/08 01:25:09 taskNum: 1 2015/12/08 01:25:09 taskNum: 1
mysql> SELECT * FROM team_task WHERE team_id = 1; +----+---------+--------------+ | id | team_id | is_completed | +----+---------+--------------+ | 1 | 1 | 0 | +----+---------+--------------+
期待通り、1人のユーザーがタスクを追加した後、他の2人のユーザーのタスク追加はスキップされました。
まとめ
- MySQL の REPEATABLE READ の嵌りどころである 一貫性読み取り と 完全な一貫性読み取りではない について説明し、コマンドラインを用いてその挙動を検証しました
- 一貫性読み取り については、実際にどういう嵌り方をするのかをより具体的に示すため、検証用のコードを用いてその挙動を検証しながら説明を行いました
個人的な見解
以上を踏まえて、個人的には以下のようにするのがよいかなあと考えています。
- 同一のデータにほぼ同時に更新をかけるような複数のトランザクションにおいて、他のトランザクションによる更新を加味した処理を行いたい場合は、トランザクション開始直後に適切なデータを locking read する
とはいえ、例えばトランザクションをネストしているような複雑なアプリケーションの場合、上記のルールを適用しきれないケースも十分あり得ると思うので、話はそう簡単ではないなあと。。。実際、自分も所属するプロジェクトにおいてこのことを身をもって痛感していますし、もちろん、locking read を用いた分、実行待ちのクエリは増えてしまうという現実もあります。
が、もし自分が新しいプロジェクトに参画してアーキテクチャレベルのコードから書くようなことがあれば、このあたりのことには気を遣って、後からコードを書く人が迷わないような仕組みをつくってみたいものです。
