SQL Serverのスナップショット分離レベル導入によるデータ基盤連携の課題解決

SQL Server Database

f:id:vasilyjp:20210427153612j:plain

こんにちは。アーキテクト部の廣瀬です。

弊社ではサービスの一部にSQL Serverを使用しており、BigQuery上のデータ基盤へテーブルを連携しています。連携の仕組みは非常によくできているものの、データ不整合や遅延が発生し得るという課題を抱えていました。しかし、SQL Serverのスナップショット分離レベルを導入することでそれらを解決できました。本記事では、抱えていた課題および解決までの流れと、スナップショット分離レベルを導入する際に気を付ける点を紹介します。

データ基盤連携の方法と課題

データ基盤との連携方法は、日次連携とリアルタイム連携の2種類です。それぞれの連携方法と抱えていた課題について説明します。

日次連携

1日1回、SQL Server専用の一括コピーツールである「bcp」を使用してテーブル全体のデータを取得する連携方法です。データ取得時のSQLのイメージは以下の通りです。

SELECT
  #{columns}
FROM
  #{@tablename} WITH(NOLOCK)

この方法では、テーブルサイズの大きさに応じてデータ取得にかかる時間も長くなります。SQL Serverにおけるデフォルトのトランザクション分離レベルは「READ COMMITTED」です。そのため、ユーザー操作によって発行される更新クエリをブロックしてしまう懸念があり、それを避けるために「WITH(NOLOCK)」を付与しています。

この「WITH(NOLOCK)」ヒントをつけるとトランザクション分離レベルが「READ UNCOMMITTED」になります。この分離レベルではダーティリードを許可するため、データの読み取り中にページ分割が起こると、データの欠損や重複などの不整合につながります。データ基盤はアプリのPUSH配信にも使われているため、重複を避けるための工夫を配信側で実装する手間や、データ欠損による機会損失が発生していました。なお、「WITH(NOLOCK)」ヒントとページ分割の関係性についてはこちらの記事で詳しく解説されています。

このように「READ COMMITTED」でも「READ UNCOMMITTED」でも、それぞれに懸念がありました。しかし、どちらかを受け入れるしかないため、ユーザー操作への悪影響を避けることを優先して「READ UNCOMMITTED」分離レベルを採用していました。

リアルタイム連携

約1分に1回、弊社で開発したリアルタイムデータ連携の仕組みを使い、直近で更新のあった差分データのみを取得する連携方法です。なお、リアルタイムデータ連携基盤に関する詳しい内容については、下記の記事をご参照ください。

techblog.zozo.com

上記記事で紹介しているデータ取得時のSQLのイメージは以下の通りです。

SELECT
  a.SYS_CHANGE_OPERATION AS changetrack_type,
  a.SYS_CHANGE_VERSION AS changetrack_ver,
  #{columns}
FROM
  CHANGETABLE(CHANGES #{@tablename},
    @前回更新したバージョン) AS a
LEFT OUTER JOIN #{@tablename} ON a.#{@primary_key} = b.#{@primary_key}

この方法では、差分データのみを取得するため、データの取得が高速に完了します。そのため、データ取得クエリが他のクエリを長時間ブロックする懸念はほぼありません。したがって、「WITH(NOLOCK)」ヒントをつけずに「READ COMMITTED」分離レベルでクエリ実行しています。

しかし、該当テーブルへ長時間の更新クエリが実行されている状況だと、逆にデータ取得クエリがブロックされてデータの同期遅延が発生することがありました。ブロックされて待ち続けた場合にロックの状況が悪化しないよう、クエリ実行時にロックのタイムアウト設定を入れたり、インターバルを60秒と長めにとるという工夫もしています。

連携方法と課題のまとめ

ここまでの説明をまとめると、以下の通りです。

  • 連携の方法は日次とリアルタイムの2種類が存在
  • 日次連携では「WITH(NOLOCK)」付きで「READ UNCOMMITTED」分離レベルでクエリを実行
    • 課題：ダーティリードを許可しているため、データの欠損や重複などの不整合が起こり得る
  • リアルタイムデータ連携では「READ COMMITTED」分離レベルでクエリを実行
    • 課題：他のクエリが更新クエリを長時間実行中だと、ブロッキングによりデータの同期遅延が起こり得る

以降では、これらの課題をどのように解決したか、順に説明します。

トランザクション分離レベルの検討

今回の課題を解決するには、「READ UNCOMMITTED」分離レベルを使用せずに、他の更新処理によって連携クエリがブロックされない状況を作る必要があります。そのためにはトランザクション分離レベルを変更する必要があります。

