SQL Server のことなら SQL Quality SQL Server パフォーマンス チューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム > 技術情報 > SQL Server 2012 自習書 No.12 Analysis Services によるインメモリ BI 入門

SQL Server 2014 実践シリーズ (HTML 版)
「No.1 インメモリ OLTP 機能の実践的な利用方法」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.1 インメモリ OLTP 機能の実践的な利用方法」の HTML 版です。 日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

4.3 更新競合が多発する場合の考え方 ~ナンバリング処理など~

更新競合が発生した場合の再試行ロジックについては、第2章で、次のように説明しました。

00214

ネイティブ コンパイル SP を実行するための、通常のストアド プロシージャを作成して、BEGIN TRY の中でネイティブ コンパイル SP を実行して、実行が成功したのか、失敗したのかを判断し、成功したら WHILE ループを抜けて終了、失敗した場合は、CATCH ブロックでエラー番号が更新競合かどうか(41302、41305、41325、41301、1205 かどうか)をチェックして、これらの番号なら再試行を行う、そうでない場合は THROW で、該当エラーをそのまま通達する、という流れになっています。

CATCH ブロックで処理しているエラー番号の意味は、次のとおりです。

00215

このように、インメモリ OLTP では、更新競合に対しては、再試行ロジックを利用するのが基本になりますが、第2章で説明したポイントカード システムのように、そもそも更新競合が発生しない(同じカードID のデータが同時に更新されることがない)というケースもあります。この場合は、再試行ロジックは不要で、アプリケーション側でエラー処理をするだけで十分です。

◆ 更新競合が多発するケース ~DELAY、再試行回数の調整~

オンライン ブックの「再試行ロジックのガイドライン」トピックにも記載されていますが、更新競合(41302 エラー)が多発する場合には、WAITFOR DELAY を調整してタイミングを遅らせたり、再試行回数を増やしたりするといったことも考慮する必要があります。

00216

このように「WAITFOR DELAY '00:00:00.001'」と記述した場合は、1ミリ秒待機して、再試行が行えるようになります。

第1章で説明した bwin 社では、次のような再試行ロジックを利用しています。

00217

1ミリ秒待機して、再試行を行ってしますが、再試行回数は設定せずに、成功するまで再試行を行うコードになっています。

◆ ナンバリング処理 ~手動で連番生成をしている場合~

ナンバリング処理(採番処理)を手動で行っている(手動で連番を生成している)場合は、多重度が上がった場合に(50多重100多重など同時実行数が増えた場合に)、更新競合が多発してしまいます。

これについて、次のような「連番管理」テーブルを例に説明します。

00218

このように、従来ながらのディスク ベースのテーブルでは、SELECT 後に UPDATE を行う場合には、UPDLOCK(更新ロック)をかけて、読み取りデータを保護する方法をとります。

これに対して、インメモリ OLTP では、更新ロックがサポートされていないので、更新競合を受け入れる必要があります。これは、次のような状況です。

00219

インメモリ OLTP では、後から更新した方が負けて、41302(更新競合)エラーが通達されます。このエラーは、多重度が上がった場合に多発する可能性があります(この連番管理テーブルの例では、同じ管理番号への同時アクセスが集中する場合に発生し得ます)。

これについて、次のようなネイティブ コンパイル SP を例に説明します。

CREATE PROC sp_numbering
@kanriNo int@renban int OUTPUT
WITH NATIVE_COMPILATIONEXECUTE AS OWNERSCHEMABINDING
AS
BEGIN ATOMIC
 WITH TRANSACTION ISOLATION LEVEL SNAPSHOT
        LANGUAGE N'japanese')
  DECLARE @現在の番号 int
  -- 現在番号取得
  SELECT @現在の番号 開始番号
   FROM dbo.連番管理_InMem
    WHERE 管理番号 @kanriNo
  -- 現在番号  UPDATE
  UPDATE dbo.連番管理_InMem
   SET 開始番号 @現在の番号 + 1
    WHERE 管理番号 @kanriNo
  -- 採番された現在番号 1 OUTPUT 返す
  SELECT @renban @現在の番号 + 1
