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

複数人利用の壁を突破せよ：DAOトランザクションにおける「ロック競合」を制御する極限の設計術

こんにちは。開発の現場へようこそ。

マクロの記録から一歩踏み出し、VBAでコードを書き始めたあなた。変数やループ、条件分岐をマスターして、「自分でもシステムが作れる！」という手応えを感じ始めている頃ではないでしょうか。

しかし、ここからが本当の「エンジニア」への登竜門です。
一人のPCで動かしている時は完璧だったプログラムが、「他のユーザーが同時に使った瞬間にエラーで止まる」。そんな経験をしたことはありませんか？

今回は、Access開発における最大の難所の一つである「多人数同時接続環境でのロック競合（デッドロック）」に焦点を当てます。これをスマートに解決する「リトライ（再試行）ロジック」の実装方法を、設計思想から丁寧に解説していきましょう。

ここをクリアすれば、あなたの作るシステムは「ただ動くもの」から「現場で信頼される道具」へと進化します。

1. なぜ「ロック」は発生し、エラーを引き起こすのか？

まず、敵の正体を知ることから始めましょう。

Accessで複数の人が同じテーブルを更新しようとすると、Access（ACE/Jetエンジン）はデータの整合性を守るために、特定のレコードやページに「ロック（鍵）」をかけます。

ロックのイメージ図

例えば、ある「在庫テーブル」の「商品A」の在庫数を更新しようとしている場面を想像してください。

1. ユーザーAが「商品A」の更新を開始（トランザクション開始）。
2. Accessが「商品A」にロック（鍵）をかける。
3. ユーザーBが「商品A」を更新しようとする。
4. ユーザーBは「鍵がかかっているから待とう」となる。
5. ユーザーAの処理が長時間終わらない、あるいは何らかのエラーで「鍵」を返し忘れる。
6. ユーザーBは待ちきれずに「エラー：レコードはロックされています」と叫んで止まってしまう。

これがロック競合です。特に、複数の処理をひとまとめにする「トランザクション（Transaction）」を使っている場合、このロックの範囲が広くなり、衝突のリスクは飛躍的に高まります。

2. DAOとADO、アーキテクチャの決定的な違い

ここで少し、知識の深みを増しておきましょう。Access VBAでデータを操作する手段には、主にDAOとADOの2種類があります。

Accessをメインのデータベースとして使う場合、DAOを使うのが「王道」です。DAOはAccessのエンジンと密接に連携しているため、トランザクションの制御（「ここからここまでを一つの処理として実行せよ」という命令）が非常にスムーズに行えます。

しかし、王道だからこそ、設計を誤ると強力なロックが原因でシステム全体を停止させてしまう諸刃の剣となるのです。

3. 解決策：リトライ・ロジック（再試行）の設計

「ロックされたらエラーを出して終了」というのは、初心者レベルの設計です。
プロのエンジニアはこう考えます。

「ロックされているなら、数ミリ秒待ってから、もう一度だけ試してみよう。それでもダメなら、諦めてユーザーに優しく通知しよう」

これがリトライ・ロジックです。

実装のアルゴリズム

1. ループを開始する（最大試行回数を決めておく）。
2. トランザクションを開始する。
3. 更新処理を行う。
4. 成功したら、コミット（確定）してループを抜ける。
5. もし「ロックエラー」が発生したら：

• ロールバック（取り消し）をして、鍵を一旦開放する。
• 少し待機（Sleep）する。
• 次のループ（再試行）へ。

6. 最大回数を超えても失敗したら、エラーとして終了する。

4. 【実践】堅牢なトランザクション実装コード

それでは、実際に現場で使えるレベルのコードを見ていきましょう。
このコードは、Windows APIを使用して「精密な待機」を行い、エラーをスマートにハンドリングします。

Option Compare Database
Option Explicit

' 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