まず、SQL Serverのトランザクション分離レベルについて簡単に説明します。

SQL Serverには、5つのトランザクション分離レベルが用意されています。

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SNAPSHOT
  • SERIALIZABLE

デフォルトの分離レベルは「READ COMMITTED」であり、これは変更できません。 トランザクション単位での分離レベルは個別に指定可能で、未指定時はデフォルトの分離レベルとなります。 分離レベルの指定は以下のクエリで実行可能です。

SET TRANSACTION ISOLATION LEVEL <分離レベル名>

また、分離レベルではないものの、「READ COMMITTED」の挙動を変化させるデータベースオプション「READ_COMMITTED_SNAPSHOT」(READ COMMITTED SNAPSHOT ISOLATION : RCSI) も存在します。

このオプションをONにすると、データ更新時にコミット済みのレコード（トランザクション内で変更する前の状態のデータ）がtempdbへと書き込まれるようになります。そしてSELECTクエリを実行した際は、必要に応じてtempdbに格納されたコミット済みデータを読み取ることで、ロック無しで整合性のとれたデータを取得できます。

このオプションのON/OFFも考慮すると、分離レベルは以下の6つに分類できます。

  • READ UNCOMMITTED
  • READ COMMITTED（READ_COMMITTED_SNAPSHOT OFF）
  • READ COMMITTED（READ_COMMITTED_SNAPSHOT ON）
  • REPEATABLE READ
  • SNAPSHOT
  • SERIALIZABLE

この中で「READ UNCOMMITTED」分離レベルを使用せずに、他の更新処理によってSELECTクエリがブロックされない状況を作るには、

  • READ COMMITTED（READ_COMMITTED_SNAPSHOT ON）
  • SNAPSHOT

のどちらかの分離レベル（＋オプション）を設定する必要があります。

そのため、この2種類の選択肢について比較検討を実施しました。

READ COMMITTED（READ_COMMITTED_SNAPSHOT ON） vs. SNAPSHOT

どの時点のデータを読み取るか

  • READ COMMITTED（READ_COMMITTED_SNAPSHOT ON）
    • 各ステートメント（SELECT文）を発行したタイミングで、コミットされていたデータ
  • SNAPSHOT
    • トランザクションを開始したタイミングでコミットされていたデータ

同一リソースへの書き込みが競合した場合の挙動

  • READ COMMITTED（READ_COMMITTED_SNAPSHOT ON）
    • ブロッキングが発生
  • SNAPSHOT
    • トランザクションの開始後、他のクエリによって変更されたデータに対して変更をコミットしようとすると、ロールバックされエラーとなる（詳細はドキュメントを参照）

読み取りの挙動が変化する範囲

  • READ COMMITTED（READ_COMMITTED_SNAPSHOT ON）
    • オプションをONにした時点ですべてのセッションが影響を受け、コミット済みデータだけをロック無しで読み取るようになる
    • 既存アプリケーションへの影響がある（読み取り処理とデータ更新処理との競合がなくなる）
  • SNAPSHOT
    • SNAPSHOT分離レベルを指定したセッションのみが影響を受ける

比較検討の結果

今回課題を抱えているのは読み取りのみのワークロードです。したがって、書き込みの競合を考慮する必要はありません。また、何年も運用されているDBのため「READ_COMMITTED_SNAPSHOT」オプションをONにすると既存のアプリケーションの挙動に予期せぬ変化が生じる懸念もありました。一方で、SNAPSHOT分離レベルの場合は明示的に分離レベルを指定したセッションのみが影響を受けるため、既存アプリケーションの挙動は一切変化しません。

以上の考察を踏まえ、最終的にSNAPSHOT分離レベルを導入することにしました。

SNAPSHOT分離レベルの導入

SNAPSHOT分離レベルに切り替えるためには、該当セッションで以下のクエリを実行します。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

ただし、データベースオプション「ALLOW_SNAPSHOT_ISOLATION」が有効化されている必要があります。

ALTER DATABASE <データベース名> SET ALLOW_SNAPSHOT_ISOLATION ON

このオプションを運用中の本番環境に適用する際には注意点があるので紹介します。

導入時の注意点

「ALLOW_SNAPSHOT_ISOLATION」の有効化はオンラインで実施可能です。

ただし、「ALTER DATABASEを実行する前に開始されたトランザクション」が存在する限り、ALTER文の実行は完了しません。「ENABLE_VERSIONING」という待ち事象で待ち続けることになります。

