ある方から下のようなコードが「共有ファイルの最大ロック数を超えています」というエラーを返すということで、サンプルをいただきました。見た目には何の支障もなさそうなコードです。
Public Sub test_ADOsyouhin()
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Cn = CurrentProject.Connection
Set Rs = New ADODB.Recordset
Rs.Open "商品", Cn, adOpenKeyset, adLockPessimistic
Rs.MoveFirst
Do While Not Rs.EOF
LoopIf Not IsNull(Rs.Fields(0)) Then
Rs.Fields("番号") = Replace(Rs.Fields("番号"), "1", "2")
Rs.Update
End If
Rs.MoveNext
商品テーブルのレコード数は1万件強、もちろん番号フィールドは主キーを外してあります。
これ、マシンを立ち上げてすぐだと何の問題もなく実行され、「成功！」のメッセージボックスが現れます。
立ち上げてほったらかしにしてアプリもいくつか立ち上げて・・・という状態で実行すると、たちどころに「共有ファイルのロック数を超えています」と怒られます。
で、テーブルを見てみると9500レコードまではReplaceされているけど、そこでデバッグがかかるわけです。
WEBで検索をかけると、「よくわからないけどConnectionオブジェクトのプロパティでJetの Max Locks Per Fileを大きな数に変えるといい、というのが1つ。ADOをJetで使うとロックの最大値は9500です、というのが1つ
はい、そうですか。というわけにもいきません。ConnectionオブジェクトのプロパティでJetの場合は9500が最大値とどこで判るんだろう？？というのが最大の疑問。で、のるかそるかでヘルプに「Jet」と打ってみました。ヒットしました〜。
| Jet OLEDB:Max Locks Per File (DBPROP_JETOLEDB_MAXLOCKSPERFILE) |
データベースに設定できる最大ロック数を示します。既定値は
9500 です。 |
余談ですが、このトピックにRecordsetのオープン時に設定する各プロパティでJetは動的カーソルはサポートしてない、とか組み合わせをかんがえないとだめ、とか苦労して調べ上げて表にまとめたことが全部載ってました。がーん。
というのは置いておいて。
はい、問題のConnectionオブジェクトのプロパティで最大ロック数と9500レコードまでしか更新されない、ということのつながりですが。
上のコードのようにレコードセットを1件ずつひっかけてUpdateする処理の場合、レコードセットにアクセスするたびにRecordsetオブジェクトの管理するロックが発生しています。
このあたりはかなり微妙で、SQLServerではUｐdateをかけた時点で一瞬排他ロックがかかるそうです。JetにADOでアクセスすると、RecordsetをOpenした時点で編集モードに入り、実は上記のメソッドもUpdate文を記述しなくてもレコードの値の変更は行われます。
（でもだからといってUpdate文を省かないでくださいね〜〜。トランザクションを使っておけば、エラー時にCancelできるのがupdate文のいいところなんですから）
当然浮かぶ疑問はUpdateしなければロックはかからないのか？？ということですが、Recordsetにアクセスする→編集して次のレコードに移動する、というプロセスだけで一回のロックがかかっていることに違いはないわけです。どっちにしろ更新（Updateしているので） Updateメソッドは関係がありません。
また、データベースレベルロックを排他でかけてしまえばいいのでは？という疑問も湧きます。
この場合、自分から自分に（他DBじゃないってことですね）アクセスしているのですが、CurrentProject.Connectionを用いるとどうやってもConnectionオブジェクトで制御できるデータベースレベルのロックのタイプは共有ロックです。排他ロックを使いたければ、ConnectionオブジェクトをもうひとつNewして、Connectionのopen時にCurrentProject.BaseConnectionStringを指定します。ただ、排他ロックを指定したところで、上記のエラーには何の影響もありませんでした。 （もちろん、Recordsetのロックのタイプを排他にしても同じ）
Recordsetを1件ずつひっかけて更新の処理をする過程ではフォームでレコードセットを扱うときのように一度データをメモリバッファに格納しておきます。そしてUpdateメソッドによってディスクへ格納されます(理論上）。でもそのたびにディスクアクセスしているわけではないはず（なので9500レコードでひっかからないときもあると思っています）。問題になるのはメモリバッファとディスクとのI/Oかなあ。
ロックが問題ならトランザクションをかけるといい、というコメントも見ましたが、これをトランザクションで処理しようと思うと余計にメモリを食い、エラー回避にはなりませんでした。出来るときと、「共有ロック数〜」のエラーになるときがあるのは、条件として変わりません。Commitするまではメモリバッファにデータを格納しておいて、別ルートでメモリ→ディスクとのI/Oは発生しているのでは？？OldValueをレコード分キャッシュに蓄えておいて、Commitの時点で新しいデータをOldValueに移行させている気配がします。多分、データベースレベルロックをしても、トランザクションをかけても内部的には一件一件のRecordsetを編集する時点でさらに共有ロックがかかるのでしょうね。
というわけでマシン立ち上げ直後に上のステートメントを実行するとJetの最大ロック数もなんのその、するっと実行されるけれど、メモリが食われ始めてから実行するとすぐさまその影響を受ける、というのが真相だと思っています。
これを回避する方法ですがConnectionオブジェクトのMax Locks Per Fileプロパティを設定しなおします。（イミディエイトウィンドウで調べると初期値は設定されていないらしくゼロでした。なのでいつも9500でストップするわけじゃないのね）
例：Connection.Properties("Jet OLEDB:Max Locks Per File") = 15000
もしくはConnectionオブジェクトのExecuteメソッドでダイレクトにSQL文を実行するといいと思います。目的のレコードセットをぼすっ！っとメモリ上に読み出して、そ〜らよっ！といちどきに書き換え、ロックは一度しかかからないので何の問題もありません。トランザクションもかってに掛けてくれちゃってるし、ディスクへのI/Oも理論上は読み込みと書き込みの二度ですむはず。とにかく処理が早い。Executeメソッドがパフォーマンスがいい、というのもうなずける話です。
おまけ
関連項目 OLE DB Provider for Microsoft Jet の実際の使用方法および機能に関する情報については、MDAC SDK の OLE DB Provider for Microsoft Jet のマニュアルを参照してください。
というのがこのヘルプの最後。OLE DB Provider for Microsoft Jetにかかっているリンク、切れています。ADOとJetの詳しい説明はhttp://www.microsoft.com/japan/msdn/data/techmat/ado/dao2ado.aspに掲載されていますのでそちらを参照してください。
2003年10月12日追記
Access2000から、MSDataShape（リレーションシップをとった子テーブルが親テーブルのレコードセレクタの位置に展開できるようになった、あれです）がサポートされ、JetとAccessの間にMSDataShapeプロバイダが入った関係で、レコードセットのOpen時に排他ロックを指定しても共有ロックになるそうです。Access側のヘルプに書いてあるとのことですが、まだ見つけられません。で、本当に共有ロックかどうか調べたら、排他ロックを指定した場合、なんとバッチ更新が返ってきました。うげー。
ちなみに上記のコードはCursorLocationをadUseClientにして、バッチ更新をかけるとすごーく時間をかけた後、めちゃめちゃな結果になって実行されます。もちろん、保留の上限をこえている、というエラー込み。3レコードも4レコードも束にして更新かけちゃうんですねえ。メモリバッファに格納する処理はあまり問題ではなく、やっぱりディスクアクセスみたい・・トランザクションっていったいなんだろう・・