''' <summary>
''' 在庫を更新する堅牢なトランザクション処理の例
''' </summary>
''' <param name="productID">更新する商品のID</param>
''' <param name="changeAmount">増減させる数量</param>
Public Sub SafeUpdateStock(ByVal productID As Long, ByVal changeAmount As Integer)
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim rs As DAO.Recordset
    
    Dim retryCount As Integer
    Const MAX_RETRIES As Integer = 5 ' 最大再試行回数
    Dim isSuccess As Boolean
    
    Set db = CurrentDb
    Set ws = DBEngine.Workspaces(0) ' Accessのメインワークスペース
    isSuccess = False
    
    ' --- リトライループ開始 ---
    For retryCount = 1 To MAX_RETRIES
        On Error GoTo ErrorHandler
        
        ' 1. トランザクション開始
        ws.BeginTrans
        
        ' 2. レコードの取得（更新対象をロックするために開く）
        Set rs = db.OpenRecordset("SELECT 在庫数 FROM T_在庫 WHERE 商品ID = " & productID, dbOpenDynaset)
        
        If Not rs.EOF Then
            ' 3. 更新処理
            rs.Edit
            rs!在庫数 = rs!在庫数 + changeAmount
            rs.Update
            
            ' 4. コミット（すべて成功したら確定）
            ws.CommitTrans
            isSuccess = True
            Debug.Print "成功: " & retryCount & "回目の試行で完了しました。"
            Exit For ' 成功したのでループを抜ける
        Else
            ' 対象レコードがない場合
            ws.Rollback
            MsgBox "対象の商品が見つかりません。", vbExclamation
            Exit Sub
        End If

ContinueRetry:
        ' エラーが発生した場合の処理（ErrorHandlerからここへ飛ぶ）
        ' ロックエラー（3260: 書き込みロック、3197: 読み取りロックなど）を判定
        If Err.Number = 3260 Or Err.Number = 3197 Or Err.Number = 3021 Then
            Debug.Print "ロック競合発生。リトライ中... (" & retryCount & "/" & MAX_RETRIES & ")"
            
            ' 重要：一度ロールバックして、掴んでいるロックを確実に解放する
            ws.Rollback
            
            ' 少し待機（500ミリ秒 = 0.5秒）して、相手の処理が終わるのを待つ
            Sleep 500
            ' 次のループへ
            Resume ContinueRetry
        Else
            ' ロック以外の致命的なエラー（構文エラーや型違いなど）は即座に中断
            Err.Raise Err.Number, Err.Source, Err.Description
        End If

    Next retryCount
    ' --- リトライループ終了 ---

    If Not isSuccess Then
        MsgBox "混雑しているため、処理を完了できませんでした。しばらく時間を置いて再度お試しください。", vbCritical, "タイムアウト"
    End If

CleanUp:
    ' 後片付け（これを行わないとメモリリークや予期せぬロックの原因になります）
    On Error Resume Next
    If Not rs Is Nothing Then rs.Close: Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    ' 致命的なエラーが発生した場合
    MsgBox "予期せぬエラーが発生しました: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

コードの「極限のポイント」解説

1. `ws.Rollback` の重要性:
エラーが起きた際、すぐに `Rollback` を呼んでください。これを忘れると、あなたが「エラーで止まった」と思っている間も、Accessは「まだ作業中かな？」と判断してロックを保持し続けてしまいます。これがデッドロックの温床です。
2. `Sleep` API の活用:
`DoEvents` だけで待機するのは不十分です。`Sleep` を使うことで、CPUに「今は休んでいいよ」と伝え、他のユーザーの処理が進むための「隙間」を作ってあげることができます。
3. エラー番号の特定:
すべてのエラーでリトライするのではなく、`3260`（書き込みロック）などの「リトライすれば解決する可能性があるエラー」だけを狙い撃ちしています。これにより、プログラムのバグ（構文エラーなど）を無限ループに陥らせることを防いでいます。

5. まとめ：エンジニアとしての視点

「動けばいい」という段階から、「止まらないシステムを作る」という段階へ。

今回学んだリトライ・ロジックは、単なるテクニックではありません。「システムを利用するユーザーの体験（UX）を、エラーメッセージで台無しにしないための配慮」です。

• DAO を使い、Accessの力を最大限に引き出す。
• トランザクション でデータの整合性を守る。
• リトライ・ロジック で、一時的な衝突をスマートに回避する。

この3拍子が揃ったとき、あなたのAccessアプリケーションは、プロの現場でも通用する「堅牢なシステム」へと昇華します。

一歩ずつ、着実に。コードの背後にある「動き」を感じながら、実装を進めていきましょう。応援していますよ！