なお、「ALTER DATABASEを実行した後に新たに開始されたトランザクション」についてはALTER文の実行を妨げることはありません。

ドキュメントには以下の記載があります。

ALLOW_SNAPSHOT_ISOLATION を新しい状態に (ON から OFF へ、または OFF から ON へ) 設定した場合、ALTER DATABASE は、データベース内にあるすべての既存のトランザクションがコミットされるまで、呼び出し元に制御を返しません。 データベースが既に ALTER DATABASE ステートメントで指定した状態にある場合には、制御は呼び出し元に直ちに返されます。

実際に弊社の環境で導入した際は、瞬時に完了したDBもあれば、完了まで90秒程度かかったDBもありました。

基本的にこのALTER文が他のクエリをブロックすることは無い認識ですが、万一の事態に備え、ALTER文の実行中は常にsys.dm_exec_requestsを使い、実行中のクエリでブロッキングが発生していないかを監視することをおすすめします。

導入後の注意点

導入後は、データの書き込みが発生する度にtempdbにコミット済みのレコード情報が書き込まれるようになるため、tempdbの負荷が上昇します。

この性質を念頭において、パフォーマンスモニタの以下のメトリクスで目立った変化が無いかを確認します。

  • CPUの高騰がみられないか
    • Processor¥% Processor Time
  • 同時実行性の低下はみられないか
    • SQLServer:Statistics¥Batch Requests/sec
    • SQLServer:General Statistics¥Processes blocked
  • 「行のバージョン管理」関連メトリクスで気になる変化はないか
    • SQL Server:Transactions¥Free Space in tempdb(KB)
    • SQL Server:Transactions¥Version Store Size(KB)
    • SQLServerTransactions¥Version Cleanup rate (KB/s)
    • SQL Server:Transactions¥Version Generation rate (KB/s)
  • tempdbのディスク負荷は問題ないか
    • Physical Disk (tempdbのドライブ)¥Disk Read Bytes/Sec
    • Physical Disk (tempdbのドライブ)¥Disk Write Bytes/Sec
    • Physical Disk (tempdbのドライブ)¥Current Disk Queue Length

行のバージョン管理に使用するtempdbの領域は、定期的に自動でクリーンアップされます。領域サイズが増え続けずに、定期的に減少するタイミング（行のバージョン管理のクリーンアップ）があること必ず確認します。

あわせて、sys.dm_exec_requestsを使って、リアルタイムでクエリの同時実行性についても確認しておくとより安心です。基本的には、上記内容に気を付けつつ導入および導入後の評価を実施すれば、安心してSNAPSHOT分離レベルを使用できるかと思います。

監視項目

「ALLOW_SNAPSHOT_ISOLATION」を有効化した後は、以下の2点は必ず監視しましょう。

tempdbの容量逼迫の検知

環境によっては、大量のデータ更新などの理由でtempdbの空き容量の枯渇が懸念されます。90％を超えたらアラートを発報するなど、検知できる仕組みを用意しておきましょう。

長時間開きっぱなしのトランザクションの検知

行のバージョン管理のクリーンアップについて、ドキュメントに以下の記載があります。

バージョンストアに格納されているバージョンは、行のバージョン管理に基づく分離レベルで実行されるトランザクションで必要な限り保持されます。 SQL Server データベース エンジンにより、必要なトランザクション シーケンス番号の中で最も小さい番号が追跡され、それよりもトランザクション シーケンス番号が小さい行のバージョンは定期的にすべて削除されます。

つまり、開きっぱなしのトランザクションがあると、そのトランザクションより後に開始されたトランザクションによってtempdbに書き込まれたデータはいつまでもクリーンアップされません。この状況になるとtempdbの容量逼迫につながる懸念があるため、例えば以下のようなクエリを定期的に実行してレコードが取得できた場合は通知する仕組みを用意しておきます。

-- 60分以上開きっぱなしのトランザクションを検知
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP (1) 'transaction_time_min:' + isnull(cast(datediff(minute, transaction_begin_time, getdate()) AS VARCHAR(max)), '')
  + ' / session_id:' + isnull(cast(es.session_id AS VARCHAR(max)), '')
  + ' / host_name:' + isnull(cast(host_name AS VARCHAR(max)), '')
  + ' / program_name:' + isnull(cast(program_name AS VARCHAR(max)), '')
  + ' / status:' + isnull(cast(es.STATUS AS VARCHAR(max)), '')
  + ' / last_request_end_time:' + isnull(cast(last_request_end_time AS VARCHAR(max)), '')
  + ' / text:' + isnull(cast(TEXT AS VARCHAR(max)), '') AS result
