排他制御を実現できるDB

解決


歯痛  2004-06-21 19:39:32  No: 114070

どなたかぜひお教えください。

JET4.0+VB6.0をadoでアプリケーション開発しています。

ユーザーのアクションによりレコードセットを取得し、
その中をmovenextやmovepreviusなどでカーソルを移動し
formにその行の情報を表示させるようにしています。

複数のユーザーが同時に使用するのですが、同じ行を
表示させないようにする必要があります。
今までは、カーソル移動した直後にflgを確認、なければ表示してflgをon、
flgが既にあれば次へ移動。。というように強引に排他を実現しようとしていたのですが
やはり100%ではありません。
(80%ぐらい  同時にアクションおこすのが約2秒以内であればoutです。。)

色々調べたところ、mdbでは限界があるようなので、DBをMSDEに変更しようと
思うのですが、MSDEなら実現可能なものでしょうか??
(いざ、導入という前に不安になってきました。。)
また、UPDATEを発行せず、単に、「その行を他のものが既に参照していたら」というような
判断は可能なものでしょうか?

どなたかご教授ください。


特攻隊長まるるう  2004-06-21 21:38:27  No: 114071

MSDE(正式名称はMicrosoft SQL Server 2000 Desktop Engine)
自体は使ったこと無いですが、SQL Server 2000と完全な互換性が
あると言うことですので SQL Server の知識から書込みしたいと
思います。ただボクは、複雑な処理は極力避けるようにプログラム
してきましたので、データベースに関して深く理解があるわけでは
ありません。簡単な処理で実現できそうな方法を提案します。

トランザクション処理に関して正しく理解されたなら、データベース
の整合性は取れるでしょう。ただし、お望みの機能を実現するには
多少の工夫が必要と思います。
トランザクション処理とロックの概要として次のリンクページの
内容に目を通してみてください。
http://www2p.biglobe.ne.jp/~sakurait/cstrue/chap5main.htm

このページで『応答を待つロック』と言う表現の機能に近いものを
実現しようとされていると解釈しましたが、同じページでリスクが
紹介されているように、今のデータベースが目指している概念とは
違っているように思います。特に[VB.NET]などではデータベースへの
接続は非接続型となっており、ユーザがテーブルを占有する時間は
最小になるよう考えられているようです…で、
>また、UPDATEを発行せず、単に、「その行を他のものが既に参照していたら」というような
>判断は可能なものでしょうか?
と言う内容も含めてデータベースの機能に求めるよりは、フラグを
立てて…の処理のほうが確実だと思います。

トランザクション処理内で自分の処理対象となる条件のテーブルに
フラグを立てる。但し、フラグだけでは複数ユーザがアクセスする
データベースでその後も処理を続けるには何かと都合悪いです。
端末で固有の端末名やユーザ名を利用して UPDATE した方が良い
でしょう。ここまでを1つのSQL文で実行してしまいます。
トランザクション処理終了。
その後(処理対象のフラグをすべて立てた後)、自分の立てたフラグ
のテーブルのみ表示…などで実現できるのではないでしょうか?。


歯痛  2004-06-21 22:52:55  No: 114072

詳細な説明をいただき、ありがとうございます。
MSDEなら行レベルのロックをサポートしているようでしたので
導入を検討したのですが、ご紹介いただいたリンクを見ると
DB側の機能で実現するよりもFLG等でカバーしたほうが良さそうですね。

FLGでおこなうならMSDEに変更することなく、
MDBのままでも実現可能でしょうか?

現在既にFLGで実装しているのですが、
どうしてもタイミングによっては同一行を
複数ユーザーが参照してしまいます。
トランザクション機能がないMDBでは難しいのでしょうか?


36NET  2004-06-21 23:05:10  No: 114073

FLGなら

update set FLG=1 from テーブル where 省略 AND FLG=0

参照

update set FLG=0 from テーブル where 省略 AND FLG=1

のような流れで最初のupdate文で更新レコード数が0の場合は
他のユーザが参照していると判断して参照を中断するとゆう処理は
いかがでしょうか?


歯痛  2004-06-22 02:05:11  No: 114074

どうもありがとうございます。

>最初のupdate文で更新レコード数が0の場合は
>他のユーザが参照していると判断

この判断をおこなう為の条件としては
adLockPessimistic(レコード単位での排他的ロック)が
サポートされているDBでないとダメということになりますでしょうか?
(jetは×、MSDEは○)

MDBの場合だと、FLG=1のUPDATEを複数ユーザーでほぼ同時に実行した場合
幾人かのユーザーが、同時にFLGをONにしたような状態に陥るときがあります。

関係は特にないですか?(MDBでも可能?)


36NET  2004-06-22 03:28:20  No: 114075

すいませんadLockPessimisticに関しては使った事がないので
説明を控えさせて下さい...

