背景
DBのスキーマを変更する際や、データを大量に更新する際はブロッキングによるタイムアウトエラー多発などの障害が発生しやすいです。
組織によっては専門のチームがスキーマの変更をすべて請け負う体制になっている場合もあると思いますが、各開発者やSREが本番環境にリリースする場合もあるかと思います。
そのようなケースで事故なく本番環境へスキーマ変更等をリリースできるように、ガイドラインを整備したいと思い考えてみました。専門知識を持った人がリリースを請け負うのがDBA的な志向だとすると、誰でも安全にリリースできるような仕組みを整えるのはDBRE的な志向だといえます。
今回は外部キー制約作成についてまとめました。
必須のknowledge
リスクと問題発生時のサービス影響
| リスク | 問題発生時のサービス影響 |
|---|---|
| 対象のテーブルに長時間ロックをかけることでブロッキングを起こしクエリタイムアウト多発につながることがある | 運用中サービス内の一部ページの読み込みがいつまでも終わらず、ユーザーにエラーが返る |
実施手順
１．外部キーを追加する側のテーブルレコード数を確認
外部キーを追加する側のテーブルレコード数が多ければ多いほど、外部キー制約を作成するクエリの実行時間が長くなります。
その間はSch-Mロックが獲得されるため、該当テーブルへのあらゆるクエリがブロックされます。
したがって、外部キーを追加する側のテーブルは新規テーブルまたはレコード数が十分に少ない（1000レコード未満目安）場合に限って、安全にリリースが可能となります。
レコード数が多い場合テーブルに外部キー制約を作成したい場合は、メンテナンス期間を設けることを検討してください。
２．外部キー制約作成クエリの実行
事前にタイムアウトを設定してクエリを実行します。
set lock_timeout 1000
ALTER TABLE [dbo].[table_1] WITH CHECK ADD FOREIGN KEY([column_1]) REFERENCES [dbo].[table_2] ([column_1])
タイムアウトした場合は何度実行しても問題ありません。クエリが成功するまで繰り返します。
所要時間
ALTER TABLE [dbo].[table_1] WITH CHECK ADD FOREIGN KEY([column_1])
REFERENCES [dbo].[table_2] ([column_1])
の実行速度は、
- REFERENCESに設定しているテーブル（=table_2）のレコード数には依存しない
- 外部キーを追加する側のテーブル（=table_1）のレコード数には依存する
- 「with check」なので、既存レコードのカラム値が参照先テーブルに存在するかのチェックが走るため。
問題が発生したとき
クエリ実行を取り消しましょう。
明示的にトランザクションを開いている場合は必ずrollbackしてトランザクションを終了させます。
実施後の確認項目
- アプリケーションのエラーログでエラーが急に多発するようになっていないか確認
- サーバーで現在実行中のクエリリストを確認し、スロークエリが多発していないかを確認
注意事項
- トランザクションを明示的に開始（begin tran）した場合は、必ずcommitまたはrollbackさせます。ロックを獲得する時間を最小限に抑えましょう。
- どうしても削除が成功しない場合はこちらの手順を参考に、成功するまでリトライするクエリを実行します。