C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

JET/ACE ロックとインデックス フィールドの値の更新

掲示板トップ C# と VB.NET 入門 新規作成 利用方法/規約 ツリー表示 トピック表示 ランキング 記事検索 過去ログ

[トピック内 8 記事 (1 - 8 表示)]　 << 0 >>

■102625 / inTopicNo.1) 　JET/ACE ロックとインデックス フィールドの値の更新
  ▼■
□投稿者/ eb (5回)-(2023/11/28(Tue) 05:01:15)

分類:[VB6 以前]　

環境は Windows 10 + Access 2019 VBA です（VBA ですみません）。

行ロックのテストをしているのですが、
インデックスが設定されたフィールドの値を更新した際の挙動で悩んでいます。
MS はこう明記しているのですが、

ページ レベル ロックとレコード レベル ロック | Microsoft Learn
https://learn.microsoft.com/ja-jp/previous-versions/office-development/cc376645(v=msdn.10)
>レコード レベル ロックをオンにしても、メモ フィールドやインデックス フィールドの値の更新には適用されないため、これらの操作を行う場合は、ページ レベル ロックに設定してください。

私が書いた簡単なテストプログラム2本で、両方否になるはずなのに、
行ロックの成否が分かれました。
理由が今でも未解決なのですが、やはり1本はどこかが間違っていて、
インデックスが設定されたフィールドの値を更新した場合（commit する瞬間に）
ページロックになるということでいいのでしょうか。
あるいは、「インデックス フィールドの値の更新」の解釈がおかしかったでしょうか。

助けていただければ幸いです。よろしくお願いします。
引用返信 編集キー/
■102627 / inTopicNo.2) 　Re[1]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ 魔界の仮面弁士 (3725回)-(2023/11/28(Tue) 06:08:51)
■No102625 (eb さん) に返信
> 私が書いた簡単なテストプログラム2本で、両方否になるはずなのに、
> 行ロックの成否が分かれました。

具体的に、どのような構造のテーブルに対してどのような操作を行ったのか、
第三者が検証できるようなテスト手順を共有できますか?
引用返信 編集キー/
■102633 / inTopicNo.3) 　Re[2]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ eb (7回)-(2023/11/28(Tue) 19:30:31)
魔界の仮面弁士様、たびたびありがとうございます。

結論から言うと、あくまで私の試行錯誤結果ですが、
悲観ロックして Update 実行前までが行ロック、
Update 実行後から CommitTrans までがページロックだと確認されました。
最初の投稿の、「片方ではできて、片方ではできない」は、
そういう切り替わりを想像できなかったせいでした。
MS 公式の文章からは、私では到底予想できませんでした……。

個人的に、「Access は行ロックできない」と言われるのが
Access（と ACE）のバージョンと技術次第でどうにかなるのか知りたくて骨を折ったのですが、
苦労の末に「やっぱりだめなんだ」という結論を手に入れたよう……
魔法の仮面弁士様のような大先輩方の知見を、20年以上遅れて追認したようです（苦笑）。
魔法の仮面弁士様も、小さな会社の IT 担当者に、
「非連結やワークテーブルで作りなさい」と助言なさっているということでよさそうでしょうか。
あるいは、MS もとうに関心が薄いらしい（？）Access 以外の新しい何かをお勧めになるでしょうか。
引用返信 編集キー/
■102634 / inTopicNo.4) 　Re[3]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ 魔界の仮面弁士 (3726回)-(2023/11/29(Wed) 09:38:07)
■No102633 (eb さん) に返信
>>> 行ロックのテストをしているのですが、
>>> インデックスが設定されたフィールドの値を更新した際の挙動で悩んでいます。
>>> 助けていただければ幸いです。よろしくお願いします。
>> 第三者が検証できるようなテスト手順を共有できますか?
> 結論から言うと、あくまで私の試行錯誤結果ですが、

テスト手順が不明瞭なまま「両方否になるはずなのに、行ロックの成否が分かれました」と
言われても状況が分からないので、検証手順を共有して欲しい、と伝えたつもりでした…。

今回の「試行錯誤の結果」には、どのようなテーブルをどう操作したのかが曖昧ですし、
DAO の話なのか ADO の話なのか、パラメーター指定はどうなっているのかなど、
状況説明が不足しすぎていて、どのようなテスト手順だったのか、まったくもって分からないです。
更新クエリの場合、バインドしたフォームから編集した場合、Recordset を編集する場合などなど。


> 悲観ロックして Update 実行前までが行ロック、
> Update 実行後から CommitTrans までがページロックだと確認されました。

それは共有ロックが排他ロックに切り替わる「タイミング」の話でしょうか。
質問の本題は、行ロック/ページロックの差異、すなわちロックされる「範囲」の違いの話かと思っていたのですが。

