【テクニカル・上級編】DAOのトランザクションで発生する「ロック競合」を解消するタイムアウト制御の設計 – Access VBA解析バイブル

DAOトランザクションにおける「ロック競合」の極限制御：指数バックオフを用いた堅牢な再試行アーキテクチャ

多人数が同時にアクセスするAccess分散データベース環境において、開発者が直面する最大の壁は「ロジックの正しさ」ではない。「タイミングの不一致」だ。

どれほど完璧なSQLを書き、緻密なビジネスロジックを構築したとしても、ACE/Jetエンジンが管理するページロックやレコードロックの衝突（Lock Contention）の前では、プログラムは無力に停止する。`dbDeadlock` や `dbWriteConflict` といったエラーが投げられるとき、それはコードのバグではなく、物理的なリソースの奪い合いが発生しているという「システムからの悲鳴」である。

本稿では、単なるエラーハンドリングを超え、Windows APIを駆使した精密な待機制御と、指数バックオフ（Exponential Backoff）アルゴリズムを組み込んだ、プロフェッショナル向けのトランザクション制御設計について解説する。

1. DAOにおけるロック競合の本質

ADO（ActiveX Data Objects）が外部データベースへの抽象化レイヤーとして機能するのに対し、DAO（Data Access Objects）はAccessのエンジン（ACE/Jet）と密結合している。この密結合こそがDAOの強みであり、同時に多人数環境における弱点となる。

デッドロックのメカニズム

DAOのトランザクションにおいて、デッドロックは主に以下のプロセスで発生する。
1. User A がレコードXを更新しようとして、ページロックを取得する。
2. User B がレコードYを更新しようとするが、レコードYがUser Aの保持するページ内に存在するため、待機状態に入る。
3. User A が次にレコードYを更新しようとする。
4. 結果： 両者が互いの解放を待ち続け、エンジンがタイムアウトを検知してエラーを投げる。

一般的な「エラーが出たら即座にリトライする」という実装は、この状況をさらに悪化させる。リトライのタイミングが重なることで、競合が再発する「サンダリング・ハード（Thundering Herd）」現象を引き起こすからだ。

2. 堅牢な設計思想：指数バックオフとジッター

競合を解消するための正解は、「衝突した直後に、適切な間隔を置いて、少しずつ待機時間を変えながら再試行すること」である。

ここで採用すべきは指数バックオフである。失敗回数が増えるごとに待機時間を指数関数的に増加させることで、競合しているプロセスがリソースを解放するための「時間的猶予」をシステムに与える。さらに、待機時間に微小な乱数（Jitter）を加えることで、複数のクライアントが全く同じタイミングでリトライを開始するのを防ぐ。

3. 実装：高信頼性トランザクション・ラッパー

以下に、Windows APIを用いてミリ秒単位の精密な待機を実現し、指数バックオフを組み込んだトランザクション制御モジュールの設計例を示す。

Option Compare Database
Option Explicit

' Windows API: プロセスの実行を一時停止させる
' VBAの標準的なDoEventsやWaitよりも精度が高く、CPU負荷を最小限に抑えられる
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

