ワークフローと SQL Server デッドロックについて

Article
12/26/2012

こんにちは SharePoint サポートの森健吾 (kenmori) です。今回の投稿では、ワークフローをご使用のお客様より時折発生すると報告のあるデッドロックという現象について説明します。

診断ログの抜粋

07/27/2011 12:06:00.80 w3wp.exe (0x353C)                       0x3170 Windows SharePoint Services Workflow Infrastructure 72fr Unexpected
Workflow Save Instance: System.Data.SqlClient.SqlException: トランザクション (プロセス ID 115) が、ロック個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。
     場所 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     場所 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
場所 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
場所 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
場所 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
場所 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
場所 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
場所 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
場所 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
場所 Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)
場所 Microsoft.SharePoint.Workflow.SPWorkflowManager.SaveInstanceData(Guid trackingId, Stream instanceStream, DateTime wakeupTime, Boolean workflowCompleted, Boolean workflowSuspended, Boolean workflowFaulting, Boolean workflowTerminated, Boolean workflowCanceled, Boolean unlockInstance)

この現象は SharePoint 2007,　2010 で確認している現象であり、SharePoint 2013 で発生するかどうかについては確認がとれておりません。ご了承ください。
本投稿では、デッドロックとは何か、ワークフローとロックについて、どのように対処すべきかについてまとめます。

デッドロックとは

デッドロックとは、SQL Server 内の異なる複数のトランザクションにおいてロックが競合したことにより、SQL Server が処理を終了させる仕組みです。
SQL Server はデータの一貫性、整合性を保持するために、トランザクションごとに書き込み時、読み取り時に異なるロックを使用して更新データを保護します。このことにより、他トランザクションでの更新が確定 (コミット) される前の情報を読み込んで動作の整合性がとれなくなることのないように制御します。

他トランザクションによって更新中であるリソースを待ち続ける状況をブロッキングと言います。
しかし、このブロッキングが複数のトランザクション処理間で、お互いのロックしているリソースを待つ状態になった時に、そのまま待機し続けると永遠に待機する動作となってしまうことは想像できると思います。このような状況においてはSQL Server 側が即座に検知して、片方のトランザクション内の処理をエラー終了させます。このエラー終了される動作がデッドロックです。

デッドロックを語るにあたって、一部の人は「単純に SharePioint がリソースへのアクセス順を順守していないだけで、単なるバグなのでは？」と思っている方もいらっしゃると思いますが、発生原因はそんなに簡単ではありません。

タイトル : デッドロックを避けるコツ
アドレス : https://blogs.msdn.com/b/jpsql/archive/2011/03/31/tips.aspx

前記にて SQL Server チームブログの投稿にもある通り、アプリケーション開発者が予期しない形でロックが割り当てられる可能性があります。

運用側のメンテナンス (インデックス断片化、統計情報の管理) などの結果、クエリコンパイル結果が想定と異なって、テーブルスキャンとなりロック範囲を広げる場合や、大量書き込み処理によるロックエスカレーションの結果発生する場合もありますし、ワークフローの場合には特にユーザーの実装側に影響 (後記にて補足) をうける場合もあります。

もちろん、製品側に改善の余地があると判断される場合は全力を尽くしてフィードバックを行う所存ではあります。しかしながら、運用環境要因によってロック範囲が開発側の意図せぬ状況に陥っている場合においては、簡単に発生原因を特定して製品開発時に製品標準的に効果がある形での対処を行うということ自体が極めて難しい状況となります。

これまでにも、開発に報告して修正された現象はありますので弊社としてもベストを尽くしておりますが、いくつかの対処案はあるものの、現実的に最も有効な対処策としては現象発生時に処理を運用側で再実行するしかありません。この点につきましてはご理解いただきたいと考えています。

補足 : ユーザー側の実装によって影響をうける要因について

ワークフローは以前の投稿にも記載した通りワークフローランタイムに永続性サービスが使用されており、このサービスの動作として TransactionScope (Serializable 分離レベル) を使用し、ワークフローの進行状況とデータのコミットをクライアントスレッドにて 1 つのトランザクション内に組み込んで SQL Server に対して永続化コミットを行います。

なお、そのトランザクションの範囲については、ワークフローが継続的に処理を実行できる範囲までです。例として以下のような条件が存在するところまでとなります。

・DelayActivity などの待機
・ユーザーアクションの待機
・ワークフローバッチ処理のタイムアウト (5 分間) を超えるまで
・PersistOnClose 属性のついたアクティビティを実行するまで

ここまでの処理がすべて 1 トランザクション内に組み入れられる動作となります。このようにワークフローでは、他機能と比較しても、一度にかけるロックの範囲が数倍にも大きくなります。つまり、アイテムから値を読み込み、タスクを更新するなどを何気なく行っていても、これらのアクションが 1 つのトランザクション内でリソースにロックをかける順序にもなっています。

SharePoint Designer を使用してワークフローをお使いの場合も想定しており、その場合ユーザーは開発知識があることが前提ではありませんので、このようなデータアクセスの観点を正確に認識した上でワークフローを作成することは難しいと考えています。この点については、すぐには無理だとしても、設計レベルでの改善を図るようフィードバックしていきたいと考えています。

効果があると想定される対処策について

以下にこれまでのお問い合わせにおいて、お伝えして実際に有効と考えられる対処策を複数紹介します。

1. 運用に応じてインデックス断片化解消や統計情報を更新する。

SharePoint は、1 日 1 回インデックスの断片化状況をチェックし、必要性があると判断する場合はインデックスの再構築を行い、それによって統計情報が更新されます。
しかし、例えば夜間や早朝のバッチでアイテムを大量に作成する運用などを別途実施する場合、既存の処理だけでは断片化の解消、あるいは統計情報の更新が十分ではない場合があります。

