開発環境DBで無限ループクエリ叩いて泣きそうになった話
2020-12-24
azblob://2022/11/11/eyecatch/2020-12-24-db-loop-000.jpg

はじめに

この記事は、FIXER 2nd Advent Calendar 2020 24日目の記事です。

どうも、ブログを書くのはだいぶお久しぶりの瓦井です。

気付いたらもうすっかり年の瀬ですね。

今年を振り返りながらブログネタを考えていたら、開発環境の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 spidkill 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クエリを実行できるようなサイトはたくさんあるので、これから勉強してみたい人は是非調べてみてください!

明日は弊社執行役員中尾さんの記事です。(激レアです)

お楽しみに!

おわり。

参考