本番環境のSSMSでトランザクションキャンセルした際にやらかした話
先日、本番障害で任意のテーブルをロックしっぱなしにし、さらに業務影響を与えるという二次障害を起こしました。
この記事は将来の自分への記事でもあります。
今回の件で学んだことをまとめ、同じミスを繰り返さないようにしたいです。
経緯
某日、本番環境にて業務が停止するレベルの障害が発生しました。
本障害は3ヵ月前に起こっていましたが、久しぶりだったこともあり少し焦っていました。
しかし、本障害は対応手順書が存在し、手順書を見れば問題を解決出来るようになっていました。
私は手順書通りに実行しました。
詳細は社外秘としますが、概ね、以下のような流れです。
- SSMSで原因解消用のSQL（
update文を含む）を実行する- このクエリは状況によってクエリが終了しない（ずっと発行中になる）
- クエリが終了しない場合、クエリ発行を中断し、Windows Serviceの業務アプリを再起動する
今回のケースはクエリが終了しない場合であったため、SSMSの画面上からクエリ発行を中断し、Windows Serviceの再起動を試みました。
ここで、問題が発生します。
手順書上では、Windows Serviceは正常に再起動し、問題が解消する手順となっていました。
しかし、Windows Serviceは再起動時のデータベースアクセスにて、タイムアウトエラーを発生し、起動に失敗しました。
この時点で顧客から鬼電が掛かりまくっており、私はパニックになってしまいました。
何が起こったか
問題の発生点はここです。
SSMSの画面上からクエリ発行を中断し、
発行クエリにはupdate文が含まれています。
経緯では触れませんでしたが、update文を含んでいるため、トランザクションの明示的な開始もしていました。
SSMSではトランザクション処理実行中にクエリ発行を中断すると、中断したタイミングで処理が止まるため、トランザクションは開始されたままになります。
実験してみましょう。
適当にデータベースを作成し、以下のクエリを実行してみます。
use [TestDatabase]
begin transaction
select
*
from
[SampleTable]
update
[SampleTable]
set
[StringValue] = 'ほげほげ'
where
[IntValue] = 100
waitfor delay '00:00:10';
select
*
from
[SampleTable]
rollback transaction
重たい処理を表現するためにwaitfor delayで処理を待ちます。
この際にSSMS画面上の■マークの「クエリ実行の取り消し」ボタンを押下します。
次にsys.dm_tran_locksテーブルを確認し、テーブルのロック状況を確認します。
[SampleTable]テーブルがロック状態にありますね。
つまり話を戻すと、update文による占有ロックが掛かったままになっていたため、Windows Serviceは再起動時にselect文すら発行出来ず、処理が落ちたということになります。
どうすべきだったか
SSMSでキャンセルした場合、エラーではないため try-catch では制御出来ません。
その他色々ググってみましたが、SQL側で制御する方法は私は見つけられませんでした。 [1]
そのため、今回のケースでは以下の対策方法が考えられるのではないかと思います。
- 手順書に
sys.dm_tran_locksテーブルを確認するクエリを実行する手順を追加する- 明示的に確認することを手順書に組み込むことで確実に防ぐ
- クエリは事前に作成しておき、第三者のレビューを通しておく
- PowerShellスクリプトなど、プログラムにてタイムアウト時間を設定して実行する
-
Invoke-SqlCmdコマンド [2]には-QueryTimeoutというプロパティがあります - スクリプトは作成者以外によるテストをしておく
-
そもそも、人の手で作業をするからミスをするのであって、理想を言えば後者を採用するべきだと思います。
ですが、今回のケースのレベルであれば前者の手順書に明記でも十分回避可能かと思います。
私にはこれくらいしか思いつきませんでした。
もし、他にもっといい方法があればコメントを頂けると幸いです。
-
2021/4/9時点 ↩︎
Discussion