上記のとおり、このような状況においてはクエリが適切なテーブルスキャンなどが発生し、適切な行のみをロックするとは限らない状況となります。
この状況を回避するために、必要なタイミングにてインデックスに対するメンテナンスを運用側で実施することは、運用上のリスクも低くもっとも即効性のある対処策となります。

タイトル : インデックスの再編成と再構築
アドレス : https://msdn.microsoft.com/ja-jp/library/ms189858(v=sql.105).aspx

インデックスの再構築

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

インデックスの再編成

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;

タイトル : クエリのパフォーマンスを向上させるための統計の使用
アドレス : https://msdn.microsoft.com/ja-jp/library/ms190397(v=sql.105).aspx

統計情報の更新

sp_updatestats

タイトル : SharePoint Server 2010 のデータベースメンテナンス
アドレス : https://technet.microsoft.com/ja-jp/library/cc262731%28v=office.14%29.aspx

2. 1 つのコンテンツ DB 内に組み入れるサイトコレクション数を減らす。

SharePoint 上で何も考えずサイトコレクションを作成すると、Web アプリケーション単位で 1 つのコンテンツ DB 内にすべてのサイトコレクションが格納される動作になります。制限値も考慮した上で、各コンテンツ DB 内に均等にサイトコレクションの格納数を保つように動作しますので、SharePoint サーバーの全体管理で新規コンテンツ DB を作成すると、新しく作成したコンテンツ DB 内に次のサイトコレクションが格納されます。

既存サイトコレクションを別コンテンツ DB に移行する場合は以下のような手順が有効です。

1)    サイトコレクションのバックアップを取得する。
2)    サイトコレクションを削除する。
3)    コンテンツ DB を作成する。
4)    サイトコレクションを復元する。(新コンテンツ DB に格納)

3. ワークフローのコミットポイントを増やす。

デッドロックを防ぐための 1 つの法則として、トランザクションをなるべく短く、ロックの影響を最小限にすることがあります。しかしながら、ワークフローは以下のアクションまで処理を続ける動作になります。

処理がある程度続くと判断される場合は DelayActivity または数分間停止するなどを使用して一度それまでの処理をコミットさせることをお勧めします。

4. トレースフラグ 1224 でロックエスカレーションを抑制する。 ( ただし注意が必要 )

ロックエスカレーションは、例えば一度に大量の行ロックが発生した際に、それをテーブルレベルのロックに切り替えて処理を行う内部処理です。
これは、SQL Server においてロックが大量に発生するとメモリを多く消費することを想定した動作です。結果的に、テーブルレベルにロックをかけた方がパフォーマンスの観点を考慮すると高速になることが想定されます。

トレースフラグ 1224 を使用すると、書き込み件数を考慮したロックエスカレーションの実行を抑止します。ただし、例えばフルクロール時における検索 DB に対する大量書き込み時などにおいて、メモリ使用量が増えるかたちとなりますので、この対処を実施すべきかどうかについては事前に十分な検討をする必要があります。

タイトル : ロックのエスカレーション (データベースエンジン)
アドレス : https://msdn.microsoft.com/ja-jp/library/ms184286(v=sql.105).aspx

タイトル : トレースフラグ (Transact-SQL)
アドレス : https://msdn.microsoft.com/ja-jp/library/ms188396(v=sql.90).aspx

タイトル : SharePoint と SQL ブロッキングの話 Part1
アドレス :　https://blogs.technet.com/b/sharepoint_support/archive/2012/07/03/sharepoint-sql-part1.aspx

5. SQL Server をスケールアップする。

単純に SQL Server をスケールアップするのも有効な対処策となります。例えば、CPU やメモリ、ディスクなどを増強したり増やしたりすれば、単純に処理速度が上がるためトランザクションがロックを使用する期間を短くでき、ロックが競合するに至る可能性を下げることができると考えられます。
また、ロックが重複してロックエスカレーションに至った上でデッドロックに至る確率も合わせて減らすことが可能と考えております。

6. エラー発生したワークフローを再起動する運用を考慮する。

上述の対処がすべて対処不可となった場合、あるいはそこまでの検討を実施していない状況においても、デッドロックの究極の対処策はやはり運用側で再起動を実施する方法となります。
この対処の具体的な説明については、先日の本ブログに対する投稿にてまとめておりますので、ご確認ください。

タイトル : 内部エラー (WinWF Internal Error) 発生を想定したSharePoint ワークフローをデザインする
アドレス: https://blogs.technet.com/b/sharepoint_support/archive/2012/11/27/a-sharepoint.aspx

まとめ

デッドロックという観点以外にも、ワークフローがエラー終了することはビジネスに遅延を招きますので、これまでにもリトライや通知機能などを追加するなどを開発本部にフィードバックを行ってきました。
しかしながら、ワークフローランタイム側がユーザーの入力ミスなどといった必ず発生するエラーと、一時的に発生するエラーとを見分けることは困難であり、さらに修正 (機能追加) の範囲も膨大となるため、製品品質を保つ上でリスクを考慮して見送られている状況です。

製品としての根本解決は極めて厳しい状況であることから、我々サポートとしてもお問い合わせ時に可能な限り現象を回避するための対応を実施している状況です。また、お客様側でより迅速な問題解決を実現するために、情報提供などを実施させていただきたいと考えており、このような情報公開に踏み切っている状況です。

是非とも、これらの情報を現象発生時に何卒お役立ていただけますと幸いです。

ワークフローと SQL Server デッドロックについて

Additional resources