END

連番管理_InMem」テーブルから現在の番号(開始番号)を取得して、それに +1 をしたものを UPDATE し、OUTPUT パラメーターでこの値を返します。

このネイティブ コンパイル SP(sp_numbering)は、次のように実行することができます。

DECLARE @kanriNo int 3@renban int
EXEC sp_numbering @kanriNo@renban OUTPUT
SELECT @renban
00220

◆ ostress ツールで多重実行 ~更新競合のシミュレート~

更新競合を確認するには、多重実行をする必要がありますが、これは ostress ツール(RML Utilities に含まれる負荷テスト ツール)を利用して試すことができます。ostress ツールは、次のように実行できます(詳しくは後述します)。

ostress.exe -n20 -r1 -Sサーバー名 --dデータベース名 --Q"DECLARE @kanriNo int = 3@renban intEXEC sp_numbering @kanriNo@renban OUTPUT"

-n20」と指定することで 20多重で、「-Q""」に記述したクエリを同時実行することができます。なお、-r1 は 1回実行、-S で接続先となる SQL Server の指定、-E で Windows認証、-d でデータベース、-q で quietモードを指定するという意味です。

実行結果は、次のようになります。

00221

このように、更新競合への対応を行っていない場合は、多重実行時にエラーが多発してしまいます。

更新競合への対応を行うには、次のように作成します(冒頭に記載した再試行ロジックとほぼ同じ)。

00222

このストアド プロシージャ(sp_numbering_retry)は、次のように実行できます。

DECLARE @kanriNo int 3@renban int
EXEC sp_numbering_retry @kanriNo@renban OUTPUT
SELECT @renban
00223

ostress ツールでの多重実行では、次のようにストアド プロシージャの名前のところのみを変更して実行してみます。

ostress.exe -n20 -r1 -Sサーバー名 -E -dデータベース名 -q -Q"DECLARE @kanriNo int = 3@renban intEXEC sp_numbering_retry @kanriNo@renban OUTPUT"

しかし、更新競合がまだ発生してしまいます(環境によっては発生しない場合もあります)。

00224

これは、再試行回数が 10回では少ないために発生しているので、「DECLARE @retry INT = 10」の 10 50 へ変更して、ストアド プロシージャを再作成し、もう一度 ostress を実行してみます。

00225

今度は、エラーが発生することなく、実行が成功します。

次に、ostress での多重度を 100 へ変更(-n20 -n100 へ変更)して実行してみます。

ostress.exe -n100 -r1 -Sサーバー名 -E -dデータベース名 -q -Q"DECLARE @kanriNo int = 3, @renban int; EXEC sp_numbering_retry @kanriNo, @renban OUTPUT"

結果は、次のように更新競合が発生してしまいます。

00226

この更新競合は、環境によっては発生しない場合があり、弊社環境では 5回に 2回ぐらいは更新競合が発生しませんでした。しかし、更新競合が発生する場合があるということは、再試行回数が 50回でも少ないということです。

したがって、実際のアプリケーションでの同時実行数や、後続の処理(採番した後の実際の処理)などを含めて、どのくらいの再試行回数が妥当なのか、あるいは WAITFOR DELAY で待機する時間を「'00:00:00.002'」のように変更して、2ミリ秒待機するようにするなど、実際のアプリケーションでテストしておくことが重要です。また、そもそもナンバリング処理(採番処理)を手動で行うのを止めて、IDENTITY やシーケンスを利用するようにすれば、このような更新競合に悩まされることはなくなります。

◆ 再試行回数を調べてみる

再試行がとれぐらい発生したかは、次のように OUTPUT パラメーターを利用すれば、確認することができます。

00227

この OUTPUT パラメーターは、次のように取得できます(ADO.NET の場合)。

00228

このように再試行回数を調べたところ、次のような結果になりました。

00229

結果は、実行のたびに大きく異なっていたのですが、そのうちのワースト ケースの部分を抜き出してみました。ベスト ケース(良い結果)では、多重度 100 で再試行回数がわずか 3回多重度 200 でわずか 6回ということもあったのですが、表にあげたように多重度 200 以上では 8万回以上の再試行回数となってしまうことが多々ありました(最大再試行回数は 1,000 を超えるものも有り、多重度 200 では 3,007回再試行するものもありました)。

