• 戻る（SQL Server 問題の分析方法、データアクセスのいろいろ）
  • DBMSのロック・分離戦略と同時実行制御
  • SQL Server の障害復旧
  • SQL Server でのロック・タイムアウト / SQL Server のロックのエスカレーション / SQL Server でのデッドロック
  • SQL Server 大量データ処理時の性能問題 / SQL Server アドホック クエリ問題の監視 / SQL Server 結合方式の問題を監視する

本ドキュメントでは、データベース アプリケーションを
開発する際に知っておく必要がある、DBMS 毎の

• ロック・分離戦略
• 同時実行制御

を説明する。

例えば、Oracle と SQL Server のデータの

• ロック・分離戦略と、
• 同時実行制御には

以下のような違いがある。

• Oracle では、参照すべきデータのバージョンを判断し、
  ロックを獲得しないでデータの読み取り一貫性、同時実行制御を実現している。
• SQL Server（の既定の設定）では、ロック制御によって、
  データの読み取り一貫性、同時実行制御を実現している。

このような実装の違いを把握しないまま、
Oracle と同じ感覚で SQL Server アプリケーションを開発した場合、
意図しないロック待ちが発生することになり、
パフォーマンスの劣化やデッドロックの発生などの問題に悩まされることになる。

1 つ以上の SQL 文を含む作業単位で、トランザクションが有効であるには、
ACID と呼ばれる 4 つのトランザクションの基本特性を備えている必要がある。

これを実現するための、ロック・分離戦略と、同時実行制御の仕組みは、
以下のように Oracle と SQL Server で大きな相違がある。

• Oracle では、一時領域を使用した「多バージョン法」
  （MultiVersion Concurrency Control:MVCC）を使用する。
• SQL Server（の既定の設定）では「ロック法」を使用する。

次節では、

• 「多バージョン法」
• 「ロック法」

の概要について説明する。

※ なお、本ドキュメントでは「多バージョン法」と「ロック法」の優劣については言及しない。

多バージョン法（MultiVersion Concurrency Control:MVCC）とは、
新旧の、複数のバージョンのデータを保持して
データの読み取り一貫性を保証するための仕組である。

このため、Oracle は、『マルチバージョン データベース』、
『多バージョン データベース』などと呼ばれる。

1. Oracle では、あるアプリケーションが更新トランザクションを開始すると、
   データの旧バージョンは一時領域に保持される。
2. 他のアプリケーションがそのデータに対する読み取り要求をすると、
   一時領域に保持された旧バージョンのデータを取り出す。
3. 更新トランザクションがコミットされると、一時領域の旧バージョンは消去され、
   アプリケーションはテーブル上の新バージョンのデータを見ることになる。

SQL Server（の既定の設定）では、データの読み取り一貫性の保証の仕組みとして
ロッキング・メカニズムを使用している。

1. あるアプリケーションが更新トランザクションを開始すると、
   テーブル内の更新レコードには排他ロック（X）が保持される。
2. 他のアプリケーションがそのレコードに対する読み取り要求をすると、
   未コミット済み読み取り（Read UnCommitted）以外の読み取りトランザクションでは、
   レコードを取り出すことは許可されず待たされることになる。
3. 更新トランザクションがコミットされると、排他ロック（X）が解除され、
   アプリケーションは更新されたレコードを見ることになる。

• 「多バージョン法」を採用しているデータベースには、
  Oracle、PostgreSQL、MySQL などがある。
• 「ロック法」を採用しているデータベースには
  SQL Server、DB2、HiRDB などがある。

一般的な同時実行制御方式は「ロック法」と言われていたが、
近年は、「多バージョン法」の同時実行制御に対応した DBMS も増えてきている。

補足（旧バージョンの置き場所）: 「一時領域」の実体は DBMS ごとに異なり、
それが運用上の注意点の違いに直結する。

DBMS	旧バージョンの保持先	注意点
Oracle	UNDO 表領域	長時間の参照で ORA-01555 スナップショットが古すぎます
PostgreSQL	テーブル本体（追記型）	不要行の回収に VACUUM が必要。テーブル肥大化
SQL Server（RCSI/SNAPSHOT）	tempdb のバージョン ストア	tempdb の I/O とサイズが増える

