先日 SQL Server のカーソルの動作を把握する という投稿しましたが、sys.databases をカーソルで参照する際には、Snapshot ベース音カーソルを使用したほうが動作が安定するかと思います。
想定しない動作となった場合の挙動
PaaS の SQL Server ベースの環境では基盤で自動的にバックアップが取得されています。
- SQL Database: バックアップ頻度
- Azure SQL Managed Instance: バックアップ頻度
- Azure Arc Enabled SQL Managed Instance: 自動バックアップ
SQL Server を使用している場合も、「管理対象のバックアップ (マネージドバックアップ)」 という機能を使用することで、自動的なバックアップを取得することができます。(メンテナンスプランを使用することで対応することも可能です)
バックアップの頻度が一番多いのはトランザクションログのバックアップとなり、頻繁に更新が行われている環境では 5 ～ 10 分程度の頻度でバックアップの取得が行われているのではないでしょうか。
このようなトランザクションログのバックアップと sys.databases をカーソルで参照するクエリがバッティングすると、想定しない動作になるケースがあります。
カーソルを使用するクエリとして、次のようなクエリを想定してみます。
SET NOCOUNT ON BACKUP LOG DB04 TO DISK=N'NUL' BACKUP LOG DB04 TO DISK=N'NUL' DECLARE @db_name sysname, @log_reuse_wait_desc sysname SELECT TOP 5 name, log_reuse_wait_desc FROM sys.databases WHERE name like 'DB%' ORDER BY name ASC DECLARE cur_databases CURSOR FOR SELECT TOP 5 name, log_reuse_wait_desc FROM sys.databases WHERE name like 'DB%' ORDER BY name ASC OPEN cur_databases SELECT name,properties FROM sys.dm_exec_cursors(@@spid) FETCH NEXT FROM cur_databases INTO @db_name, @log_reuse_wait_desc WHILE @@FETCH_STATUS = 0 BEGIN WAITFOR DELAY '00:00:05' FETCH NEXT FROM cur_databases INTO @db_name, @log_reuse_wait_desc PRINT @db_name + ':' + CAST(@@FETCH_STATUS AS varchar(10)) END CLOSE cur_databases DEALLOCATE cur_databases SELECT TOP 5 name, log_reuse_wait_desc FROM sys.databases WHERE name like 'DB%' ORDER BY name ASC
DB01～DB05 までの 5 つのデータベースについて処理が行われ、PRINT については次のように出力が行われ、最後の「@@FETCH_STATUS」が「-1」になるのが想定される動作となります。
しかし、上記のクエリを実行している最中に別のセッションで次のクエリを実行してみます。
DROP TABLE IF EXISTS DB04.dbo.T1 SELECT * INTO DB04.dbo.T1 FROM DB01.dbo.T1 GO 3
これにより、DB04 については、トランザクションログの書き込みが行われた状態となります。
そうすると先ほどのクエリの実行結果が変わり、次のように DB04 を処理対象としたタイミングで 「@@FETCH_STATUS」が「-2」となり、想定しない動作となります。
カーソルのオプションによっては、sys.databases を使用したクエリでこのような実行結果となり、想定したデータベース分の処理が行われれず、途中で処理が終了するというようなことが発生します。
想定しない動作が発生する理由
先ほどのカーソルを使用したクエリでは次のようなステートメントで実行しています。
DECLARE cur_databases CURSOR FOR SELECT TOP 5 name, log_reuse_wait_desc FROM sys.databases WHERE name like 'DB%' ORDER BY name ASC
カーソルにはオプションを使用していないのですが、この場合、カーソルのプロパティは次のように Keyset を使用したオプションとなります。
このようなオプションでカーソルを使用している場合、
- カーソルで sys.databases を参照するクエリ
- トランザクションログのバックアップ
がバッティングした場合には、上記のように処理の途中で「@@FETCH_STATUS = -2」となり、ループが途中で終了するという事象が発生します。
このような動作になる原因について SR で確認をしたところ、トランザクションログのバックアップが取得されたタイミングで、sys.databases の項目の log_reuse_wait / log_reuse_wait_desc の値が、カーソルで処理を実行している途中で変更されていることに起因しているとのことでした。
今回は、カーソルを開いたタイミングと処理の最中で、log_reuse_wait の情報が変更されるようなクエリ構成としています。
最初はバックアップを取得しているため、log_reuse_wait_desc は「NOTHING」の状態となっています。
カーソルのループを繰り返している最中に、DB04 に対して変更が行われることで、この項目は「LOG_BACKUP」へと変化します。
これにより、Keyset でカーソルを開いている場合はデータ変更が検知され「@@FETCH_STATUS=-2」となっているとのことでした。
sys.databases は、複数の内部情報を組み合わせて表示しているものとなるため、データ構造的にこのような挙動となるのかもしれませんね。
実際のケースでは「NOTHING → LOG_BACKUP」の変化ではなく、カーソルのループの最中にトランザクションログのバックアップが取得され「LOG_BACKUP → NOTHING」への変化が検知される機会が多いかと思いますが、この変化の場合も同様の挙動となります。
ログバックアップと sys.databases を使用したカーソルがバッティングしなければ、上述のクエリでも動作は安定するのですが、バッティングした場合はカーソルが途中で終了し予期せぬ動作となる可能性があります。
PaaS の SQL Server ベースの環境ではトランザクションログのバックアップは 5～10 分起きに実施されるため、実行頻度の多い処理となっています。
そのため、sys.databases を使用したカーソル内で処理に時間のかかるステートメントが実行されている場合にはバッティングする可能性が高くなるのではないでしょうか。
(SQL Server で短い頻度でトランザクションログのバックアップを取得している場合も同様です)
想定しない動作を回避するためには
このような想定しない動作 (処理の途中で「@@FETCH_STATUS=-2」となる) を回避するためには、Snapshot のカーソルを使用することを検討します。
先ほどのカーソルであれば、「STATIC」カーソルに変更します。
(厳密には Snapshot のデータ取得方式のカーソルとなっていれば STATIC 以外でも問題ありません)
DECLARE cur_databases CURSOR STATIC FOR SELECT TOP 5 name, log_reuse_wait_desc FROM sys.databases WHERE name like 'DB%' ORDER BY name ASC
STATIC カーソルを使用することで、Snapshot のカーソルが使用されるため、カーソルのループ内でデータの変更が行われても FETCH するタイミングでは検知されなくなります。
これにより、動作が安定しループの途中で処理が終了するということはなくなるかと思います。
通常、Keyset のカーソルを使用していても、キー項目に対しての変更が発生していなければ、データ変更によるループの実行回数の影響は受けないはずなのですが、sys.databases のような内部的に複数の項目を組み合わせているシステムテーブルではこのような挙動が発生することがあるようです。
システムテーブル / DMV を使用していて「@@FETCH_STATUS=-2」になって、カーソルのループの途中で処理が終了してしまう場合には、カーソルオプションを見直し、Snapshot ベースのカーソルを使用して、内部的なデータ変更の影響を受けないようにしてみると、動作が安定するかもしれません。