本記事はFIXERが提供する「cloud.config Tech Blog」に掲載された「開発環境DBで無限ループクエリ叩いて泣きそうになった話」を再編集したものです。
はじめに
この記事は、FIXER 2nd Advent Calendar 2020 24日目の記事です。
どうも、ブログを書くのはだいぶお久しぶりの瓦井です。
2020年を振り返りながらブログネタを考えていたら、開発環境のDBに向かって無限ループのクエリを叩いてしまい泣きそうになったことを思い出したのでバッドノウハウとして残したいと思います。
（今さっき無限ループを叩いてしまったよっていうヤバめの人は目次から 対処法 に飛んじゃってください）
事の成り行き
きっかけは開発のテストデータとしてクラウド（Azure）上のDBに WHILE文でレコードを大量生成しようとしてたんです。
これまではAzure Data Studioを使ってDBに接続し、必要なデータを閲覧したり一部レコードを書き換えるくらいのことしかしてませんでした。
SQLはあまり慣れてなかったもんで一つ一つ構文を調べながらクエリを書いて、意を決して実行！
ドキドキ…
あれ…終わらんなぁ…… もうちょい待ってみるかー。
ドキドキ…ドキドキ…
いや、これはおかしいぞ……（汗）
クエリをキャンセル！！（ポチッ）
ん？止まってない？？
Azure Data Studioを再起動したら停止できるかな？
（Azure Data Studioを再起動）
さっき叩いたクエリも消えてしまった！
やけくその再度クエリの停止！！（ポチッ）
やっぱり止まってる気配がない…
すでにDBにクエリが送信されて実行されてるからAzure Data Studioからのクエリ停止ボタンじゃ無理っぽい…？これはもしや…
恐る恐るレコード数を見てみたら…
実際のレコード
((((；ﾟДﾟ)))))))
空だったテーブルに気づけば5万件超…
当時の瓦井の頭の中
あれ、AzureのDBってデータ量で従量課金制だっけ……
このままレコード増えまくったら給料から天引きか……？
あれ、今日このまま帰れない…？！などと数十秒に一回レコード全削除のクエリを叩きながら考えてました…
【教訓】 自分一人じゃ無理だと思ったらすぐに助けを求めましょう
実際のSOS（焦りすぎてAzure Data StudioのことをSSMSって書いてる）
ヘルプを出した直後、めっちゃパニクってる自分の横で淡々と解決してくれた同期のD君には感謝感激です。。。
この時の原因が終了条件変数のカウントアップを忘れていたなんて恥ずかし過ぎて言えない
っていうことでやっと対処法↓
対処法
A. DBで無限ループを実行してるプロセスのプロセスIDを調べてKILLする
方法）まず実行中のクエリ一覧を取得。
SELECT TOP 100
der.session_id as spid
,der.blocking_session_id as blk_spid
,datediff(s, der.start_time, GETDATE()) as elapsed_sec
,DB_NAME(der.database_id) AS db_name
,des.host_name
,des.program_name
,der.status
,dest.text as command_text
,REPLACE(REPLACE(REPLACE(SUBSTRING(dest.text,
(der.statement_start_offset / 2) + 1,
((CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
END - der.statement_start_offset) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS current_running_stmt
,datediff(s, der.start_time, GETDATE()) as time_sec
,wait_resource
,wait_type
,last_wait_type
,der.wait_time as wait_time_ms
,der.open_transaction_count
,der.command
,der.percent_complete
,der.cpu_time
,(case der.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level
,der.granted_query_memory * 8 as granted_query_memory_kb
,deqp.query_plan
FROM
sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS deqp
WHERE
des.is_user_process = 1
AND datediff(s, der.start_time, GETDATE()) >= 1
AND dest.text like '%%'
ORDER BY
datediff(s, der.start_time, GETDATE()) DESC
↑で取得したプロセスID spid を kill spid で強制終了させます。
詳しくはこちらを参考
（この方法自体はDBに限らずメモリを食ってる処理を探して強制終了させたい時に結構使えますね。）
ちなみにこのときSSMS（SQL Server Management Studio）から止めようとしてくださった先輩はAzure SQL Database に対してこの操作ができなかったらしく、ツールによってもできることが微妙に違うようです。
予防策
この記事で一番伝えたいことは正味これ。
今回のケースに限らず、SQL SsrverなどT-SQLが使える環境では TRAN ROLLBACK を使いましょう。特に初心者は。これをつけると処理が完了してROLLBACKにたどり着いた時に変更を取り消して最初に戻るというやつです。
BEGIN TRAN
ここに実行したいクエリを書く
ROLLBACK
--COMMIT
上記を実行して以下のような実行時間結果（Total execution time）が表示されたら無限ループせずに終わったということなので、安心して ROLLBACK をコメントアウト、逆に COMMIT をアンコメントして実行してあげてください。
たったこれだけで無限ループの恐怖とおさらばできると思ったら絶対やるべきですよね。
（自分はこの件以来お守りのように100%書いています。もはやトラウマ）
最後に
書きながら当時のことを思い出して少し胃が痛くなりましたが、少しでも参考にしてもらえたら（ついでに少し笑ってもらえれば）幸いです。
また、T-SQLの学習やオンライン上でSQLクエリを実行できるようなサイトはたくさんあるので、これから勉強してみたい人は是非調べてみてください！
参考
・SQLServer: 現在実行中クエリのリアルタイムトラブルシューティング【Qiita】
・BEGIN TRANSACTION【Microsoft Docs】
・ROLLBACK TRANSACTION【Microsoft Docs】
瓦井 太雄／FIXER
温泉巡りや映画、スポーツ鑑賞が好きです。IFTTT や Workflow でしていたことを Power Automate でもしてみたい。
