QA@IT

SQLServerのスナップショット分離レベルとは

8173 PV

SQLServerのスナップショット分離レベルとは、
・すべてのSELECTにFOR UPDATEを付けたRead Committed分離レベル
で良いでしょうか?
 
私は、
・行バージョン有り
・スナップショット分離可能
としたSQLServer 2014、
Tomcat 8、Java 7、Microsoft JDBC Driver 4.1 for SQL Server、
Windows Server 2012R2(Windows Updateは最後まで)
のシステムにて、
 
A:有る処理
 有るSELECT(多い)
 有るUPDATE(多い)
をRead Committed分離レベルでしていましたが、
 
今度、
A:有る処理
 有るSELECT(多い)
 有るUPDATE(多い)
B:別の処理
 別のSELECT(少ない)
 別のUPDATE(少ない)
もしBでUPDATE件数が0で無い場合
 A':有る処理
  有るSELECT(同じに多い)
  有るUPDATE(同じに多い)
をする事になり、全体をスナップショット分離レベルにして
見ました。
 
すると、
・Bが0件でA'をしない場合、Aは遅くなった。
・Bが0件で無く、Aと同じ処理をするA'は遅くならない。
 (Aの2倍の遅さにならない)
となりました。
 
スナップショット分離レベルとは、すべてのSELECTにFOR UPDATEが
付くのと同等だとすると、
・Aではスナップショットをtempdbに書き込む分、遅くなった。
・A'では書き込み済みの為、遅くならない。
という風に説明が付きます。
 
いかがでしょうか?

  • スナップショット分離レベルってのは、基本的にはロックをかけないでトランザクションを実行するためのものです。なので違います
    ロックをかけないでselectするので、ロックしてほしいときに指定するのが(ORACLEで言う)for update です
    -

回答

クライアントやミドル層を除いて、あくまでサーバの動作だけで見た場合

読み取り一貫性(行バージョン)が無かった。

行バージョンはスナップショットを実現するための内部機構です
昔のSQL Serverにはスナップショットはなかったので、まあ正しいと言ってもいいと思います

トランザクション単位で共有ロックをかける

トランザクション単位でという意味は不明ですが、違います
スナップショットでのselectはデフォルトではロックをかけません
READ_COMMITTED_SNAPSHOTでもスナップショット分離レベルでも同様です
ORACLEでのFOR UPDATEなしのselectに相当です

読み取り一貫性(行バージョン)はトランザクション単位で、挿入・更新・削除も有り

読み取り一貫性は、更新、削除を保証しません
READ_COMMITTED_SNAPSHOTはトランザクション単位の読み取り一貫性は保障しません

読み取り一貫性(行バージョン)が行単位の(本当のOracleと同じ)分離レベルは無い。

行単位の意味がわかりません
一貫性とは複数の間で整合性が取れている事ですよ
テーブルに対して全ての行に一貫性があるとか
トランザクションに対して全ての文で一貫性があるとか

本当のORACLEとまったく同じ動作を求めるなら、ORACLEを使うしかありませんが
すくなくともREAD_COMMITTED_SNAPSHOTでの動作はORACLEのデフォルトと極めて近い動作をするはずですが
あくまでミドル層やクライアントによる動作の影響は考慮しない範囲で、ですが

分離レベルは他のトランザクションとの同時実行性を考慮して決めるものです
スナップショット分離レベル一択と言った単純な話ではありません

他のトランザクションを待たせて良いのなら、SERIALIZABLEで実行すれば良いだけの話です

個人的には更新のあるトランザクションでスナップショット分離レベルは通常ありません(selectしたデータの更新が保証されないので)
反復可能読み取りが必要で、どうしてもREPEATABLE READで実行できないときには選択肢の一つとしてはありますが

編集 履歴 (1)
  • ご教示ありがとうございました。やっと得心が行きました。 -

※ここの作法に叶っていないかも知れませんが、コメント欄では限界なので、
 質問の仕切り直しをさせて下さい。
 
質問:
私は、
・行バージョン有り
・スナップショット分離可能
としたSQLServer 2014、
Tomcat 8、Java 7、Microsoft JDBC Driver 4.1 for SQL Server、
Windows Server 2012R2(Windows Updateは最後まで)
のシステムにて
 
A:有る1かたまりのテーブルの読み書き →B:別の読み書き →A’Aをもう一度読み書き
(Aという処理の結果(テーブル更新)はBに影響し、Bという処理の結果(テーブル更新)は
 A’に影響する。)
 