FROM sys.dm_tran_session_transactions ts
JOIN sys.dm_exec_sessions es ON es.session_id = ts.session_id
JOIN sys.dm_tran_active_transactions at ON at.transaction_Id = ts.transaction_id
LEFT JOIN sys.dm_exec_requests der ON es.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE datediff(minute, transaction_begin_time, getdate()) > 60
ORDER BY datediff(minute, transaction_begin_time, getdate()) DESC

導入効果

「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化し、データ基盤への連携クエリだけ「SNAPSHOT」分離レベルを使用することで、抱えていた以下の課題を解決できました。

  • 日次連携：WITH(NOLOCK)つきのクエリを実行することによるデータ不整合
    • ブロッキングの懸念が無くなったため、WITH(NOLOCK)を外すことができた
  • リアルタイム連携：「READ COMMITTED」分離レベルでクエリを実行する際に他のクエリにブロックされる
    • ブロックされることが無くなったため、遅延が発生しなくなりデータ基盤への連携が安定した
    • ブロックすることも無くなったため、同期のインターバルを短く設定してより早く連携できるようになった

また、既存のアプリケーションの挙動については一切変化しないため、予期せぬ不具合が発生することも避けることができました。

導入後に起きた問題

ほとんどのDBは上記内容でスムーズに導入できましたが、一部のDBでは導入後に問題が発生して切り戻しました。発生した問題点と、策定した解決方法を紹介します。

トランザクションログのバックアップサイズが急激に肥大

「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化したあとに特定のDBだけ、トランザクションログファイルのバックサイズが約100倍に肥大しました。tempdbへの書き込みが増加することは認識していましたが、ユーザーDBのトランザクションログファイルがここまで急激に肥大することは考慮できていませんでした。

ログ肥大の原因調査

「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化する前後のバックアップログファイルをテーブルにダンプして解析しました。

まず、以下のクエリでログファイルをテーブルにINSERTします。

SELECT * INTO tran_log_dump FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1
, N'C:\***\backup_log_file.trn'
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, NULL, NULL, NULL)

次に、INSERTしたテーブルをOperation（INSERT/DELETEなど）、Context（HEAP/CLUSTEREDなど）、AllocUnitId（テーブル）単位で集計し、合計のトランザクションログサイズが大きい順に表示しました。

SELECT
  *
  ,SUM(cnt) OVER () AS sum_cnt
  ,SUM(sum_log_record_length) OVER () AS sum_all_log_record_length
  ,SUM(sum_log_reserve) OVER () AS sum_all_log_reserve
FROM
(
  SELECT Operation
  ,Context
  ,AllocUnitId
  ,COUNT(*) AS cnt
  ,SUM(CAST([Log Record Length] AS BIGINT)) AS sum_log_record_length --単位：byte
  ,SUM(CAST([Log Reserve] AS BIGINT)) AS sum_log_reserve --単位：byte
  FROM tran_log_dump WITH(NOLOCK)
  GROUP BY Operation
  ,Context
  ,AllocUnitId
) AS A
ORDER BY
  sum_log_record_length DESC

image

上の図は、「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化した後のトランザクションログです。図のように、1位だけログサイズも、ログの件数も突出していました。有効化前のトランザクションログと比較すると、LOP_MODIFY_ROW（行のUPDATE）の出現回数が約2500倍になっていました。

どのテーブルへのUPDATEが大量に行われているのか確認するために、AllocUnitIdを使ってテーブル名を解決しました。

SELECT allocation_unit_id, object_name(object_id)
FROM sys.allocation_units WITH (NOLOCK)
JOIN sys.partitions WITH (NOLOCK) ON container_id = hobt_id
WHERE allocation_unit_id IN (12 * * * * 34)

その後、該当のテーブルのUPDATE回数をdm_db_index_operational_statsを使って確認しました。

SELECT *
FROM sys.dm_db_index_operational_stats(db_id('DatabaseName'), NULL, NULL, NULL)
WHERE object_id = object_id('TableName')
AND database_id = db_id('DatabaseName')

image

図のように、UPDATE回数が約6兆回と、INSERTやDELETEといった他の操作と比べても突出して大きな値となっていました。このテーブルへUPDATEしている処理はどういったものがあるのかキャッシュから確認したところ、5分に1回のペースで定期的に該当テーブルを更新しているバッチ処理がありました。この処理のクエリは、該当テーブルのほぼ全レコードに対してUPDATEを実行していましたが、大半のレコードは同じ値でUPDATEされていることも分かりました。そのため、同じ値でカラムをUPDATEしたときの挙動について、「ALLOW_SNAPSHOT_ISOLATION」オプションの有効化前後で比較を実施しました。

