Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ryoshinhomie株式会社

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

Last updated at Posted at 2024-09-11

標準パッケージ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

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?