SQL Server で多バージョン法を有効にする場合、
tempdb が性能上のクリティカル パスになる点は必ず押さえておくこと
（SQL Server のファイルの配置参照）。

多バージョン法と、ロック法の最も大きな違いは、
更新中のデータに対して検索を行った場合である。

Oracle では、多バージョン法により、一時領域に保持された、
旧バージョンのデータを読み込む。

つまり、Oracle では、読み取るデータが更新中であっても
検索処理が待たされることはない。

これに対し、SQL Server では、ロック法による同時実行制御のため、
検索時に共有ロックをかける。
読み取るデータが更新中の場合は、すでにそのデータに排他ロックがかかっているため、
共有ロックをかけることができない。

つまり、SQL Server では、更新中のデータに読み手はアクセスできず、
検索処理が待たされる。

このような、実装の特徴を理解していないと、アプリケーション開発者は、
パフォーマンス劣化やデッドロックなどのトラブルに見舞われることになる。

• トランザクションの分離レベルとは、トランザクションを、
  他のトランザクションから分離する必要性の度合いのことである。

  • 分離レベルが高くなるとデータの一貫性は確保されるが、
    アプリケーションの同時実行性は低下する。
  • 分離レベルが低くなるとアプリケーションの同時実行性は向上するが、
    データの正確性は低下する。

• Oracle は、

  • コミット読み取り(Read Committed)
  • 直列可能(Serializable)

  の 2 つをサポートしている。

• それに対し、SQL Server は、
  ANSI/ISO 標準のトランザクション分離レベルをすべてサポートしている。

  • 未コミット読み取り(Uncommitted Read)
  • コミット読み取り(Read Committed)
  • 繰り返し可能読み取り（Repeatable Read)
  • 直列可能(Serializable)

• Oracle も SQL Server もデフォルトの
  トランザクション分離レベルはコミット読み取り(Read Committed) である。

以下に、Oracle と SQL Server がサポートする
ANSI/ISO 標準のトランザクション分離レベルの対応表を示す。

移行メモ（体裁）: 元ページの SQL Server がサポートする分離レベルの列挙に
「繰り返し可能読み取り（Repeatable Read)|未サポート」という
表の断片が紛れ込んでいた（直後の対応表の一部と思われる）。
SQL Server は Repeatable Read をサポートしているため、削除した。

補足（SQL Server には 5 つ目がある）: 上の 4 つに加え、
SQL Server には SNAPSHOT 分離レベル（SQL Server 2005 以降）がある。
ANSI/ISO 標準にはない独自のレベルで、本ページ後半で扱う。

ANSI/ISO SQL 規格は、同時に実行されるトランザクションの分離レベルと
同時実行における 3 つの問題点を定義している。

次に、この 3 つの問題点について説明する
（同時実行における最も基本的な問題点である『更新データの消失』は、次節で説明する）。

同時に実行されている、
まだコミットされていないトランザクションが
書き込んだデータを読み込んでしまう。

トランザクションが過去に読み込んだデータをもう一度読み込もうとしたとき、
他のトランザクションによって書き換えられ、コミットされたデータを得てしまう。

トランザクションが、ある行の集合を返す検索条件で問い合わせを再実行したとき、
別のトランザクションがその問い合わせ条件を満たす行を追加し、読み込んでしまう。

本節では、Oracle と SQL Server の分離レベルの実現方式について説明する。

• Oracle では、参照するデータのバージョンを選択することにより、
  コミット済み読み取り(Read Committed)・直列化(Serializable) の分離レベルを実現する。

• SQL Server では、分離レベルの設定により、
  データ参照時に適用されるロックの期間、ロックの種類が変更される。

補足（NOLOCK は「ロックしない」だけではない）: 未コミット読み取り
（WITH (NOLOCK) / SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED）は
「多少古いデータが読めても構わない」程度に理解されがちだが、実際には
存在しないデータを読む／存在するデータを読み落とすことがある。

• ページ分割が起きている最中にスキャンすると、
  同じ行を 2 回読む、または行を 1 件も読まない
• 集計値が実在しない値になる
• 稀に エラー 601（データの移動によりスキャンを続行できない）で失敗する

「参照が重いから NOLOCK」という対処は、
後述の RCSI に置き換えるべきである。

次に、『更新データの消失』の対処法と、分離レベルとの関係を説明する。

2 つのトランザクションが行を読み取り、それぞれ、行を更新してしまったとき、

• 最初に更新したトランザクションの変更は、
  後から更新したトランザクションの変更によって上書きされる。
• 最初に更新したトランザクションの更新は、気が付かないうちに失われてしまい、
  後で問題が発生する。

『更新データの消失』は、以下の方法で回避できる。

• ロックを使用しない Oracle データベースでは、

  • 更新対象のデータには、明示的に更新ロックをホールドで掛ける
    （SELECT ... FOR UPDATE）。
  • トランザクションの分離レベルに、直列化(Serializable) を指定した場合、
    『レコードに対する変更が、自トランザクションの開始時にすでにコミットされていた
    他トランザクションによるものであると決定できる場合のみ、
    自トランザクションのコミットを許可する。』

  という制御を使用する。

• SQL Server では、

  • 繰り返し可能読み取り(Repeatable Read) 以上の分離レベルで、
    対象データに共有ロックをホールドで掛ける。
  • コミット済み読み取り(Read Committed) の分離レベルで、
    明示的に更新ロックをホールドで掛ける（WITH (UPDLOCK)）。

本章では、ロックの互換性と、更新ロックについて詳しく説明する。

基本的なロックの互換性については以下の表に示す通りである。
Oracle に関しては、参照時にロックを掛けることが無いため、

• 「共有ロックが存在しない」
  • ⇒ 「共有ロックと排他ロックの違いが無い」
    • ⇒ 「排他ロックのみ存在する」

と言うことになる。

※　SQL Server には、インテントロック等、特殊なロックも存在するが、
ユーザが制御するものでは無いため、ここでは割愛する。

Oracle と SQL Server の更新ロックは、双方『更新データの消失』を防ぐ役割で使用される。

SQL Server の場合、『更新データの消失』の防止は、共有ロックでも事足りる。
このため、SQL Server の更新ロックは『デッドロック』防止のためと言われる。

補足: 更新ロックがデッドロックを防ぐ仕組みの詳細は、
SQL Server でのデッドロックの補足を参照。

前述の、「多バージョン法」・「ロック法」の、
トランザクション設計 ～ 実装方式の決定フローチャートを以下に示す。
※「多バージョン法」の方がディシジョン（判断）が浅く、設計しやすいと言える。

WEBのような、DBMSトランザクションを使用できない方式で、UPを実装する？
  ┃
  ┣ YES ⇒ タイムスタンプを使用せざるを得ない（楽観方式）。
  ┃
  ┗ NO ⇒ DBMSトランザクションを使用することができる。
            ┃
            ┗ ⇒ 排他方式に、楽観方式を採用する？
                  ┃
                  ┣ YES ⇒ 分離レベルを直列化（Serializable）を設定し、トランザクションのコミット時に競合エラーを検出する（楽観方式）。
                  ┃
                  ┗ NO ⇒ 更新前提の読み取りに、明示的に更新ロックを使用することで、事前に競合検出する（悲観方式）。

※　更新ロックの競合時、待機するか、待機しないかを設定することができる（NOWAIT オプション）。