「ALLOW_SNAPSHOT_ISOLATION」オプションの有効化前後でのトランザクションログの比較

以下の検証用のクエリは、同一のテーブルを同じレコード数、同じ値でUPDATEした際のトランザクションログの中身を確認するクエリです。「ALLOW_SNAPSHOT_ISOLATION」オプションの無効化時と有効化時の結果をそれぞれ確認できます。

SET NOCOUNT ON
GO

DROP TABLE IF EXISTS UpdateTest
GO

CREATE TABLE UpdateTest (
  C1 INT PRIMARY KEY CLUSTERED
  ,C2 INT
  ,C3 INT
  ,C4 INT
  ,C5 INT
)
GO

-- 10000レコード、ランダムな値でINSERT
DECLARE @cnt INT = 1

BEGIN TRAN

WHILE (@cnt <= 10000)
BEGIN
  INSERT INTO UpdateTest VALUES (@cnt, RAND() * 100, RAND() * 100, RAND() * 100, RAND() * 100)
  SET @cnt += 1
END

COMMIT TRAN;
GO

-- ログバックアップによりトランザクションログを切り捨てる
CHECKPOINT
BACKUP DATABASE TEST TO DISK = N'NUL'
CHECKPOINT
BACKUP LOG TEST TO DISK = N'NUL'
GO

-- トランザクションログの中身をチェック（この時点では空っぽのはず）
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%UpdateTest%'
GO

-- 「ALLOW_SNAPSHOT_ISOLATION」オプション無効化
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

-- C1=1のカラムの存在チェック
SELECT * FROM UpdateTest WHERE C1 = 1
GO

-- 同じ値でカラムをUPDATE
UPDATE UpdateTest SET C2 = C2 WHERE C1 = 1
GO 10

-- トランザクションログの中身をチェック（ここの結果を比較したい）
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%UpdateTest%'

-- 「ALLOW_SNAPSHOT_ISOLATION」オプション有効化
ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON
GO

-- 同じ値でカラムをUPDATE
UPDATE UpdateTest SET C2 = C2 WHERE C1 = 1
GO 10

-- トランザクションログの中身をチェック（ここの結果を比較したい）
SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName LIKE '%UpdateTest%'

上記クエリの実行結果は以下のようになりました。「ALLOW_SNAPSHOT_ISOLATION」オプションの無効化時（上段）は、同じ値でUPDATEした場合はトランザクションログに書き込まれていません。一方で、有効化時（下段）は同じ値でUPDATEしてもトランザクションログに書き込みが行われるようになっていました。

image

「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化すると、各レコードにバージョン情報のタイムスタンプを保持するようになります。今回の実験における挙動の違いは、有効化時はタイムスタンプだけが更新され、その更新情報がトランザクションログに書き込まれたものと推測されます。

次に、その推測通りの挙動になっているかを確認しました。「ALLOW_SNAPSHOT_ISOLATION」オプションを有効化した状態で、同じ値でUPDATEする前後のデータページの中身を確認します。

DBCC TRACEON(3604)
DBCC PAGE (N'TEST', 1, 2875640, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)
GO

UPDATE UpdateTest SET C2 = C2 WHERE C1=1
GO 10

DBCC TRACEON(3604)
DBCC PAGE (N'TEST', 1, 2875640, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

image

推測通り、UPDATEの前後で行のバージョン情報である、「Transaction Timestamp」が更新されていることが確認できました。

したがって、ログ肥大の原因は「トランザクションログへの書き込みの挙動がオプション有効化によって変化し、同じ値で大量のレコードを更新している処理がログへ書き込まれるようになったため」と判断できます。

対応策

問題となったバッチ処理では、ほとんどのレコードは同じ値でUPDATEされているため、変化があったレコードだけを更新する差分更新に処理を修正することでログ肥大を抑えられると考えられます。リリースに向けて現在対応中です。

まとめ

本記事では、SQL Serverからデータ基盤へとデータを連携する際に抱えていた課題について説明し、スナップショット分離レベルを導入することで課題を解決するまでの流れ（分離レベルの選定、導入前後の注意点、導入後の問題点）を紹介しました。同じような課題を抱えている方の参考になれば幸いです。

最後に

ZOZOテクノロジーズでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！

tech.zozo.com

カテゴリー