none
ファイルストリームの使用にあたり開始したトランザクションの放置を防止したい RRS feed

  • 質問

  • 皆様お世話になります。

    SQL Server ExpressにアクセスするクライアントアプリケーションをC/C++(ODBC)で開発しています。

    トランザクション処理はテーブル全体をロックしたままフリーズして全体に影響を及ぼすのが心配ですので、ストアドプロシージャにしてcommit/rollbackまでサーバー側責任で実行するべきと考えています。

    しかし、ファイルストリームはOSのファイルハンドルを使用するので、どう考えてもストアドプロシージャでcommitまで完結できず、commitまで実行する前にクライアントアプリケーションが予期せずフリーズする余地を与えてしまいます。

    ネットワーク障害なら十数秒で切断・ロールバックされることは確認しましたが、begin tranしたままアプリケーションがフリーズしてもタイムアウトしないようです。

    ネットで調べたところでは、.NET Framework ではタイムアウトを設定できるようですが、ODBCドライバーをC/C++で呼び出す開発手法では使えなさそうですし、アプリケーションがフリーズしたら.NET Frameworkもろともタイムアウトも効かないのでは?とも思います。

    ストアドプロシージャで完結できないファイルストリームを使用するにあたり、トランザクションの放置を防止する設計手法はあるのでしょうか?

    あるいは私の気にし過ぎ、勘違いもあるかもしれませんが、ご指摘の程お願いいたします。

    2020年12月28日 11:21

回答

  • トランザクションを開始したアプリケーションがコミットもロールバックもしないままフリーズ状態になり、かつ、アプリケーションが動作しているOSやハードは正常な状態ということを想定されているのですかね。
    GET_FILESTREAM_TRANSACTION_CONTEXT() は、トランザクションを発生させたセッションでのみ使用できたと思いますので、上記の状況の場合は対処策にはならないかもしれません。
    なお、一般的にコミットもロールバックもされないトランザクションが残ることで問題になるのは、ブロッキングでしょうか。
    考えられる対処策として、該当アプリケーションで長期間セッションを張り続ける実装になっていない場合は、セッションが開始されてStatusがsleepingのまま長期間残っているセッションを Kill する仕組みを作る、もしくは、ブロッキング発生時、ブロッキングが閾値に指定した時間よりも長く継続した場合、ブロッキング元のセッションを Kill するという仕組みを作ることで対処ができるかもしれません。
    • 回答としてマーク M14Cluster 2020年12月29日 3:53
    2020年12月28日 13:24