''' <summary>
''' トランザクション実行用定数
''' </summary>
Private Const MAX_RETRIES As Integer = 5          ' 最大リトライ回数
Private Const INITIAL_BACKOFF_MS As Long = 100    ' 初回の待機時間(ms)
Private Const MAX_BACKOFF_MS As Long = 2000       ' 最大待機時間(ms)

''' <summary>
''' 堅牢なトランザクション実行エンジン
''' </summary>
''' <param name="ExecuteLogic">実行したい一連の処理（引数として渡す必要がある場合は、クラス化して渡す設計を推奨）</param>
Public Function ExecuteWithRetry(ByRef db As DAO.Database) As Boolean
    Dim retryCount As Integer
    Dim waitTime As Long
    Dim success As Boolean
    Dim errNum As Long
    
    retryCount = 0
    success = False
    
    On Error GoTo ErrorHandler

    Do
        On Error GoTo InnerErrorHandler
        
        ' --- トランザクション開始 ---
        db.BeginTrans
        
        ' ---------------------------------------------------------
        ' ここに実際のビジネスロジックを記述する
        ' 本来はコールバック関数やインターフェースを介して呼び出すのが理想的
        Call PerformBusinessLogic(db)
        ' ---------------------------------------------------------
        
        ' コミットが成功すればループを抜ける
        db.CommitTrans
        success = True
        Exit Do

InnerErrorHandler:
        errNum = Err.Number
        ' ロック競合に関連するエラー番号を特定
        ' 3260: dbWriteConflict, 3211: dbDeadlock 等
        If errNum = 3260 Or errNum = 3211 Or errNum = 3161 Then
            ' ロック競合が発生した場合はロールバックしてリトライへ
            db.Rollback
            
            retryCount = retryCount + 1
            If retryCount > MAX_RETRIES Then
                Debug.Print "Max retries exceeded. Aborting."
                Exit Do
            End If
            
            ' 指数バックオフ計算: initial * 2^(retry-1) + jitter
            waitTime = CalculateBackoff(retryCount)
            Debug.Print "Lock contention detected (Error " & errNum & "). Retry " & retryCount & " in " & waitTime & "ms."
            
            ' Windows APIによる精密な待機
            Sleep waitTime
        Else
            ' ロック競合以外の致命的なエラーは即時ロールバックして終了
            db.Rollback
            Err.Raise errNum, "ExecuteWithRetry", Err.Description
        End If
    Loop

    ExecuteWithRetry = success
    Exit Function

ErrorHandler:
    ' 致命的なエラー処理
    Debug.Print "Fatal Error: " & Err.Description
    ExecuteWithRetry = False
End Function

''' <summary>
''' 指数バックオフとジッターの計算
''' </summary>
Private Function CalculateBackoff(ByVal retry As Integer) As Long
    Dim baseWait As Long
    Dim jitter As Long
    
    ' 指数関数的な増加 (100, 200, 400, 800...)
    baseWait = INITIAL_BACKOFF_MS * (2 ^ (retry - 1))
    
    ' 最大値を制限
    If baseWait > MAX_BACKOFF_MS Then baseWait = MAX_BACKOFF_MS
    
    ' ジッター（0〜50msの乱数）を加えて衝突を回避
    jitter = Int((50 * Rnd) + 1)
    
    CalculateBackoff = baseWait + jitter
End Function

''' <summary>
''' 実際の業務ロジック（例）
''' </summary>
Private Sub PerformBusinessLogic(ByRef db As DAO.Database)
    Dim rs As DAO.Recordset
    ' 実際にはここで複雑な更新処理を行う
    Set rs = db.OpenRecordset("SELECT * FROM T_Inventory WHERE ItemID = 1", dbOpenDynaset)
    
    If Not rs.EOF Then
        rs.Edit
        rs!Stock = rs!Stock - 1
        rs.Update
    End If
    
    ' 重要：オブジェクトの明示的解放
    ' これを怠ると、オブジェクトのライフサイクルが終了するまでロックが保持され続ける
    rs.Close
    Set rs = Nothing
End Sub

4. アーキテクトの視点：オブジェクトのライフサイクル管理

コード例の `PerformBusinessLogic` 内に、極めて重要な記述がある。
`Set rs = Nothing` によるオブジェクトの明示的な解放だ。

VBAのガベージコレクション（参照カウント方式）は、スコープを抜ければ自動的にオブジェクトを破棄するが、トランザクション内においてはその「自動性」が仇となる。
`Recordset` や `Database` オブジェクトがメモリ上に残り続ける限り、そのオブジェクトが掴んでいるハンドルやページロックは解放されない。

「エラーが起きたからロールバックした。これで安心だ」と考えてはならない。ロールバック後も、ローカル変数に参照が残っていれば、それは「幽霊ロック」として他のユーザーを阻害し続ける。
「トランザクションが終了したら、その中で生成したすべてのDAOオブジェクトを即座に、明示的に破棄する」。これは、大規模なレガシーシステムを保守する上で、鉄則中の鉄則である。

5. 結論

Access VBAにおける高可用なシステム設計とは、単に正しい計算を行うことではない。
非同期に、かつ不規則に発生するリソースの奪い合いを、いかに「予測可能な挙動」へと制御するかにある。

1. Windows API (`Sleep`) を用い、CPUを浪費しない精密な待機を行う。
2. 指数バックオフとジッター を導入し、リトライの衝突を回避する。
3. オブジェクトのライフサイクル を完全に掌握し、ロックの残留を防ぐ。

これらの知見を実装に落とし込むことで、あなたのシステムは、単なる「動くツール」から、過酷な業務環境に耐えうる「堅牢な基盤」へと昇華する。