で、折角の行バージョンを生かした、ロックのエスカレーションの無い、
多段階(Aというクラスを実行、Bいうクラスを実行、Aというクラスを実行)の
SQL読み書きをしたいが、
以下の制約から、SQLServer2014ではスナップショット分離一択である。
 
で良いでしょうか?

制約:
・そもそもSQLServerのSELECT文はすべてトランザクション単位で共有ロックを
 かけていた。
 そして残念な事に、読み取り一貫性(行バージョン)が無かった。
 
・READ_COMMITTED_SNAPSHOT ONをしたDBのSELECT文も、トランザクション単位で
 共有ロックをかけるが、それに加え行単位の読み取り一貫性(行バージョン)が増えた。
 (これがOracleのFOR UPDATE付きSELECT相当?)
 
・ALLOW_SNAPSHOT_ISOLATION ONをしたDBで、
 (処理の先頭で予期せぬトランザクションを除く為、ロールバックをした後)
 com.microsoft.sqlserver.jdbc.SQLServerConnection.TRANSACTION_SNAPSHOT
 にしたDBは、
 共有ロックをかけないし、読み取り一貫性(行バージョン)も有る上に、
 (ここまでFOR UPDATE相当なしのOracle SELECT文?)
 読み取り一貫性(行バージョン)はトランザクション単位で、挿入・更新・削除
 も有り。
 
・「共有ロックをかけないし、読み取り一貫性(行バージョン)も有る上」
 が、「読み取り一貫性(行バージョン)が行単位」の(本当のOracleと同じ)
 分離レベルは無い。

編集 履歴 (0)
  • なんかスナップショット分離常用で良いような気がしてきたりして。 -
  • あなたの言う「読み取り一貫性」とは何を指していますか? -
  • UNDO表領域を使って、未コミットのデータをかくし、過去のコミット済みのデータを見せる機能です。 -

どういう問題を解決しようとしているのかわかりませんが、
何の前提も無く比較した場合共有ロックとスナップショット分離は別の物です。

例えばスナップショット分離の 2つのトランザクション TRAN A と TRAN Bで、
※ 環境は最後に。

-- use readcommited_snapshot_db
-- TRAN A

begin
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

begin tran
  select * from sample1;
  update sample1 set f1 = f1 + 10 where id = 1
  waitfor delay '00:00:10' --ウェイト
commit

end
-- use readcommited_snapshot_db
-- TRAN B

begin 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

begin tran
  select * from sample1;
  update sample1 set f1 = f1 + 1 where id = 1
commit

end

ウェイト中に開始された TRAN Bは、select文は成功し、update文が競合エラーとなります。

メッセージ 3960、レベル 16、状態 2、行 9
更新の競合により、スナップショット分離トランザクションが中断しました。スナップショット分離を使用してデータベース 'readcommited_snapshot_db' のテーブル 'dbo.sample1' に直接または間接的にアクセスし、別のトランザクションによって変更または削除された行を更新、削除、または挿入することはできません。トランザクションを再試行するか、更新/削除ステートメントの分離レベルを変更してください。

なお、update文を削除すれば成功しますが selectではTRAN Aでの更新前の値が取得されます。

SQL Serverでは デフォルトの状態のデータベースは READ_COMMITEDトランザクションなので、
スナップショットを有効にしていない場合で、SET TRANSACTION ISOLATION LEVEL~を消したSQLを試せば
TRAN Aのウエイト中に開始したTRAN Bのselect文(updateではありません)で待ち状態になり、TRAN Aのコミット後にTRAN Bはupdateも含め成功します。

ところで、SQL Serverで Select ~ For Updateって トランザクションのロックが勝つ(というか優先されるというか)ので、効果が感じられなかった気がするんですが( WITH(UPDLOCK) を代わりに使うような気が )、今は違うんでしょうか?


version: SQL Server 2008 Standard
: SQL Server 2008 付属の SQL Server Management Studio

DB設定 (データベース名 readcommited_snapshot_db)

ALTER DATABASE readcommited_snapshot_db
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE readcommited_snapshot_db
SET READ_COMMITTED_SNAPSHOT ON

テーブル sample1