WEBのような、DBMSトランザクションを使用できない方式で、UPを実装する？
┃
┣ YES ⇒ タイムスタンプを使用せざるを得ない。
┗ NO ⇒ DBMSトランザクションを使用することができる。
        ┃
        ┗ ⇒ DBMSトランザクションを使用できるがDBに負荷をかけたくない？
              ┃
              ┣ YES ⇒ コミット済み読み取り（Read Committed）に設定し、タイムスタンプを使用し、UP側で制御する（楽観方式）。
              ┗ NO ⇒ 特に問題が無い場合は、DBMSトランザクションを使用する。
                        ┃
                        ┗ ⇒ 排他方式に、楽観方式を採用する？
                              ┃
                              ┣ YES ⇒ コミット済み読み取り（Read Committed）で設定し、
                              ┃        タイムスタンプを使用し、UP側で同時実行制御をする（楽観方式）。
                              ┃
                              ┗ NO ⇒ 悲観方式を、簡単に実装する？
                                        ┃
                                        ┣ YES ⇒ 繰り返し可能読み取り（Repeatable Read）以上の分離レベルを設定することで、
                                        ┃        参照処理の結果セットに共有ロックのホールドロックを掛け、事前に競合検出する。
                                        ┗ NO ⇒ コミット済み読み取り（Read Committed）以上の分離レベルを設定し、
                                                  更新前提の読み取りに、明示的に更新ロックを使用することで、事前に競合検出する。

SQL Server では、繰り返し可能読み取り(Repeatable Read) 以上の分離レベルで、
対象データに共有ロックをホールドで掛けるため
『更新データの消失』を防ぐことができるだけの排他制御を実現できる。
更新ロックには、このとき問題として発生するデッド・ロックを防ぐために
使用するという観点があるが、システムのユーザプログラムを開発する場合には、
同時実行性を高めるために、分離レベルに、コミット済み読み取り(Read Committed) の
設定で一覧を読み取り（一覧画面表示）、更新対象データの読み取り（詳細画面表示）時に、
明示的に更新ロックを掛けデータを読み取るパターンが多い。

補足（Web アプリでの現実解）: フローチャートの最初の分岐にあるとおり、
Web のようなステートレスな方式では画面をまたいで DB トランザクションを
保持できないため、悲観ロックは事実上使えない。
このため、実務では楽観同時実行制御が主流になる。

SQL Server では rowversion（timestamp）型が定石。

ALTER TABLE dbo.Orders ADD RowVer rowversion NOT NULL;

-- 更新時：読み取り時点の RowVer を条件に含める
UPDATE dbo.Orders
   SET Amount = @amount
 WHERE OrderId = @id
   AND RowVer  = @rowVerAtRead;

IF @@ROWCOUNT = 0
    THROW 50001, N'他のユーザによって更新されています。', 1;

Entity Framework では、この列に [Timestamp] 属性を
付けるだけで同じ制御が行われ、競合時は
DbUpdateConcurrencyException が発生する。

SQL Server 2005 からは、多バージョン法を使用した、
同時実行制御（MVCC）がサポートされるようになっている。
多バージョン法を使用した、同時実行制御（MVCC）を使用するには、次の 2 つの方法がある。

READ_COMMITTED_SNAPSHOT は、Oracle でのデフォルトの動作とほぼ同じであり、
ステートメント発行時点での正しいデータを参照できることを保証する。

スナップショット分離レベルは、Oracle の Serializable の分離レベルを
選択した場合とほぼ同じ動作であり、
トランザクション発行時点での正しいデータを参照できることを保証する。

上記の多バージョン法を使用した、同時実行制御（MVCC）はデフォルトでは利用できない。

利用の際は、DBMS へ下記の設定が必要になる。

以下のようにデータベースに対して READ_COMMITTED_SNAPSHOT を ON に設定する。

ALTER DATABASE データベース名
SET READ_COMMITTED_SNAPSHOT ON

以下のようにデータベースに対して ALLOW_SNAPSHOT_ISOLATION を ON に設定する。

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

詳しくは、以下のドキュメントを参照のこと。

• SQL Server 2005 Tips and Tips > 第2回 排他ロックにブロックされない読み取りの実現

補足（RCSI と SNAPSHOT の違い）: 名前が似ているが性質が異なる。

	RCSI	SNAPSHOT
有効化	SET READ_COMMITTED_SNAPSHOT ON	SET ALLOW_SNAPSHOT_ISOLATION ON
アプリ側の変更	不要（既定の Read Committed の挙動が変わる）	必要（SET TRANSACTION ISOLATION LEVEL SNAPSHOT）
一貫性の単位	ステートメント開始時点	トランザクション開始時点
更新競合	発生しない（最新をロック待ち）	エラー 3960 が発生しうる
有効化の条件	DB への排他アクセスが必要（全接続の切断）	通常は不要

