「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>データアクセスのいろいろ]]

* 目次 [#n7479520]
#contents

*はじめに [#g7fe854c]
本ドキュメントでは、データベース アプリケーションを~
開発する際に知っておく必要がある、DBMS毎の
-ロック・分離戦略
-同時実行制御

を説明する。

例えば、OracleとSQL Serverのデータの
-ロック・分離戦略と、
-同時実行制御には

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

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

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

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

|原子性|Atomicity|
|一貫性|Consistency|
|分離（独立）性|Isolation|
|持続（永続）性|Durability|

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

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

次節では、
-「多バージョン法」
-「ロック法」

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

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

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

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

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

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

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

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

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

#ref(1_LockAndMVCC.png,left,nowrap,多バージョン法とロック法の動作の概要図)

**アプリケーション開発にて問題となる点 [#vd5cf469]
多バージョン法と、ロック法の最も大きな違いは、更新中のデータに対して検索を行った場合である。

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

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

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

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

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

*トランザクション分離レベル [#z75a5422]
**サポートされる分離レベル [#z48ca945]
-トランザクションの分離レベルとは、トランザクションを、他のトランザクションから分離する必要性の度合いのことである。
--分離レベルが高くなるとデータの一貫性は確保されるが、アプリケーションの同時実効性は低下する。
--分離レベルが低くなるとアプリケーションの同時実効性は向上するが、データの正確性は低下する。

-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標準のトランザクション分離レベルの対応表を示す。

|ANSI・ISO標準のトランザクション分離レベル|Oracle|SQL Server|h
|未コミット読み取り(Uncommitted Read)|未サポート|○|
|コミット読み取り(Read Committed)|○(default)|○(default)|
|繰り返し可能読み取り（Repeatable Read)|未サポート|○|
|直列可能(Serializable)|○|○|

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

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

***ダーティ・リード [#t8aa7375]
同時に実行されている、~
まだコミットされていないトランザクションが~
書き込んだデータを読み込んでしまう。 

#ref(2_DirtyRead.png,left,nowrap,DirtyRead)
.png [詳細] .png [詳細] file

***反復不可能読み取り [#tc130c6f]
トランザクションが過去に読み込んだデータをもう一度読み込もうとしたとき、~
他のトランザクションによって書き換えられ、コミットされたデータを得てしまう。

#ref(3_RepeatableRead.png,left,nowrap,RepeatableRead)

***ファントム・リード [#s3fdbf36]
トランザクションが、ある行の集合を返す検索条件で問い合わせを再実行したとき、~
別のトランザクションがその問い合わせ条件を満たす行を追加し、読み込んでしまう。 

#ref(4_PhantomRead.png,left,nowrap,PhantomRead)

***３つの問題点の対応表 [#zc99c0a3]

|ANSI・ISO標準のトランザクション分離レベル|ダーティ・リード|反復不可能読み取り|ファントム・リード|h
|未コミット読み取り(Uncommitted Read)|有|有|有|
|コミット読み取り(Read Committed)|－|有|有|
|繰り返し可能読み取り（Repeatable Read)|－|－|有|
|直列可能(Serializable)|－|－|－|

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

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

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


|ANSI/ISO標準トランザクション分離レベル|Oracle|SQL Server|h
|未コミット読み取り(Uncommitted Read)|更新中のデータは、一時領域を参照することで、ダーティーデータを読まないように制御している。|トランザクション内のSELECTステートメントで、共有ロックを使用しない。このため、ダーティーデータを参照してしまう可能性が有る。|
|コミット読み取り(Read Committed)|~|データの読み取りに、一時的に共有ロックを使用し、ダーティーデータを読まないように制御している。ただし、共有ロックを保持しないため、トランザクションが終了する前に、データが変更される可能性がある。|
|繰り返し可能読み取り（Repeatable Read)|~|トランザクション終了まで、共有ロックを保持し、繰り返し可能読み取りを実現する。ただし、キー範囲ロックが適用されないため、トランザクションが終了する前に、参照データに行を挿入される可能性がある。|
|直列可能(Serializable)|自トランザクションの開始以降に開始されたトランザクションが挿入した行に関しては、参照データに含めないように制御している。|トランザクション内のSELECTステートメントで、トランザクション終了まで共有ロック・キー範囲ロックを保持し、直列可能を実現する。|


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

２つのトランザクションが行を読み取り、それぞれ、行を更新してしまったとき、
-最初に更新したトランザクションの変更は、後から更新したトランザクションの変更によって上書きされる。
-最初に更新したトランザクションの更新は、気が付かないうちに失われてしまい、後で問題が発生する。

#ref(5_LossOfDataUpdate.png,left,nowrap,LossOfDataUpdate)

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

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

>という制御を使用する。

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

*ロック・メカニズムの詳細 [#pecf549e]
本章では、ロックの互換性と、更新ロックについて詳しく説明する。

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

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

と言うことになる。


|／|>|>|既にかけられているモード|
|／|>|>|既にかけられているモード|h
|要求されたモード|共有(S)|更新(U)|排他(X)|
|共有(S)|○|○|×|
|更新(U)|○|×|×|
|排他(X)|×|×|×|

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


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

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

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

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

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

**ロック法 [#t7f79038]
 WEBのような、DBMSトランザクションを使用できない方式で、ＵＰを実装する？
 ┃
 ┣ 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)の設定で一覧を読み取り（一覧画面表示）、更新対象データの読み取り（詳細画面表示）時に、明示的に更新ロックを掛けデータを読み取るパターンが多い。

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

**２つの方法 [#kc2d8dc3]

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

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

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

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

***設定１：READ_COMMITTED_SNAPSHOT [#g3f85ca5]
以下のようにデータベースに対して READ_COMMITTED_SNAPSHOT を ON に設定する。

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

***設定２：スナップショット分離レベル [#ocaa8477]
以下のようにデータベースに対して ALLOW_SNAPSHOT_ISOLATION を ON に設定する。

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

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

-SQL Server 2005 Tips and Tips > 第２回 排他ロックにブロックされない読み取りの実現~
http://www.microsoft.com/japan/sqlserver/2005/ssj/tips/02.mspx

*インテントロック [#e4866bc5]
*SQLServerのロック法と多バージョン法の比較 [#q9fc2610]
-ロック法が既定のモードなのでロック法の方が多い気がしますが、~
多バージョン法が使えないという情報も聞きません。
-昔、中の人にロック法と多バージョン法のどっちに自信あるの？~
と聞いた事がありますが、回答無しでした（まぁ、あたりまえですが）。

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

-ロック法と多バージョン方を比較すると、多バージョン方は、
--ブロッキングやデッドロックが発生し難くなるが、
--TEMPDB を使用し、処理のオーバヘッドも大きくなる。

>ということのようです。

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

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

>だそうです。

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

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

*その他のトピック [#x4baac76]

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

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

-参考
--インテントロックとは？ – Microsoft SQL Server Japan Support Team Blog~
https://blogs.msdn.microsoft.com/jpsql/2012/06/27/609/

*参考 [#mf1b9e3e]
**[[SQL Server でのロック・タイムアウト]] [#f6c7f906]
**[[SQL Server のロックのエスカレーション]] [#k5e5d1dd]
**[[SQL Server でのデッドロック]] [#i715456a]

----
Tags: [[:データアクセス]], [[:SQL Server]], [[:ADO.NET]]
トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS