【テクニカル・上級編】実務中級者向け:レコードセットのロック制御(dbPessimistic vs dbOptimistic)の実践 – Access VBA解析バイブル

スポンサーリンク
スポンサーリンク

排他制御の真髄:Access VBAにおけるロック戦略の設計思想

マルチユーザー環境におけるデータベース設計において、最も残酷なのは「ロジックのバグ」ではない。「競合(Conflict)」だ。

複数のユーザーが同時に同じレコードへ手を伸ばしたとき、システムがどう振る舞うか。これを設計段階で定義できていないエンジニアが書くコードは、どれほど美しくとも、運用フェーズで必ず「データの不整合」という名の爆弾を抱えることになる。

本稿では、Access VBA(DAO)におけるレコードセットのロック制御、すなわち`dbPessimistic`(悲観的ロック)と`dbOptimistic`(楽観的ロック)の使い分けについて、その設計思想と実装の極致を解説する。

1. 競合の正体:TOCTOU問題とデータの整合性

我々が対峙しているのは、TOCTOU (Time-of-Check to Time-of-Use) という概念的な課題である。

1. ユーザーAがレコードを読み込む(Check)
2. ユーザーBが同じレコードを書き換える(Change)
3. ユーザーAが読み込んだ古い情報に基づき、更新をかける(Use)

この隙間に、データの「上書き(Lost Update)」が発生する。この隙間をどう埋めるか。そのアプローチが、悲観的ロックと楽観的ロックの分岐点となる。

2. 悲観的ロック(dbPessimistic):堅牢なる要塞

`dbPessimistic` は、レコードを読み込んだ瞬間に「このレコードは私が使う。他者は触れるな」と宣言し、物理的なロックをかける戦略だ。

特徴

  • 整合性: 極めて高い。読み込みから更新完了まで、他者の介入を完全に遮断する。
  • コスト: 高い。ロックを保持している間、他のユーザーは待機(またはエラー)を強いられ、スループットは著しく低下する。
  • リスク: デッドロック、および「プロセスが異常終了した際にロックが残存する」リスク。

設計の指針

金融取引、在庫の引き当て、あるいは極めてクリティカルなマスター更新など、「一瞬の不整合も許されない」領域にのみ適用せよ。

' --- 悲観的ロックの実装例 ---
Public Sub UpdateCriticalData_Pessimistic(ByVal targetID As Long, ByVal newValue As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    
    On Error GoTo ErrorHandler
    
    ' dbPessimisticを指定し、読み込みと同時にレコードをロックする
    ' 注意: ネットワーク遅延がある環境では、ロック保持時間が長くなりUXを損なう
    Set rs = db.OpenRecordset("SELECT * FROM T_CriticalInventory WHERE ID = " & targetID, _
                               dbOpenDynaset, dbPessimistic)
    
    If Not rs.EOF Then
        rs.Edit
        rs.Fields("Status").Value = newValue
        rs.Update ' ここでロックが解放される
        Debug.Print "Update Successful."
    End If

CleanUp:
    ' オブジェクトの明示的な解放は、メモリリークと予期せぬロック残存を防ぐための「儀式」である
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

ErrorHandler:
    ' 悲観的ロック下では、他者が既にロックしている場合にエラーが発生する
    MsgBox "データは現在他のユーザーによって使用中です。時間を置いて再試行してください。", vbCritical, "排他エラー"
    Resume CleanUp
End Sub

3. 楽観的ロック(dbOptimistic):信頼と検証のパラドックス

現代的なWebアプリケーションや、高頻度なアクセスが発生するシステムで主流となるのが `dbOptimistic` である。これは、更新の瞬間までロックをかけない。代わりに、「更新を実行する直前に、自分が読み込んだ時とデータが変わっていないか」をエンジンに検証させる。

特徴

  • 整合性: 衝突が発生した際にエラーとして検知する。
  • コスト: 低い。ロック保持時間が極めて短いため、同時実行性が高い。
  • リスク: 「書き込み競合(Write Conflict)」が発生した際、ユーザーにどう「再試行」させるかという、高度なUX設計が求められる。

設計の指針

一般的な業務アプリケーション、マスタの緩やかな更新、ログ記録など、「衝突の確率は低いが、衝突した際のリカバリ手順が確立できている」領域に適用せよ。

' --- 楽観的ロックの実装例 ---
Public Sub UpdateNormalData_Optimistic(ByVal targetID As Long, ByVal newValue As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    
    On Error GoTo ErrorHandler
    
    ' dbOptimisticを指定。読み込み時はロックしない
    Set rs = db.OpenRecordset("SELECT * FROM T_GeneralData WHERE ID = " & targetID, _
                               dbOpenDynaset, dbOptimistic)
    
    If Not rs.EOF Then
        rs.Edit
        rs.Fields("Description").Value = newValue
        
        ' rs.Updateの瞬間に、DAOエンジンが「読み込み時と現在の値」を比較する
        ' もし他者が変更していれば、ここでエラーが発生する
        rs.Update
        Debug.Print "Update Successful."
    End If

CleanUp:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Sub

ErrorHandler:
    ' DAO Error 3165 または 3260 は書き込み競合を示唆する
    ' ここでの設計が「シニア」と「ジュニア」の分かれ目である
    Select Case Err.Number
        Case 3165, 3260
            ' 競合発生時の戦略: ユーザーに選択肢を与える(再読み込みして手動更新 vs キャンセル)
            Dim response As VbMsgBoxResult
            response = MsgBox("他のユーザーがこのレコードを更新しました。最新の状態を読み込み直しますか?", _
                              vbYesNo + vbExclamation, "書き込み競合")
            If response = vbYes Then
                ' 再試行ロジックへ(再帰呼び出しやループ構造が必要)
                ' 本来は現在の値を保持したまま、再取得を行う設計が望ましい
                Debug.Print "Retry logic triggered."
            End If
        Case Else
            MsgBox "予期せぬエラー: " & Err.Description, vbCritical
    End Select
    Resume CleanUp
End Sub

4. アーキテクトの極意:メモリ管理とライフサイクル

プロフェッショナルとして、コードの動作だけでなく、その背後で動くWindowsリソースへの敬意を忘れてはならない。

オブジェクトの明示的破棄

VBAのガベージコレクションは、C#やJavaのように洗練されてはいない。`Set rs = Nothing` を怠ることは、メモリの断片化を招くだけでなく、DAOエンジンが「まだレコードセットが生きている」と判断し、不必要なロックを保持し続ける原因となる。特にループ内でRecordsetを生成する場合、これは致命的なパフォーマンス低下を招く。

Windows APIとの連携(高度な待機処理)

もし、悲観的ロックによる競合を「エラー」としてではなく、「リトライ」で解決したい場合、単なる `DoEvents` だけでは不十分だ。CPU負荷を抑えつつ、ミリ秒単位の精密な待機を行うには、Windows APIの `Sleep` を活用すべきである。

' Windows APIの宣言

If VBA7 Then

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Else

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

End If

' リトライ処理の概念 ' 1. エラー検知 ' 2. Sleep 500 (500ms待機) ' 3. 最大回数リトライ ' 4. 限界を超えたらユーザーに通知

5. 結論:どちらを選ぶべきか

設計判断の基準を、以下のマトリックスで整理しておけ。

評価項目 悲観的ロック (`dbPessimistic`) 楽観的ロック (`dbOptimistic`)
主な目的 データの絶対的整合性の確保 システムのスループット最大化
競合時の挙動 発生を未然に防ぐ(待機させる) 発生後に検知する(エラーを出す)
ユーザー体験 「操作が重い」「固まった」と感じる 「さっき書き換えたのに!」と憤る
実装難易度 低い(エンジン任せ) 高い(エラーハンドリングが肝)
推奨用途 在庫、残高、重要設定 顧客情報、履歴、一般業務データ

エンジニアの仕事は、コードを書くことではない。「起こりうる不整合に対して、いかにエレガントに、かつ最小限のコストで対処するか」という戦略を実装することである。

君のシステムに、どのような「信頼」を組み込むのか。その答えは、このロック戦略の選択に集約されている。

タイトルとURLをコピーしました