WAITFOR DELAY で待機する時間を「'00:00:00.002'」(2ミリ秒)に変更した場合は、次のようになりました。

00230

この結果も、実行のたびに大きく異なっていたのですが、このうちのワースト ケースを表にしました。再試行回数は、大きく減らすことができましたが、待機時間が 1ミリ秒長くなってしまうことが難点です。

しかし、再試行回数を減らせられるのは、大きな差で、次のように考えることができます。

00231

待機時間(WAITFOR DELAY)が 1ミリ秒のときは、1,000回以上再試行するものがありましたが、これだと 1秒以上の実行時間がかかってしまうことになります。これに対して、2ミリ秒に設定して、再試行回数を 100回に減らせられるのであれば、200ミリ秒程度の実行時間で済むわけです。

◆ ナンバリング処理はインメモリ化すべきか???

再試行回数が多いということは、待機時間だけでなく、その分の実行時間もかかっているということを考慮しなければなりません(以下の表)。

00232

1回の実行速度は、仮のものとしていますが、例えばディスク ベースで 500マイクロ秒で完了する処理は、再試行をしなくて済む(UPDLOCK を利用して同時更新をブロックしている)ので、実行時間は 500マイクロ秒で済み、これに対してインメモリ OLTP で実行速度が 100マイクロ秒だと仮定して、再試行回数が 100回であったとすると 10ミリ秒も実行時間がかかってしまうわけです(もちろん、すべての実行が 100回の再試行をするわけではなく、1回の実行で終わるものもあれば、数回の再試行で済むものも多数あります)。

このように、1回の実行速度が速くなっても、再試行の回数が増えてしまえば、その差は逆転してしまうことがあり得ます。

したがって、実際のアプリケーションでの同時実行数や、後続の処理(採番した後の実際の処理)などを含めて、実際のアプリケーションで入念なテストを行って、どれぐらいの更新競合/再試行回数が発生するのか、ナンバリング処理をディスク ベースのままにしておくのか、インメモリ化をするのかをしっかりと検証しておくことが重要です。

そもそも、ナンバリング処理(採番処理)を手動で行うのを止めるというのがベストな選択で、IDENTITY シーケンスを利用するように変更するのがお勧めになります。また、更新競合が多発するような状況を避けるようにアプリケーションやテーブルを再設計していくことも重要です。

目次へ | 前のページへ | 次のページへ

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第 51 回:PASS Summit と MVP Summit で進化を確信!
第 50 回:新しくなった Power BI(2.0)の自習書を作成しました!
第49 回:Excel 2016 の Power Query を使う
第 48 回:新しくなった Microsoft Power BI ! 無料版がある!!
第 47 回:「Microsoft Azure SQL Database 入門」 完成&公開!
第 46 回:Microsoft Power BI for Windows app からの Power BI サイト アクセス
第 45 回:Power Query で取得したデータを PowerPivot へ読み込む方法と PowerPivot for Excel 自習書のご紹介
第44回:「SQL Server 2014 への移行とアップグレードの実践」ドキュメントを作成しました
第43回:SQL Server 2014 インメモリ OLTP 機能の上級者向けドキュメントを作成しました
第42回:Power Query プレビュー版 と Power BI for Office 365 へのクエリ保存(共有クエリ)
第41回:「SQL Server 2014 CTP2 インメモリ OLTP 機能の概要」自習書のお知らせです
第40回: SQL Server 2012 自習書(HTML版)を掲載しました
第39回: Power BI for Office 365 プレビュー版は試されましたか?
第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回: iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回: PASS Summit 2012 参加レポート
第31回: SQL Server 2012 Reporting Services 自習書のお知らせ
第30回: SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回: 書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回: SQL Server 2012 の Power View 機能のご紹介
第25回: SQL Server 2012 の Data Quality Services
第24回: SQL Server 2012 自習書のご案内と初セミナー報告
第23回: Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本美穂松本崇博

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)