障害になる前に知っておくべき、sql.Rowsのコネクション管理

標準パッケージdatabase/sqlを使用したDB操作では、コネクションが適切に開放されるよう注意する必要があります。誤った実装をすると、SQLの実行が終了してもコネクションが開放されず、コネクションプールが枯渇してしまいます。その結果、新規SQLを実行できずアプリケーションが応答しなくなり、システム障害になる可能性があります。

今回は、コネクションの開放において、database/sqlパッケージのRows構造体(sql.Rows)を利用する際に知っておくべきことを、アンチパターンと共に紹介したいと思います。

コネクションプールについて軽くおさらい

database/sqlパッケージのDB構造体(sql.DB)のQueryメソッドやExecメソッドを呼び出すと、sql.DBはコネクションプールから利用可能な接続(idleな接続)を取得するか、ない場合は新規接続を作成します。接続が不要になるとクローズして、接続をプールに返します。

オープンな接続の最大数はデフォルトで無限ですが、db.SetMacOpenConns()で最大接続数を設定可能です。新規接続取得時、最大接続数に達して利用可能なプールがない場合は、既存の接続が空くまで待機します。最大接続数を設定する場合は、パフォーマンス低下やデッドロックにならないよう設定値をチューニングする必要があります。

コネクションプールについてはこちらの記事が詳しいです。

動作環境

OS: macOS Sonoma 14.6.1
Golang: 1.22.5 darwin/arm64
MySQL: 8.0.34
です。

使用するテーブル

説明用に2つテーブルが必要だっただけなので、テーブルの意味はあまり気にしなくてよいです。

customerテーブル

顧客を管理するcustomerテーブルです。2レコードあります。

id	name
1	customer1
2	customer2

orderテーブル

顧客の注文を管理するorderテーブルです。データは空です。

id	customer_id

アンチパターン1: rows.Next()の途中でループを抜ける

以下のコードはrows.Next()の内でエラーになり途中でループを抜けるコードです。
最大接続数は2に設定しています。

main.go

func main() {
	db, _ := sql.Open("mysql",
		"root:@tcp(127.0.0.1:3306)/sample")
	defer db.Close()

	db.SetMaxOpenConns(2) // 最大接続数=2

	err := runSQL(db)
	slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)

	err = runSQL(db)
	slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)

	err = runSQL(db)
	slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)
}

func runSQL(db *sql.DB) error {
	var (
		id    int
		name  string
		name2 string
	)
	rows, _ := db.Query("SELECT id, name FROM customer")

	for rows.Next() {
		// 存在しないカラムをScanするとエラーになる
		if err := rows.Scan(&id, &name, &name2); err != nil {
			return err
		}
	}

	return nil
}

このコードの実行結果は以下です。runSQLの実行のたびにコネクション数が増えています。コネクションがクローズされないので、2つ目のrunSQLで最大コネクション数に達し、3つ目のrunSQLはコネクション空き待ちになり処理は終了しません。

INFO runSQL connections=1 err="sql: expected 2 destination arguments in Scan, not 3"
INFO runSQL connections=2 err="sql: expected 2 destination arguments in Scan, not 3"

解説

rows.Next()はDBから取得したレコードを1行づつ読み込んでいます。最後の行まで読み込み、次の行を読み込んだ際にEOF errorになった場合rows.Close()が呼ばれコネクションがクローズします(もしくは、Next()メソッド内でエラーが発生したとき)。
上記の例では、rows.Next()のループを途中で抜けているためコネクションがクローズされず、最大コネクション数に達しました。

対応策

defer rows.Close()呼ぶようにしましょう。これによりループを途中で抜けても必ずrows.Close()が呼ばれコネクションがクローズされます。

main.go

defer rows.Close()
for rows.Next() {
    // 存在しないカラムをScanするとエラーになる
    if err := rows.Scan(&id, &name, &name2); err != nil {
        return err
    }
}

実行結果

INFO runSQL connections=0 err="sql: expected 2 destination arguments in Scan, not 3"
INFO runSQL connections=0 err="sql: expected 2 destination arguments in Scan, not 3"
INFO runSQL connections=0 err="sql: expected 2 destination arguments in Scan, not 3"

アンチパターン2: 戻り値Rowsを無視する

以下のコードはdb.Query()でINSERTを実行しているコードです。
最大接続数は2に設定しています。

main.go

func main() {
	db, _ := sql.Open("mysql",
		"root:@tcp(127.0.0.1:3306)/sample")
	defer db.Close()

	db.SetMaxOpenConns(2) // 最大接続数=2

	_, err := db.Query("INSERT INTO customer (name) VALUES ('Customer3')")
	slog.Info("runSQL", "err", err, "connections", db.Stats().InUse)

	_, err = db.Query("INSERT INTO customer (name) VALUES ('Customer4')")
	slog.Info("runSQL", "err", err, "connections", db.Stats().InUse)

	_, err = db.Query("INSERT INTO customer (name) VALUES ('Customer5')")
	slog.Info("runSQL", "err", err, "connections", db.Stats().InUse)
}