すべての返信

  • FILESTREAMでは

    新しい組み込み関数の GET_FILESTREAM_TRANSACTION_CONTEXT()を使用すると、セッションが関連付けられている現在のトランザクションを表すトークンを取得できます。 このトランザクションは、既に開始され、まだ中止もコミットもされていないトランザクションである必要があります。 アプリケーションでは、トークンを取得することにより、FILESTREAM のファイル システム ストリーミング操作を、既に開始されているトランザクションにバインドできます。 この関数は、明示的に開始されたトランザクションがない場合は NULL を返します。
    トランザクションをコミットしたり中止したりする前にすべてのファイル ハンドルを閉じる必要があります。 トランザクションのスコープを超えてもハンドルが開いたままになっていると、そのハンドルに対するその後の読み取りが失敗します。そのハンドルに対するその後の書き込みは成功しますが、ディスクに実際のデータが書き込まれません。 同様に、 データベース エンジン のデータベースやインスタンスがシャットダウンすると、開いているハンドルはすべて無効になります。

    と説明されていますが、問題になるのでしょうか?

    2020年12月28日 12:27
  • トランザクションを開始したアプリケーションがコミットもロールバックもしないままフリーズ状態になり、かつ、アプリケーションが動作しているOSやハードは正常な状態ということを想定されているのですかね。
    GET_FILESTREAM_TRANSACTION_CONTEXT() は、トランザクションを発生させたセッションでのみ使用できたと思いますので、上記の状況の場合は対処策にはならないかもしれません。
    なお、一般的にコミットもロールバックもされないトランザクションが残ることで問題になるのは、ブロッキングでしょうか。
    考えられる対処策として、該当アプリケーションで長期間セッションを張り続ける実装になっていない場合は、セッションが開始されてStatusがsleepingのまま長期間残っているセッションを Kill する仕組みを作る、もしくは、ブロッキング発生時、ブロッキングが閾値に指定した時間よりも長く継続した場合、ブロッキング元のセッションを Kill するという仕組みを作ることで対処ができるかもしれません。
    • 回答としてマーク M14Cluster 2020年12月29日 3:53
    2020年12月28日 13:24
  • 佐祐理様、NOBTA様、コメントありがとうございます。

    なるほど、最初からブロッキングと言えば誤解を招かずに済んだかもしれませんが、用語を覚えるのが苦手で済みません。

    NOBTA様から頂いたアドバイスは、こんな感じのSQLをサーバー上で定期的に実行する、ということですね。

    declare @spid as int
    declare @sql nvarchar(32)
    select @spid=spid from master.sys.sysprocesses
    where program_name='MyApp' and status='sleeping'
    and open_tran>0 and last_batch<(getdate()-30/86400.0);
    if @spid is not null
    begin
    set @sql = 'kill ' + cast(@spid as varchar(4))
    exec (@sql)
    end

    端末でトラブルが発生してもシステム全体に影響が及ばないことを最優先に考えるとタイムアウトは5秒で十分と思っていましたが、こういう手法だと、監視に必要な負荷も心配なので、負荷を計測して決めたいと思います。

    「ブロッキング発生時」のイベントが拾えるならその方が良い気もしますが、イベントの拾い方から勉強します。

    ありがとうございました。

    2020年12月29日 3:53
  • sys.sysprocesses は、将来のバージョンで削除されるシステム カタログであるため、sys.dm_exec_sessions などの動的管理ビューに置き換えても良いかもしれません。

    また、Connection Pooling を使用している場合、一般的には 最大セッションプール数までセッションを確立した状態(sleeping)で維持されると思いますので、Connection Pooling を使用している場合は、ブロッキングの発生を検知するスクリプトを実装されると良いかと思います。


    2020年12月29日 4:07
  • NOBTA様、ご指摘ありがとうございます。

    sys.sysprocessesは済みません。また古い記事に引っかかってしまったようですので、確認して修正致します。

    非推奨といえば、ファイルストリームも、OpenSqlFilestream()の記事に従って「Native Clientのインストール」に進みましたが、Native Clientのトップに非推奨と書かれていました。これはいいのでしょうか。といっても、MSOLEDBSQLのサンプルコードを見てもさっぱり分からなかったので、非推奨承知で開き直ることにしましたが。

    Connection Pooling は初耳ですが、有効にする方法は書いてあっても、デフォルトで有効とは書かれていないですし、SQLDisconnect()の後にセッションは残らないので、使っていないと思います。

    何れにせよ無駄にkillしないようブロッキングの発生は検知したいと考えていますが、取り急ぎ、ファイルストリームの書き込み手順が致命的でないと分かれば、他に進めたいことがまだ残っているので、ブロッキング対策の実装はもう少し先です。

    実装する際に分からなくなったら改めて質問させて頂くかもしれませんが、その際はよろしくお願いします。
    2020年12月30日 4:27
  • .NET Framework のものになりますが、SqlConnection.ConnectionStringのプロパティに Max Pool Size, Min Pool Size という Connection Pooling 用のプロパティが用意されています。

    なお、接続が維持されるのは、Min Pool Size に指定した値までですね。失礼しました。

    Native Client では UTF-8 (Unicode) が使用できない、新機能に対応していないなどの制約があるかと思いますが、通常のクエリ処理、既存の機能については使用できるかと思います。

    SqlConnection.ConnectionString プロパティ
    https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-5.0
    Max Pool Size
    Min Pool Size

    2020年12月30日 5:23
  • NOBTA様、コメントありがとうございます。

    .NET Frameworkも勉強しないと、と思っていますが、そもそもGUI開発に使っているC++Builderからどうやって呼び出すの?というレベルです。済みません。

    ということで、C++Builderから.NET Frameworkを呼び出す方法があるのか調べているうちに、あることに気づき、C#によるGUI開発を調べ直して Visual Studio に ".NET desktop development" を追加してみたところ、何ということでしょう、ほぼVCLではないですか!

    VCLの開発者がBorlandからMSに移籍して.NET Frameworkを作ったという記事はかなり昔に読んでいましたが、「ふーん」くらいで気に留めず、C#を試すも.NETをスルーしていました。なんという不覚。

    Visual C++でのGUI開発は20年以上前にMFCで挫折し、その後Delphiを経てC++Builderに走ったのですが、C++Builderも、日本語フォーラムに続いて英語フォーラムまでも閉鎖される有様なので、そろそろ見限るべきかと思っていたところでした。

    ということで、さようならC++Builder、今までありがとう!

    今開発中のものも来月中には正式リリースしたいので、C++Builderで突っ走ります。

    ほぼ関係ない返事で済みませんが、私にとっては、今年最大の事件でした。
    2020年12月31日 6:12