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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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