このコードの実行結果は以下です。アンチパターン1と同様にSQLの実行が終了してもコネクションがクローズされず最大コネクション数に達し、コネクション空き待ちになり処理が終了しません。

INFO runSQL connections=1 err=<nil>
INFO runSQL connections=2 err=<nil>

解説

Query()はsql.Rows構造体を返します。これはDBへのコネクションを維持したままです。無視するとクローズされないままです。

対応策

INSERT、UPDATE、DELETEで実行結果を利用しない場合は、db.Exec()を使用しましょう。db.Exec()はsql.Rows構造体を返しません。

main.go

_, err := db.Exec("INSERT INTO customer (name) VALUES ('Customer3')")
slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)

_, err = db.Exec("INSERT INTO customer (name) VALUES ('Customer4')")
slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)

_, err = db.Exec("INSERT INTO customer (name) VALUES ('Customer5')")
slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)

実行結果

INFO runSQL connections=0 err=<nil>
INFO runSQL connections=0 err=<nil>
INFO runSQL connections=0 err=<nil>

アンチパターン3: rows.Next()内でクエリを実行する

以下のコードは、SELECTで取得したデータをもとにINSERTを実行するコードです。SELECTとINSERTはrunSQL関数内で実行しており、runSQLはgoroutineで並列実行しています。defer rows.Close()でクローズしており、INSERTはExec()で実行しているので、一見問題なさそうです。
最大接続数は2に設定しています。

main.go

func main() {
	db, _ := sql.Open("mysql",
		"root:@tcp(127.0.0.1:3306)/sample")
	defer db.Close()

	db.SetMaxOpenConns(2) // 最大接続数=2

	var wg sync.WaitGroup
	wg.Add(2)

	go func() {
		defer wg.Done()
		err := runSQL(db)
		slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)
	}()
	go func() {
		defer wg.Done()
		err := runSQL(db)
		slog.Info("runSQL", "connections", db.Stats().InUse, "err", err)
	}()

	wg.Wait()
}

func runSQL(db *sql.DB) error {
	var id, sleepResult int
	// 再現性のためにSLEEPで1秒遅らせている
	rows, _ := db.Query("SELECT id, SLEEP(1) FROM customer WHERE name = 'Customer1'")

	defer rows.Close()
	for rows.Next() {
		if err := rows.Scan(&id, &sleepResult); err != nil {
			return err
		}
  		// 上記SELECT文で取得したidを使ってINSERT文を実行
		_, err := db.Exec("INSERT INTO `order` (customer_id) VALUES (?)", id)
		if err != nil {
			return err
		}
	}

	return nil
}

実行結果

実行すると処理が終了しません。ログも出てないので、runSQLが1つも終了していないことが分かります。

$ go run main.go

解説

rowsのコネクションがクローズされる条件を思い出してください。最後の行まで読み込んでrows.Next()のループが終了した場合でしたね。
まず、SELECTのQuery()がコネクションを1つ確保します。並列で2つ実行しているので、INSERTが実行される前に2つ確保されます。その後、INSERTのExec()で新規接続を試みますが、利用可能なコネクションが無いので空き待ちになります。それが両方の関数で起きているので、お互い空き待ちになり、デッドロック状態になっています。
これは例えば、外部からのリクエストをgoroutineで並列処理するAPIサーバーで発生する可能性があります。

対応策

rows.Next()内でSQLを実行するのはやめましょう。関連データを取得する場合はJOINしてもよいです。

main.go

var id, sleepResult int
rows, _ := db.Query("SELECT id, SLEEP(1) FROM customer WHERE name = 'Customer1'")

defer rows.Close()
for rows.Next() {
    if err := rows.Scan(&id, &sleepResult); err != nil {
        return err
    }
}
_, err := db.Exec("INSERT INTO `order` (customer_id) VALUES (?)", id)
if err != nil {
    return err
}

実行結果

INFO runSQL connections=1 err=<nil>
INFO runSQL connections=0 err=<nil>

まとめ

golangのdatabase/sqlを使ってSQLを実行する際は、使用後にコネクションが開放されるか注意しましょう。コネクションプールが枯渇してしまい、新規SQLを実行できずアプリケーションが応答しなくなり、障害になる可能性があります。

参考文献

https://please-sleep.cou929.nu/go-sql-db-connection-pool.html
http://go-database-sql.org/
https://go.dev/doc/database/manage-connections