DAO の場合、Edit メソッドを読んだ時点でロックが取得され、Update 時に解放されます。
この動作は LockEdits プロパティの影響を受けます。
http://sennin.image.coocan.jp/access/access15/access15.htm
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/recordset-lockedits-property-dao?WT.mc-id=DT-MVP-8907

一方、ADO の場合は、Collect プロパティなどに書き込みを開始した時点が、
DAO でいうところの Edit メソッド相当の動作にあたります。
そして編集開始時にロックが開始されるか、Update 時にロックが開始されるかは
OLE DB Provider の実装と、カーソルの種類によって異なります。
クライアントカーソルでは後者だったかな…? ちょっとうろ覚え。

それと、Access の場合、テーブルを Form や Report にバインドして使うことが多いですが、
こちらは RecordLocks プロパティの影響を受けます。既定値は0:楽観的ロックですが、
1:すべてのレコードにすれば、そのテーブルを開いた時点でテーブルロックが取得されますし、
2:編集済みのレコードになれば、編集開始時点で排他的ロックが取得されます。
https://youtu.be/_bstegbUBPA?t=468

ADO のトランザクションの場合、ACID特性の分離レベル(isolaction)の影響もあるのですが、
私は対 Access での動作を検証したことが無いです。
https://techracho.bpsinc.jp/kotetsu75/2018_12_14/66410

一方 DAO には、ADO の IsolationLevel プロパティにあたる仕組みは無かったはず。
(ODBCDirect ワークスペース用の IsolateODBCTTrans ってのがある程度)


> MS 公式の文章からは、私では到底予想できませんでした……。
Micorost 公式の MDAC SDK 関連サイトも軒並み無くなっていますしね。
紙媒体の古い資料だと、DAO だと ISBN:9784756121899 、ADO だと 9784798101309 で
読んだ記憶があるんですが、今どちらも手元になくて確認できません。
引用返信 編集キー/
■102645 / inTopicNo.5) 　Re[4]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ eb (8回)-(2023/11/29(Wed) 19:35:28)
魔法の仮面弁士様、たびたび申し訳ありません。本当にありがとうございます。
お手数申し訳無いので自己完結しようと思ったのですが、せっかくですので詳しく書きます。

目的は、MS のこの解説の意味を具体的な挙動として把握することでした。

ページ レベル ロックとレコード レベル ロック | Microsoft Learn
https://learn.microsoft.com/ja-jp/previous-versions/office-development/cc376645(v=msdn.10)
>レコード レベル ロックをオンにしても、メモ フィールドやインデックス フィールドの値の更新には適用されないため、これらの操作を行う場合は、ページ レベル ロックに設定してください。

「テストの 2 本」は、正確には、テストを 1 本実行して理解した気になって、
踏まえて次に実務的なものの製作に手を着けたら予想外の挙動があって頓挫した、というのが経緯です。
そのあたりをリセットして、余計なところを全て削ぎ落としてテスト用のデータとコードはこれです。

■バックエンド be.accdb
table1 (ID, NonIdxedNum, IdxedNum) -- 適当なデータ 2 行

■フロントエンド fe1.accdb, fe2.accdb
Sub UpdateIdxedNum()

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\…\be.accdb"

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "table1", cnn, adOpenKeyset, adLockPessimistic

Debug.Assert rst.LockType = adLockPessimistic ' 有効に設定されたかチェック

cnn.BeginTrans
rst.MoveNext ' fe1 ではコメントアウト
rst.Fields("IdxedNum") = Int(Rnd * 10) ' ロック開始
rst.Update ' fe1 ブレークポイント①
cnn.CommitTrans ' fe1 ブレークポイント②

MsgBox "Done!"

rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing

End Sub

手順としては、
fe1 にだけブレークポイント①を置いて fe1 を実行 → fe2 を実行すると、
fe2 は正常終了。行ロックだと解されます。
fe1 にだけブレークポイント②を置いて fe1 を実行 → fe2 を実行すると、
fe2 は rst.update の行で「他でロックされている」とエラー発生。ページロックだと解されます。
……というものなのですが、合っていそうでしょうか。

「私は対 Access での動作を検証したことが無い」が、
Access をお持ちでないというお話であれば検証しかねるかもしれませんが、
どうやらこういう仕様である（？）と、現在私は考えざるを得ないです。
もしかして、当たり前だったりするのかもしれませんが……。



ところで、豊富な情報ありがとうございます。

>紙媒体の古い資料だと、DAO だと ISBN:9784756121899 、ADO だと 9784798101309 で

