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

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

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

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

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

コネクションプールについては[こちらの記事](https://please-sleep.cou929.nu/go-sql-db-connection-pool.html "Go の sql.DB がコネクションプールを管理する仕組み")が詳しいです。

# 動作環境
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に設定しています。
```go: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()`が呼ばれコネクションがクローズされます。
```go: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に設定しています。
```go: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`構造体を返しません。
```go: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に設定しています。
```go: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してもよいです。
```go: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