【ITニュース解説】An Instant ADD COLUMN Waiting on One Slow SELECT Blocks Every Query Behind It

システムエンジニアを目指す皆さんにとって、データベースの操作は日常的に遭遇する場面が多い。その中でも、データベースの構造（スキーマ）を変更する「マイグレーション」は、システムの安定稼働に直結する重要な作業である。一見すると瞬時に終わるはずのデータベースの構造変更が、本番環境でシステムの応答停止を引き起こすことがある。なぜこのような現象が起こるのか、その原因と対策を解説する。

問題の核心は、データベースがデータの整合性を保つために利用する「ロック」という仕組みと、ロックの「待ち行列」にある。データベースでは、複数のユーザーやプログラムが同時にデータにアクセスする際、データの矛盾が発生しないよう「ロック」をかけて管理している。例えば、あるデータが更新されている最中に他のユーザーがそのデータを読み取ったり変更したりしないよう、一時的にアクセスを制限する。

PostgreSQLという人気のデータベースでは、テーブルの構造を変更するALTER TABLE ADD COLUMNのような操作は、通常ACCESS EXCLUSIVEという非常に強力なロックを取得する。このACCESS EXCLUSIVEロックは、そのテーブルに対する全ての種類の操作（読み取りも書き込みも）と競合する性質を持つ。一方、通常のデータ読み取りを行うSELECTクエリは、ACCESS SHAREという、より緩やかなロックを取得する。ACCESS SHAREロックは他のACCESS SHAREロックとは競合しないため、複数のSELECTクエリは同時に実行できる。

しかし、ALTER TABLE ADD COLUMNがACCESS EXCLUSIVEロックを要求する際、もしテーブルに対してすでに他のSELECTクエリがACCESS SHAREロックを保持している場合、ALTER TABLEはそのロックが解放されるまで待機しなければならない。ALTER TABLE ADD COLUMN自体はテーブルのデータを書き換える必要がないため、実行自体は非常に高速に終わる。それでも、先にSELECTクエリがロックを保持している限り、ALTER TABLEはスタートすることすらできないのだ。これが、一見「瞬時」に終わるはずの操作が待機状態になる最初の原因である。

さらに状況を悪化させるのが「ロック待ち行列」の存在だ。ALTER TABLEがACCESS EXCLUSIVEロックの取得を待ってキューの先頭に並んでいると、その後ろに到着した全ての新しいクエリも、ALTER TABLEに続いて待機列に並んでしまう。例えば、新しいSELECTクエリがテーブルにアクセスしようとすると、それはすでに実行中のSELECTクエリとは競合しないはずだが、ACCESS EXCLUSIVEロックを要求しているALTER TABLEがキューの先頭にいるため、新しいSELECTクエリはALTER TABLEがロックを取得して処理を終えるまで待たされることになる。この現象は「ソフトブロック」と呼ばれ、SELECTだけでなくINSERT、UPDATE、DELETEといった全ての操作が影響を受ける。結果として、一つの長時間実行される読み取りクエリ（例えば、時間がかかるレポート生成や分析クエリ）がテーブルのロックを保持していると、その後ろに並んだALTER TABLEがブロックされ、さらにその後ろにアプリケーションからの無数の読み書きクエリが「玉突き事故」のようにブロックされてしまい、最終的にアプリケーションのAPIが応答不能に陥り、ユーザーにダウンタイムとして影響が出るのだ。

