1. YujiSoftware

    Posted

    YujiSoftware
Changes in title
+SELECT文で本番環境を落としたお話
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,92 @@
+[本番環境でやらかしちゃった人 Advent Calendar](https://qiita.com/advent-calendar/2019/yarakashi-production)で、このパターンのやらかしはなかったのでキーボードを叩くことにしました。
+番外編のつもりでお楽しみください。
+
+# 何をやったのか
+
+ある日、ちょっとした調査のために本番データベースのデータを確認することになりました。
+(個人情報が格納されているようなシステムではなかったので、必要であれば本番データベースへのアクセスが許されていました)
+
+もしメンテナンスがあればそのタイミングでやればよかったのですが、直近では特に予定はないとのことでした。そのため、システムが動いている状態のまま作業をすることにしました。
+ごく単純な `SELECT` を実行するだけのつもりだったので、システムに影響がないと判断したためです。
+
+その際、万が一コピペをミスって更新系の SQL を実行してしまったら怖いので、念のためトランザクションをかけてからSQLを実行することにしました。
+具体的には、psql (PostgreSQL のターミナル)で本番データベースに繋いで、以下の SQL を実行しました。
+
+```sql
+BEGIN;
+SELECT * FROM user_setting WHERE xxx = 1;
+```
+
+結果はすぐに帰ってきました。確か2行程度だったと思います。
+
+続けて次の SQL を実行しようとしました。しかし、ここで同僚から「ソースコードでわからないところがあるんですが…」と声をかけられました。
+こちらは急ぎの作業ではなかったので、ターミナルをそのままにして同僚の質問に回答することにしました。
+
+そして約10分後…。
+
+**$\huge{「本番環境がダウンしてるー!」}$**
+
+本番障害となりました。
+
+# 何が悪かったのか
+
+**「トランザクションをかけて `SELECT` 文を打ったお前が悪い」**ということになりました。
+
+# 何が起きていたのか
+
+ログからシステムの動きを確認したところ、あるスレッドで `user_setting` テーブルをロックしようとしていたことが分かりました。具体的には、以下の SQL が発行されていました。
+
+```sql
+LOCK TABLE user_setting;
+```
+
+この SQL には、ロックモードの指定がありません。この場合、PostgreSQL は `ACCESS EXCLUSIVE` ロックが指定されたものとみなされます。
+→ [明示的ロック - PostgreSQL 9.4.5文書](https://www.postgresql.jp/document/9.4/html/explicit-locking.html)
+
+**この `ACCESS EXCLUSIVE` ロックは<font color="red">最も強いテーブルロック</font>です。
+SELECT 実行時に獲得される最も弱いテーブルロックである `ACCESS SHARE` ロックとも競合します。**
+
+つまり、システムが `LOCK TABLE` 文によって `user_setting` テーブルの `ACCESS EXCLUSIVE` ロックを獲得しようとしましたが、私が先に `SELECT` 文によって `ACCESS SHARE` ロックを獲得していたことで、ロック解除待ちに入って処理が止まってしまいました。
+
+さらに、このあと別のスレッドが `user_setting` テーブルに対し `SELECT` を実行しようとしていました。しかし、`user_setting` テーブルは `ACCESS EXCLUSIVE` ロックの獲得待ちが発生しているので、この `SELECT` 文も止まってしまっていました。
+
+結果、一つのスレッドが `LOCK TABLE` で、多数のスレッドが `SELECT` で止まってしまい、データベースとのコネクションプールが枯渇。システムダウンに至りました。
+
+# どうすればよかったのか
+
+二度と惨劇を起こさないために、以下の知見を得ました。
+
+* `SELECT` しかしないなら、トランザクションをかけない
+* トランザクションを開始したなら、放置しない
+ * なるべく早く `COMMIT` か `ROLLBACK` をする
+
+また、私がシステムを設計する際に以下の点に気を付けるようになりました。[^LOCK]
+
+* ロック粒度に注意する
+* `LOCK TABLE` は極力使用しない
+ * やむを得ず `LOCK TABLE` を使用するなら、可能な限り弱いロックモードを使用する
+ * その際は、`LOCK TABLE` との競合について周知する
+
+[^LOCK]: 今回の場合、私が `LOCK TABLE` するコードを書いたわけではなかったので、このシステムでこのような競合が起きることを知りませんでした。
+
+# MySQL では
+
+MySQL だとテーブルロックのロックモードには READ と WRITE の2種類があります。
+[MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.5 LOCK TABLES および UNLOCK TABLES 構文](https://dev.mysql.com/doc/refman/5.6/ja/lock-tables.html)
+
+このうち **WRITE ロックは SELECT と競合します。**
+また、MySQL のテーブルロックはセッション単位です。ドキュメントにあるように、**ROLLBACK しただけではロックが解除されない**ので注意が必要です。
+
+> * トランザクションを (たとえば、START TRANSACTION で) 開始すると、現在のトランザクションはすべて暗黙的にコミットされ、既存のテーブルロックが解放されます。
+> * (中略)
+> * **ROLLBACK は、テーブルロックを解放しません。**
+>
+> [MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.5.1 テーブルロックとトランザクションの通信](https://dev.mysql.com/doc/refman/5.6/ja/lock-tables-and-transactions.html)
+
+# 後日談
+
+この件などでストレスが重なったことで、胃痙攣をやらして今度は私がダウンしました[^1]。人間の体も本番障害が起きるんですね…。
+
+みなさんも、障害発生後の体調管理には十分ご注意ください!
+
+[^1]: 布団の上で数時間のたうち回りました。