この場合誰がFLGをONにしたか判定出来ればよいと思うので
ADOならExecute("UPDATE文", RecordsAffected)で
RecordsAffectedに"UPDATE文"で反映されたレコード数が
取得出来るので自分が更新出来た場合は「1」が代入されて
他のユーザに更新された場合はwhere条件で該当レコードが
見つからなかったと処理されて「0」が代入されると思います。


特攻隊長まるるう  2004-06-23 04:57:25  No: 114076

>FLGでおこなうならMSDEに変更することなく、
>MDBのままでも実現可能でしょうか?
そもそも Access は複数のユーザにアクセスされる事を想定していない
と認識しております。歯痛さんも最初の書き込みで
>色々調べたところ、mdbでは限界があるようなので、DBをMSDEに変更しようと
>思うのですが、MSDEなら実現可能なものでしょうか??
と書かれてますが、調べた『MDB のままでの限界』に『複数のユーザによる
アクセス』に対応する情報は含まれていないのでしょうか?データベースや
フラグの処理以前に
>複数のユーザーが同時に使用する
時点でボクの中では Access は"無し"です。その状態で『実現可能か?』
と聞かれても何を基準にして答えればいいのか分かりません。

>トランザクション機能がないMDBでは難しいのでしょうか?
これも困った質問なのですが、トランザクション処理はデータベース操作に
おいて基礎的な知識に分類されると思います。Webでの検索でもたくさん
引っかかります(Googleで143,000件程度)。…もちろん、どのような事を意味
するか当然理解されてると思いますが、では『トランザクション機能がない』
ということは何を意味しますか?。ボクはごくごく簡単に言うなら『データ
ベースの整合性が取れない』と理解してます。それはボクにとってはデータ
ベースの意味がないと思われることです。何をもって好しとするのか?その
基準がまったく理解できません。ですから答えられる質問ではありません。
ボクの解釈では難しいとか難しくないとかの話ではないのです。

もちろんユーザさんの強い要望や環境的な止むを得ずの場合もあるでしょう。
しかしその判断は歯痛さんのするべき事ですし、それで MDB ファイルが
壊れるかどうかは環境と運に依存しています。実行環境でやってもらうしか
ないことですので、ボクが答えられる問題ではないと判断します。

以上、ここまでの質問はフラグがどうとか言う問題ではありません。データ
ベースとして Access を使用するのが適切か?という問題です。

で、実際の処理の工夫ですが、
>(80%ぐらい  同時にアクションおこすのが約2秒以内であればoutです。。)
…これは明らかにプログラムがマズいと思っています。MDB を使っていても
もう少しマシな処理ができるはずです。
その方法も含めて前の書き込みで書いてあります。書き込みした通りの処理を
プログラムすればもう少しマシな処理になるハズですが?

重要な点は
>固有の端末名やユーザ名を利用して UPDATE

>1つのSQL文で実行してしまいます。
という所です。もっと書きますと、UPDATE の処理と表示の処理を分けます。
つまり、2回SQL文を実行するという事です。ここは 36NET さんの書き込
まれた内容をうまく利用すれば1回でできたりするのかな?…とも思います
が、36NET さんの方法をボク自身が試したことがないのでベタに2回実行する
処理で書き込みしておきます。処理を1つに出来るかどうかはご自分で研究
してください。
まず、最初のSQL文は
  UPDATE TestTbl SET CheckOutUserName = '特攻隊長まるるう'
  WHERE CheckOutUserName = ''
  AND 処理対象となるレコードの条件
です。Visual SourceSafe やエクセルのチェックアウトに近いイメージでしょうか?
処理対象となるレコードの更新を1処理でやってしまいます。そして2番目の
SQL文は
  SELECT * FROM TestTbl
  WHERE CheckOutUserName = '特攻隊長まるるう'
です。これだけ書けば理解して頂けたと思いますが、CheckOutUserName
として自分の名前で更新できたレコードのみ処理対象として"改めて"呼び出す
わけです。更に、これでも同時実行の不具合が起こる場合、1つ目のSQL文
実行と、2つ目のSQL文実行の間に待機時間を起きます。もし同時実行で、
CheckOutUserName = '特攻隊長まるるう'
のレコードが
CheckOutUserName = ''
と判断されて
CheckOutUserName = '歯痛'
と更新されたとしても、
2回目のSQL文実行までに十分時間を置けば
CheckOutUserName = '歯痛'
のレコードは処理対象から外れるという仕組みです。

何件のレコードを処理するか等で待機時間の調整の必要があります。
フラグ1or0では、完全に分割された処理ですので処理できません。
CheckOutUserName として端末固有のフラグを立てる必要があります。
1つの端末でアプリケーションの2重起動をするとマズい処理となります。

以上の注意点で実現はそう難しくないと考えます。


Dental  2004-06-23 08:03:27  No: 114077

[歯痛さん / 2004/06/21(月) 10:39:32]
> 複数のユーザーが同時に使用するのですが
この時点で、Jetの採用はナンセンスと思われますので、
確かに、MSDEに移行された方が良いでしょうね。