CREATE TABLE sample1(
    id int primary key,
    f1 int,
    f2 int,
);
編集 履歴 (2)
  • 効果もなにも、そもそもSQL Serverのselectに For Update なんて構文はなかったと記憶してますが -
  • カーソル使う時には使えますね。 -
  • あれはカーソルを書き込み可能にするもので、構文的にはDECLARE文の一部です
    カーソルでのロックがどうなるかは承知してないので、select時(厳密にはカーソルオープン時でしょうね)でロックされていない可能性はあると思いますが
    -
  • jdbcでresultsetを使っていればカーソルなので、createStatementのSQLにFOR UPDATEは指定できてしまいます(sp_cursoropenのstatement)。
    質問もselectにFOR UPDATEを付ける事しか書いてないので何を狙っているのかはわかりません(というか最終的に応答速度の話になっているし)。
    -
  • どうも。何を狙っているかですが、(長文なのでチャット風に複数回分けて、コメントを投稿します。) -
  • そもそも、このシステムはOracle前提で開発していたのを(Read Commitedに全面的に依存した)、なにを血迷ったかSQLServer2000にしてしまったもので、そのせいで、その当時は(サーバーのRDBにも関わらず)時間を決めて1人ずつ使うしか無いシステムに成り下がっていました。 -
  • 今度SQLServer2014で、行バージョン有りにした事により、OracleのRead Commited前提のロジックとようやくかみ合ったので良かったです。 -
  • 良かったのですが、今回、Read Commited前提のロジック(特定の非常にスパースな確率でしか競合しないキーが管理するすべてのデータを得、RDB外の変数のみに終始して計算をし、そのキーのすべてのデータをいっぺんに置き換える)が成り立たない事態を要求されるようになりました。 -
  • それは、Aの処理の結果がBに影響を与え、Bの処理の結果がA'に影響を与えるというものでした。そうなると、複数SQL行にまたがって行バージョンを保持してもらうより無いと思いましたが、OracleではそれはFOR UPDATEです。 -
  • もしSQLServer2014のスナップショット分離がOracleのそれに相当するとするならば、「使える」と判断出来る傍証になると思った訳です。狙いはそれです。応答速度の話は、仕組みがどうなっているかをサイドチャネルから探ろうとしただけで、それが目的ではありません。 -
  • あくまで、「スナップショット分離レベル」というものがどういうもので、(良く知られている)FOR UPDATEとの関連性からそれを知りたかっただけです。(長文失礼) -
  • Takacさんが書いていますが、OracleのFor Updateは行ロックです、ロック中は対象の行はSelectできません。SQL ServerならWITH (UPDLOCK) 相当です。というか行ロックしていいなら行レベルのバージョン管理すら不要なのでは。
    -
  • スナップショット分離はトランザクション中一旦Selectしたものは他のトランザクションで更新された後、もう一度Selectしてもトランザクション開始時点の内容を取得するというものです。
    -
  • すいません。「というか行ロックしていいなら行レベルのバージョン管理すら不要なのでは。」は間違いですね。取り消します。 -
  • 行ロックというより更新ロック(排他ロック)ですね
    スナップショットなしでは、読み込み時に共有ロックがかかります。ORACLEのデフォルトやSQL Serverのスナップショットは、この読み込み時の共有ロックを行わなくするものです
    なので、「読み込み時に共有ロックして良いのであればスナップショットは不要」と言えるって事ですね
    -

  • さらに言うなら、他のトランザクションとの同時実効性を考慮しないなら、読み込み時に排他ロックかけるなら(for updateやWITH(UPDLOCK)するなら)REPEATABLE READやSERIALIZABLEで実行すれば良いって事になります
    -
  • (個人的感想でどこにも書いていないのですが)READ_COMMITTED_SNAPSHOT ONにしてしまうとロックヒントは完全無視の様に思えてなりません。(だから困っているのですが。。。ヒントなので無視はあり得るのでしょうけれど。。。) -
  • 少なくとも手元の2012Expressで試してみた限り、READ_COMMITTED_SNAPSHOT ONのデフォルト分離レベル(READ_COMMITED)でもSNAPSHOT分離レベルでも、WITH(UPDLOCK)ヒントで更新ロックが獲得されるのは確認しましたが
    SQL Serverの更新ロックは排他ロックではないので、望む動作かどうかはわかりませんが
    -
  • 個人的な意見で言えば、ロックヒントをどうこうする前に、分離レベルを上げて実行できないか検討するべきだと思います
    不必要なロックが獲得されるのでどうしてもより高い分離レベルで実行できないときに、自分でロックを操作するわけで、その際はそのDBMSでのロック操作に熟知している必要があります
    -
  • どういう動きを見て完全無視の様に思えるのかはわかりませんが、私の環境でも双方のSelectにWith(UPDLOCK)を付ければロック獲得に失敗しますのでロックはされていますね。
    片方にしか With(UPDLOCK)をつけていないという事はありませんか?
    -
  • 排他ロックが良いのであればSQL ServerではWith(XLock)がありますが、OracleのSelect ~ For Updateも排他ロックではありません(他セッションからのFor UpdateなしのSelectは実行可能だったかと)。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。