何をやったのか

ある日、ちょっとした調査のために本番データベースのデータを確認することになりました。
（個人情報が格納されているようなシステムではなかったので、必要であれば本番データベースへのアクセスが許されていました）

もしメンテナンスがあればそのタイミングでやればよかったのですが、直近では特に予定はないとのことでした。そのため、システムが動いている状態のまま作業をすることにしました。
ごく単純な SELECT を実行するだけのつもりだったので、システムに影響がないと判断したためです。

その際、万が一コピペをミスって更新系の SQL を実行してしまったら怖いので、念のためトランザクションをかけてからSQLを実行することにしました。
具体的には、psql （PostgreSQL のターミナル）で本番データベースに繋いで、以下の SQL を実行しました。

BEGIN;
SELECT * FROM user_setting WHERE xxx = 1;

結果はすぐに帰ってきました。確か2行程度だったと思います。

続けてさらに SQL を実行しようとしました。しかし、ここで同僚から「ソースコードでわからないところがあるんですが…」と声をかけられました。
こちらは急ぎの作業ではなかったので、ターミナルをそのままにして同僚の質問に回答することにしました。

そして約10分後…。

$\huge{「システムがダウンしてるー！」}$

本番障害となりました。

何が悪かったのか

**「トランザクションをかけて SELECT 文を打ったお前が悪い」**ということになりました。

何が起きていたのか

ログからシステムの動きを確認したところ、あるスレッドで user_setting テーブルをロックしようとしていたことが分かりました。具体的には、以下の SQL が発行されていました。

LOCK TABLE user_setting;

この SQL には、ロックモードの指定がありません。この場合、PostgreSQL は ACCESS EXCLUSIVE ロックが指定されたものとみなされます。
→ 明示的ロック - PostgreSQL 15.4文書

この ACCESS EXCLUSIVE ロックは最も強いテーブルロックです。
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 TABLE は極力使用しない
- やむを得ず LOCK TABLE を使用するなら、可能な限り弱いロックモードを使用する
  - その際は、LOCK TABLE との競合について周知する¹

MySQL では

MySQL だとテーブルロックのロックモードには READ と WRITE の2種類があります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.5 LOCK TABLES および UNLOCK TABLES 構文

このうち WRITE ロックは SELECT と競合します。
また、MySQL のテーブルロックはセッション単位です。ドキュメントにあるように、ROLLBACK しただけではロックが解除されないので注意が必要です。

トランザクションを (たとえば、START TRANSACTION で) 開始すると、現在のトランザクションはすべて暗黙的にコミットされ、既存のテーブルロックが解放されます。

（中略）

ROLLBACK は、テーブルロックを解放しません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.5.1 テーブルロックとトランザクションの通信

後日談

この件などでストレスが重なったことで胃痙攣をやらして、今度は私がダウンしました²。人間の体も本番障害が起きるんですね…。

みなさんも、障害発生後の体調管理には十分ご注意ください！

今回の場合、私が LOCK TABLE するコードを書いたわけではなかったので、このシステムでこのような競合が起きることを知りませんでした。 ↩
布団の上で数時間のたうち回りました。 ↩

SELECT文で本番環境を落としたお話

何をやったのか

何が悪かったのか

何が起きていたのか

どうすればよかったのか

MySQL では

後日談