本連載は、「Microsoft SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は「Read Committed Snapshot分離レベルを採用したときのTEMPDB関連トラブル対処方法」を解説します。
本連載では、「Microsoft SQL Server(以下、SQL Server)」で発生するトラブルについて、「なぜ起こったか」の理由とともに具体的な対処方法を紹介していきます。
「Windows Server 2012 R2」上に「SQL Server 2016 RTM」をインストールした環境を想定して解説します。
トラブルの実例:デッドロックが多発するシステムにおいて、ロック競合を起こす頻度を下げるために「Read Committed Snapshot分離レベル(*1)」を採用した(図16-1)。
そのシステムでしばらく運用していたところ、TEMPDBを配置したディスク領域で「空き領域閾(しきい)値超過」の警告が発生。やがてディスクの空き領域がゼロとなり、いくつかの処理でディスク拡張に関わるエラーが発生した。
*1:Read Committed Snapshot分離レベルでは、更新ロックが掛かっているレコードに対する読み取りはロック待ちにならない。インスタンスは、更新ロックをかけているトランザクションが開始する「前のレコード」を、後続する読み取りのクエリに見せる仕組みで実現する
このトラブルは、Read Committed Snapshot分離レベルを採用したことに由来し、バージョンストア(更新前レコード)が長時間TEMPDBに保存されていることが原因です。
バージョンストアは参照される必要がなくなれば自動的に解放され、TEMPDBの領域を確保し続けることはありません。今回のケースでは、ロック競合を回避するためにRead Committed Snapshot分離レベルを採用しましたが、それでもロック競合が多発している場合はアプリケーションの実装に問題があると想定されます。アプリケーションがロックを持ち続ける理由には以下の要因があります。
このような課題を抱えているアプリケーションでは、Read Committed Snapshot分離レベルを採用したとしてもトランザクションが終了されません。結果としてバージョンストアがTEMPDB内に蓄積され続け、容量を消費してしまいます(図16-2)。
本トラブルの根本解決策は、「バージョンストアを蓄積させないために、トランザクションを短時間で終了させる」ことです。
トランザクションは短いに越したことはありません。例えば、「ページ1」から「ページ3」まで入力させて完了となるWebアンケートの入力ページがあるとします。このときユーザーは、「ページ2」で放置してしまう可能性があります。トランザクションがオープンのままだと、ここに「ロック待ち」の要因が発生します。トランザクションは可能な限り分割することで、不要なロックを保持しない運用につながります。
Read Committed Snapshot分離レベルは、短期的にはロック待ちの軽減につながります。しかし、状況によっては解決しないロック待ちもあります。例えば「更新ロックと更新ロックの競合」の場合はRead Committed Snapshot分離レベルでは回避できないので注意しましょう。また、Read Committed Snapshot分離レベルと、SQL Serverの既定動作である「Read Committed分離レベル」では、異なる読み取り一貫性が実装されているため、業務ロジック上で問題が発生しないかの検討が必要です。
SQL Serverは、既定動作では「Read Committed分離レベル」で動作します。設定の変更によって、「Read Committed Snapshot分離レベル」を選択できます。どちらも「Read Committed」であることは変わりませんが、それぞれ何が違うのかを以下の図で示します(図16-3)。
Read Committed分離レベルとRead Committed Snapshot分離レベルは、後続のトランザクションが実行されるタイミングによって読み出すレコードが異なります。分離レベルを変更すると、アプリケーションが読み出すレコードが異なる可能性があります。「業務ロジック上での問題」とは、それが異なっても、業務上問題がないかを確認する作業のことです。
ユニアデックス株式会社所属。Microsoft MVP Data Platform(2011〜 )。OracleやSQL Serverなど商用データベースの重大障害や大型案件の設計構築、プリセールス、社内外の教育、新技術評価を行っていた。2016年4月よりIoTビジネス開発の担当となり、新しい仕事に奮闘中。ストレッチをして柔らかい身体を手に入れるのが当面の目標。
ユニアデックス株式会社所属。入社以来 SQL Serverの評価/設計/構築/教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。
Copyright © ITmedia, Inc. All Rights Reserved.