どちらも面白そうです！　上のようなことまで書かれていそうです。
中古を買ってみようかと調べたら、どちらも高いし厚い！
メモしましたが、Access・ADO の将来性の問題もあるし悩ましいですね（苦笑）。
引用返信 編集キー/
■102667 / inTopicNo.6) 　Re[5]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ eb (9回)-(2023/12/02(Sat) 04:37:28)
JET/ACE に興味のあるエンジニアは少数派でしょうし、
Excel から ADO で繋いでる EUC 営業マンや事務員も排他制御まで立ち入らないですよね。

でも自分的にははっきりさせられてよかったです。
魔界の仮面弁士様、ありがとうございました。
解決済み
引用返信 編集キー/
■102670 / inTopicNo.7) 　Re[6]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ 魔界の仮面弁士 (3731回)-(2023/12/04(Mon) 10:35:50)
■No102645 (eb さん) に返信
> ■バックエンド be.accdb
> table1 (ID, NonIdxedNum, IdxedNum) -- 適当なデータ 2 行

とりあえず、こんな感じで良いですか?

CREATE TABLE [table1]
( [ID] COUNTER(1000, 1)
, [NonIdxedNum] INTEGER
, [IdxedNum] INTEGER
, CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);
CREATE INDEX [Index1] ON [table1] ([ID]) WITH IGNORE NULL;

INSERT INTO [table1] ([ID], [NonIdxedNum], [IdxedNum]) VALUES (100, -100, 123456);
INSERT INTO [table1] ([ID], [NonIdxedNum], [IdxedNum]) VALUES (110, +110, 789012);


2001 年の夏に、VB初心者友の会の Q&A 掲示板に回答したときのメモからの自己抜粋ですが
DAO の場合、Jetエンジンが返すレコードロックに関するエラーとして主な物は、
>>　3197: 共有的ロックで発生します。具体的には、編集中に他ユーザが同一レコードを保存し、
>>　　　　その後で自分が保存しようとした場合に発生します。
>>　3218, 3202: 他ユーザーが排他的ロックをしている時に、レコードを編集しようとしたか、
>>　　　　共有ロック中のレコードが他ユーザーに排他ロックされ、その後で自分が保存しようと
>>　　　　した場合に発生します。（ページロックの場合は、 3260 か 3186 が返されます）
などがあります。OLE DB の場合のエラーコードは未調査。



> Set cnn = New ADODB.Connection
> cnn.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\…\be.accdb"

Access 2019 VBA をお使いで、かつ、ACEDAO ではなく、ADODB 限定の話をされていた、という認識で良いですか?
それも、CodeProject.Connection や CurrentProject.BaseConnectionString ではなく、
自データベースとは別の accdb を開くパターンである、と。


> ページ レベル ロックとレコード レベル ロック
それらのロックモードの動作の違いを調べていたのですよね?
質問内容と検証内容が合致していないような…。


Microsoft Access (2000 以上)で .mdb / .accdb を開く場合、オプション設定の
「レコードレベルでロックして開く」が on か off のいずれであるかは確認済みですか?

ここの掲示板の No18719 のスレッド(の No18739 )の解説が言葉足らずだったので補足すると、
>> VBA でいえば、"Use Row Level Locking" のオプション設定(既定値:False)です。

これは要するに、
　Application.SetOption "Use Row Level Locking", False
　Application.SetOption "Use Row Level Locking", True
のことです。
これは、Access のオプション画面の [クライアントの設定]-[詳細設定]にある
「レコードレベルでロックして開く」の項を切り替える物です。
最初にデータベースを開いたユーザーがこの設定をオフにしている場合、
同じデータベースを後から開いたユーザーはページ レベル ロックを使用することになります。


一方、Access を使わず、OLE DB Provider で開く場合には、
この掲示板の No18719 のスレッドにも少し書いたことがありますが、
データベースを "Jet OLEDB:Database Locking Mode=1" 指定で最初に開いた上で、
テーブルを "Jet OLEDB:Locking Granularity=2" 指定で開く必要があるはず。
※後者の既定値は 2 なので、重要なのは前者かな。