《排他制御の罠》
http://www.naboki.net/access/achell/achell_01.html

Jetで実現可能か不可能かと問われれば、「可能」ですが、
個人的には、MSDE 等を採用される事をお奨めします。

[歯痛さん / 2004/06/21(月) 13:52:55]
> トランザクション機能がないMDBでは難しいのでしょうか?
Jet にも『トランザクション 機能』は、実装されていますよ。
まぁ、「トランザクション ログ」はありませんけれども。

Jet であったとしても、BeginTrans 等はやはり必要とされます。
そもそも、トランザクションを張らずに mdb ファイルを更新するのは、
あまり良いコードとは言えませんし。(下記参照)

《Jet Engineのキャッシュとその制御》
http://www.canalian.com/workshop/access/JetCache.html

[歯痛さん / 2004/06/21(月) 17:05:11]
> adLockPessimistic(レコード単位での排他的ロック)が
> サポートされているDBでないとダメということになりますでしょうか?
> (jetは×、MSDEは○)
Jetでも、adLockPessimistic はサポートされています。
ロック単位がレコード単位ではなく、ページ単位だというだけで。

> MDBの場合だと、FLG=1のUPDATEを複数ユーザーでほぼ同時に実行した場合
> 幾人かのユーザーが、同時にFLGをONにしたような状態に陥るときがあります。
リードキャッシュが更新されていなかったのでしょう。先述した
《Jet Engineのキャッシュとその制御》のページを参照してみてください。

ちなみに、MSDEならば常にレコード単位になるというわけでも無いので、
いずれにしても、特攻隊長まるるうさんが紹介されたURLなどを参考にして、
ロックについて十分に理解した上で、かつ、十分に実験を行った上で
採用しないと、期待した結果は得られないかと思います。

《Books Online - SQL Server のロック》
http://www.microsoft.com/japan/msdn/library/ja/acdata/ac_8_con_7a_7xde.asp

《魅惑(困惑)のSQL Server》
http://www.unisys.co.jp/club/net_view/20030129.html

[特攻隊長まるるうさん / 2004/06/22(火) 19:57:25]
> もっと書きますと、UPDATE の処理と表示の処理を分けます。
> つまり、2回SQL文を実行するという事です。ここは 36NET さんの書き込
> まれた内容をうまく利用すれば1回でできたりするのかな?…とも思います
更新 / データ取得等のビジネスロジックを、ストアドプロシージャとして
データベース側(MSDE等)にまとめてしまうのも手かな。Jetだと無理ですけど。


歯痛  2004-08-06 18:58:50  No: 114078

皆様
連絡が遅くなり申し訳ございません。
おかげさまで、JETでどうにか排他制御を実現することができました!

  ・UPDATE句を[cn.BeginTrans][cn.CommitTrans]で明示的に
    TRANをおこなうようにしました。
  ・処理対象のチェックと、FLGのONを1つのUPDATE句でおこなうようにしまし      た。(それまでは、ますSELECT句でFLGの有無をチェックし、FLGが無ければ
    その対象にUPDATE句を発行しFLGをONするという2つに分かれていました。)  
   ・使用中かそうでないかの判別は上記でUPDATEされたものがあるかどうかによ      りおこなうようにしました。
  ・リードキャッシュを更新するようにしました。
      Dim JE As New JRO.JetEngine  '・・宣言部分
      JE.RefreshCache cn

(参考)ひとつにまとめたSQLとFLG有無判断部分
        cn.BeginTrans
        mysql = "Update GOGO set GOGO.使用中FLG = '1', " _
        & "TBLKAIIN.最終更新日時 = NOW, " _
        & "TBLKAIIN.担当者 = '" + username + "' " _
        & "WHERE GOGO.識別NO = '" & txt識別NO.Text & "' " _
        & "and GOGO.使用中FLG = '0' "
        cn.Execute mysql, cnt
        cn.CommitTrans
                
        If cnt = 0 Then
            Call AFG
            Call BFG
            MsgBox "この識別NOは" + userST + "が使用中!" & Chr(13) & 
            txt識別NO.Text.Text
            Call movenext
        Else
           ・
            ・      
教えていただいた手法を全て適用しましたので、一体どれが効いたのかは
よくわからないことと、リードキャッシュの更新をおこなうのにどの箇所が
適切なのかよくわかっていないのでやたらめったに付与している状態ではありますが、とにかくマルチユーザーで使用してもいまのところ排他が100%実現できています。

クリティカルな処理にACCESSは不向きであることはよくわかりましたので
このシステムの重要度が増すようであれ、商用RDBMSに移行したいと思います。
何とは友あれ、ACCESSでも排他制御(擬似?)ができるということで
とても感動しました!(よかったあ〜。)

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

※MSDEは同時接続5USERという制限があるというような話を聞きましたので
  MSDEへの移行は見送りました。


※返信する前に利用規約をご確認ください。




  


  このエントリーをはてなブックマークに追加