この問題は、ローカル環境でも簡単に再現できる。まず、長時間実行されるSELECTクエリを一つのセッションで開始し、テーブルのACCESS SHAREロックを意図的に保持させる。例えばSELECT pg_sleep(300) FROM orders LIMIT 1;のようなクエリで、5分間スリープさせる。この状態で別のセッションからALTER TABLE orders ADD COLUMN note text;を実行すると、このALTER TABLEは先のSELECTクエリが保持するロックのために待機状態となる。さらに別のセッションから通常のSELECT count(*) FROM orders;を実行してみると、これもまたALTER TABLEの後ろに並んで待機してしまう。これは、pg_blocking_pids()などのデータベースのシステムビューで確認できる。ALTER TABLEが最初のSELECTに「ハードブロック」され、その後のSELECTがALTER TABLEに「ソフトブロック」されている様子がはっきりとわかる。最初のSELECTをキャンセルすると、ALTER TABLEが即座に実行され、その後すぐに、待機していたSELECTも結果を返すはずだ。この一連の動きから、マイグレーション（ALTER TABLE）自体は高速でも、ロックの取得待ちが全体のボトルネックになることが理解できる。

この問題に対する解決策はいくつかある。一つは、lock_timeout設定とリトライループを組み合わせる方法だ。 lock_timeoutは、データベース操作がロックを待つ最大時間を指定する設定である。例えば、マイグレーションスクリプトの先頭にSET lock_timeout = '2s';と記述することで、ALTER TABLEは2秒以上ロックを取得できない場合、エラーで処理を中断する。これにより、ALTER TABLEが長時間ロックキューに居座り、後続のアプリケーションクエリをブロックし続ける事態を防ぐ。ALTER TABLEがタイムアウトで失敗した場合、そのALTER TABLEはキューから離脱するため、その後続のアプリケーションクエリはALTER TABLEによるブロックを受けずに、テーブルへのアクセスを再開できる。

もちろん、マイグレーション自体は成功させる必要があるため、これをデプロイパイプラインに組み込む。具体的には、ALTER TABLE操作をシェルスクリプトなどのリトライループで囲むのだ。ALTER TABLEがlock_timeoutで失敗した場合、一定時間待ってから再度実行を試みる。このリトライ処理は、例えば失敗するたびに待ち時間を少しずつ長くする（指数バックオフ）などの工夫を凝らすことで、データベースへの負荷を抑えつつ、最終的にロックを取得してマイグレーションを成功させることができる。この方法であれば、一度のALTER TABLEの試行が後続のアプリケーションクエリをブロックする時間は最大でlock_timeoutで設定した時間（この例では2秒）に限定されるため、システム全体のダウンタイムを大幅に削減できる。

もう一つの解決策は、長時間実行される読み取りクエリ自体に制限をかけることである。特定のユーザーロール（例えば、レポート生成を行う役割）に対して、ALTER ROLE reporting SET statement_timeout = '30s';のようにstatement_timeoutを設定することで、そのロールから実行されるクエリの実行時間を最大30秒に制限できる。これにより、意図せず長時間実行されるクエリが他の重要な操作を長時間ブロックすることを防ぐ。また、idle_in_transaction_session_timeoutという設定を使えば、BEGINでトランザクションを開始したものの、コミットされずに放置されてしまったセッションを自動的にタイムアウトさせ、不要なロックの保持を防ぐことができる。これらの設定は、全てのセッションに適用するのではなく、影響範囲を限定して特定のロールに適用することが望ましい。デプロイ前には、pg_stat_activityなどのシステムビューを使って、長時間実行中のトランザクションがないかを確認することも有効な事前対策となる。

このように、データベースのマイグレーション、特にADD COLUMNのような操作は、一見安全に見えても潜在的なリスクを抱えていることがわかる。このリスクを最小限に抑え、安全に本番環境へデプロイするために、全てのマイグレーションスクリプトの先頭にSET lock_timeout = '2s';のようなタイムアウト設定を義務付けることが非常に重要である。このルールをCI/CDパイプラインや開発ガイドラインで強制することで、開発者が意図せずシステム停止を引き起こすリスクを未然に防ぎ、システムの安定稼働を確保できる。データベースのスキーマ変更が、ライブトラフィック下でどのように振る舞うかを理解することは、システムエンジニアとして非常に重要なスキルとなるだろう。