'DBPROP_JETOLEDB_DATABASELOCKMODE
'"Jet OLEDB:Database Locking Mode"
'　型: adInteger
'　既定値: 0
'　属性: adPropRead, adPropWrite, adPropRequired
'
'　データベースをロックするときに使用される手段。データベースは、一度に 1 つのモードでのみ開けます。
'　データベースを開く 最初のユーザーが、データベースを開いている間に使用されるロック モード の決定権を持ちます。
'
'　有効な値の一覧については、MDAC SDK の「付録 C: Microsoft Jet 4.0 Provide が定義するプロパティ値」を参照してください。
'
Const DBPROPVAL_DL_OLDMODE As Long = 0　'Old mode used in previous releases of Jet's storage engine
Const DBPROPVAL_DL_ALCATRAZ As Long = 1　'Alcatraz mode. Enables locking mode that allows for row-level locking. This does not preclude page locking.
'
'A database can be open in only one mode at a time. The first user to open the database determines the locking mode used while the database is open.
Debug.Print rst.ActiveConnection.Properties("Jet OLEDB:Database Locking Mode").Value
'
'
'DBPROP_JETOLEDB_LOCKGRANULARITY
'"Jet OLEDB:Locking Granularity"
'　型: adInteger
'　既定値: 2
'　属性: adPropRead, adPropWrite, adPropRequired
'
'　テーブルが Alcatraz 行レベル ロックを使用して開かれるか どうかを決定します。このプロパティは、
'　DBPROP_JETOLEDB_DATABASELOCKMODE が DBPROPVAL_DL_ALCATRAZ に設定されていない場合は無視されます。
Const DBPROPVAL_LG_PAGE As Long = 1 'Use page locking for this result set for concurrency control.
Const DBPROPVAL_LG_ALCATRAZ As Long = 2 'Use row-level locking for concurrency control.
'
'Determines whether a table is opened using Alcatraz row-level locking. This property is ignored unless DBPROP_JETOLEDB_DATABASELOCKMODE is set to DBPROPVAL_DL_ALCATRAZ.
Debug.Print rst.Properties("Jet OLEDB:Locking Granularity").Value



■No102667 (eb さん) に返信
> JET/ACE に興味のあるエンジニアは少数派でしょうし、
> Excel から ADO で繋いでる EUC 営業マンや事務員も排他制御まで立ち入らないですよね。

Office 2000 Developer CD-ROM をお持ちであれば、ODETools\V9\Samples\OPG\Appendixes フォルダに含まれる
ADOProperties.doc も参照してみてください。

その他、参考になりそうな資料を置いておきます。
多くのサイトが既に閉鎖しているため、Internet Archive からのサルベージも含んでいますが。


日本語【アーカイブ】[MDAC] - [DAO から ADO への移植] - [Jet Provider と組み合わせた ADO の使用 by Alyssa Henry] 全11ページ
https://web.archive.org/web/20030708005542/http://microsoft.com/japan/msdn/data/techmat/ado/dao2ado.asp
https://web.archive.org/web/20030708005542/http://microsoft.com/japan/msdn/data/techmat/ado/dao2ado_11.asp

日本語 [Microsoft OLE DB Provider for Microsoft Jet]
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/microsoft-ole-db-provider-for-microsoft-jet

英語 [Microsoft OLE DB] - [OLE DB Providers Overview] - [Jet Provider]
https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms722791%28v%3dvs.85%29

英語 [Access World Forums] - [元スレッド不明] - [whitepaper1of2.doc および whitepaper2of2.doc]
https://www.access-programmers.co.uk/forums/attachments/whitepaper1of2-zip.384/
https://www.access-programmers.co.uk/forums/attachments/whitepaper2of2-zip.385/
解決済み
引用返信 編集キー/
■102674 / inTopicNo.8) 　Re[7]: JET/ACE ロックとインデックス フィールドの値の更新
▲▼■
□投稿者/ eb (10回)-(2023/12/06(Wed) 05:17:35)
2023/12/06(Wed) 05:23:22 編集(投稿者)
2023/12/06(Wed) 05:23:15 編集(投稿者)

まだレスを付けていただけてるとは思わず、お礼遅れました。

CREATE TABLE はそういう感じです。

> Access 2019 VBA をお使いで、かつ、ACEDAO ではなく、ADODB 限定の話をされていた、という認識で良いですか?
> それも、CodeProject.Connection や CurrentProject.BaseConnectionString ではなく、
> 自データベースとは別の accdb を開くパターンである、と。
>
>>ページ レベル ロックとレコード レベル ロック
> それらのロックモードの動作の違いを調べていたのですよね?
> 質問内容と検証内容が合致していないような…。

これについてはテストを網羅すべきなのでしょうが、以下の文言を捕捉できればどれでもありでした。
（主キーは別として）インデックス付きフィールドの更新って珍しくないのに、行ロックできない？？？
という疑問を解消したかったんですね。

>ページ レベル ロックとレコード レベル ロック | Microsoft Learn
>https://learn.microsoft.com/ja-jp/previous-versions/office-development/cc376645(v=msdn.10)
>>レコード レベル ロックをオンにしても、メモ フィールドやインデックス フィールドの値の更新には適用されないため、これらの操作を行う場合は、ページ レベル ロックに設定してください。

以下、情報ありがとうございます。
私は「アルカトラズ」は技術者の俗語なのかと思ってたのですが、
公式にそういう言葉が採用されてるんですね。面白いです。
internet archive の記事も、リンクから鈴なりに見られますね。
とても便利です。

改めて、ありがとうございました。
解決済み
引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