RCSI の有効化には DB への排他アクセスが必要なため、
稼働中のシステムに後から入れる場合は停止時間の計画が要る
（ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE を併用）。

• ロック法が既定のモードなのでロック法の方が多い気がしますが、
  多バージョン法が使えないという情報も聞きません。
• 昔、中の人にロック法と多バージョン法のどっちに自信あるの？
  と聞いた事がありますが、回答無しでした（まぁ、あたりまえですが）。

調べてみましたが、以下のトレード・オフを考慮して使い分ければイイだけのようです。

• ロック法と多バージョン法を比較すると、多バージョン法は、

  • ブロッキングやデッドロックが発生し難くなるが、
  • tempdb を使用し、処理のオーバヘッドも大きくなる。

  ということのようです。

• ちなみに、デフォルトは、

  • SQL Server では READ_COMMITTED_SNAPSHOT がデフォルトで OFF
  • SQL Azure では、READ_COMMITTED_SNAPSHOT がデフォルトで ON

  だそうです。

補足（最新化：現在の推奨）: 「Azure SQL Database では既定で ON」という
事実は、Microsoft 自身が RCSI を既定として妥当と判断していることを意味する。
現在は、オンプレミスの新規構築でもRCSI を有効にするのが第一候補
という位置付けになっている。理由は以下。

• 参照処理が共有ロックを取らなくなり、
  「参照 × 更新」のブロッキングとデッドロックが激減する
• WITH (NOLOCK) を使う動機がなくなる（前述のとおり NOLOCK は危険）
• アプリケーションの修正が不要

一方でコストも正しく理解しておく。

コスト	内容
tempdb	バージョン ストアの I/O と容量。長時間トランザクションで肥大化する
行のサイズ	各行に 14 バイトのバージョン情報が付加される（初回更新時に増加）
更新競合	RCSI では発生しないが、ロストアップデートは依然として起こりうる（楽観制御は別途必要）

特に「RCSI にしたから更新の同時実行制御が不要になる」という誤解は禁物。
RCSI が解決するのは参照のブロッキングであって、更新の競合ではない。

あまり情報がありませんでしたが、以下の情報が非常に参考になりました。

• SQL Server の読み取り一貫性とロック｜システム開発のブログ｜株式会社ベルソフト
  https://bellsoft.jp/blog/system/detail_586

• トランザクション ロックと行のバージョン管理ガイド | Microsoft Learn
  https://learn.microsoft.com/ja-jp/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

より粒度の低いロックリソースにロックを保持していることを示すためのロック。
内部仕様（内部システム）で使用されるため、外部仕様として意識する必要は無い。

• ロックマネージャは、単に一つのロックリソースに対してロックを獲得し
  互換性で制御するだけなので、コレだけでは、ロックの互換性から、
  テーブルに共有ロックをしていても、行に排他ロックをかけて
  更新されてしまうことがある。

• インテントロックはコレを防ぐため、更新時、行に排他ロックをかける前に、
  テーブルにインテント排他ロックをかけようとする。
  この場合、テーブルに共有ロックがかかっていれば、
  行に排他ロックの前の、テーブルにインテント排他ロックをかける段階で失敗する。

• 参考

  • インテントロックとは？ – Microsoft SQL Server Japan Support Team Blog
    https://learn.microsoft.com/ja-jp/archive/blogs/jpsql/

移行メモ（誤字）: 元ページの「排他ロックを書けて」は
「排他ロックをかけて」の誤記。

補足（意識する場面はある）: 「外部仕様として意識する必要は無い」とあるが、
障害解析時には必ず目にする。
sys.dm_tran_locks の request_mode に現れる
IS / IX / SIX がインテント ロックで、
resource_type = 'OBJECT' の行に付く。
テーブル ロック（S / X）が見えたら
ロック エスカレーションを疑う、という読み方をする
（SQL Server のロックのエスカレーション）。

• SQL Server でのロック・タイムアウト
• SQL Server のロックのエスカレーション
• SQL Server でのデッドロック

Tags: 移行, データアクセス, SQL Server, ADO.NET