SQLite WALモード活用術：クラッシュに強いDB構築とSQLITE_BUSY対策

SQLiteのWrite-Ahead Logging（WAL）モードは、従来のジャーナルモードと比較して、並行性と堅牢性を大幅に向上させるための仕組みです。WALモードでは、データベースの変更は直接データベースファイルに書き込まれるのではなく、まず「WALファイル」という別のファイルに追記されます。これにより、クラッシュリカバリのプロセスがより効率的かつ安全になります。

WALモードでのクラッシュリカバリの仕組みは以下の通りです。

1. • INSERT、UPDATE、DELETEなどのデータベース操作が行われると、変更内容はまずWALファイルに追記されます。この時点では、メインのデータベースファイル（.dbファイル）はまだ変更されていません。
   • 各トランザクションはWALファイル内で一意の「フレーム」として記録されます。

2. チェックポイント処理 (Checkpointing)

   • WALファイルが一定のサイズに達したり、特定の条件が満たされたりすると、「チェックポイント処理」が実行されます。
   • チェックポイント処理では、WALファイルに追記された変更内容が、メインのデータベースファイルに適用（同期）されます。
   • このプロセスはバックグラウンドで行われることが多く、通常のデータベース操作をブロックしません。

3. クラッシュ発生時のリカバリ

   • もし、何らかの理由でシステムがクラッシュした場合（電源断、OSのフリーズなど）、データベースファイルは整合性の取れた状態を保っています。なぜなら、未コミットの変更はメインファイルには書き込まれていないからです。
   • データベースが次に開かれたとき、SQLiteはWALファイルとメインのデータベースファイルの状態を自動的にチェックします。
   • ロールフォワード (Roll Forward)
     WALファイル内に未チェックポイントのコミット済みトランザクションの変更が残っている場合、SQLiteはそれらの変更をWALファイルから読み取り、メインのデータベースファイルに適用します。これにより、クラッシュ発生までにコミットされたすべてのデータが回復されます。
   • 部分的に書き込まれたページの問題
     WALモードでは、メインのデータベースファイルに部分的に書き込まれたページ（断片化されたデータ）が発生するリスクが非常に低くなります。これは、変更がまずWALファイルにアトミックに追記され、その後チェックポイント処理で一貫した状態でメインファイルに適用されるためです。

WALモードのクラッシュリカバリの利点

• 高速なリカバリ
  WALファイルは通常小さく、リカバリプロセスは非常に高速です。
• 並行性の向上
  リーダー（読み取り操作）はWALファイルがチェックポイントされるのを待つ必要がないため、ライター（書き込み操作）が実行中でも読み取りが可能です。
• 高い堅牢性
  データベースファイルが破損するリスクが大幅に低減されます。
• データ損失の最小化
  クラッシュ発生時でも、コミットされたデータは確実に回復されます。未コミットのデータは失われますが、これは意図された動作です。

• WALモードは、デフォルトでは有効になっていないため、PRAGMA journal_mode=WAL; コマンドで明示的に有効にする必要があります。
• WALモードを使用すると、データベースファイル（.db）、WALファイル（.db-wal）、共有メモリファイル（.db-shm）の3つのファイルが生成されます。

---

SQLite WALモードにおける一般的なエラーとトラブルシューティング

WALモードはクラッシュリカバリに非常に強いですが、それでもいくつかの状況で問題が発生することがあります。

SQLITE_BUSY エラー（データベースがロックされている）

原因
WALモードは読み取りと書き込みの並行性を高めますが、特定の操作（特にリカバリプロセスやチェックポイント処理）ではデータベース全体に排他ロックがかかることがあります。例えば、クラッシュ後に最初にデータベースを開いた接続は、リカバリ処理を行うために排他ロックを取得します。その間に別の接続がデータベースにアクセスしようとすると、SQLITE_BUSY エラーが発生する可能性があります。

トラブルシューティング

• 手動チェックポイントの検討
  自動チェックポイント処理が頻繁にロックを引き起こす場合、アプリケーションのロジックで閑散期に手動でチェックポイント（例: PRAGMA wal_checkpoint(full);）を実行することを検討します。これにより、予測できないタイミングでのロックを減らすことができます。
• 読み取りと書き込みの接続の分離
  読み取り専用の接続と書き込み専用の接続を分けることで、SQLITE_BUSY の発生頻度を減らせる場合があります。
• トランザクションの短縮化
  書き込みトランザクションを可能な限り短くすることで、ロックが保持される時間を減らします。
• リトライロジックの実装
  SQLITE_BUSY エラーが発生した場合、すぐに諦めずに、数ミリ秒から数秒の待機を挟んで操作をリトライするロジックを実装します。SQLiteのデフォルトのbusy_timeout設定（通常0）を増やすことも有効です。

  PRAGMA busy_timeout = 5000; -- 5秒待機
  

WALファイル (.db-wal) の肥大化とディスクスペース不足

原因
WALファイルは、チェックポイント処理によってメインのデータベースファイルにマージされるまで、コミットされた変更を保持します。以下の状況でWALファイルが肥大化することがあります。

• アプリケーションの不適切な終了
  データベース接続が適切に閉じられずにアプリケーションが終了した場合、WALファイルが適切にクリーンアップされないことがあります。
• 自動チェックポイントの無効化
  PRAGMA journal_mode=WAL; を設定した際に、自動チェックポイントが無効になっている場合。
• チェックポイントの停滞 (Checkpoint Starvation)
  データベースへの読み取り接続が常に存在し、チェックポイント処理が完了できない場合。チェックポイントは、WALファイルの内容をメインのデータベースファイルに書き込むために、全ての読み取り接続が一時的に存在しない状態になる必要があります。

トラブルシューティング

• アプリケーション終了時のクリーンアップ
  データベース接続を閉じる前に、明示的にsqlite3_close()を呼び出すなど、適切なクリーンアップ処理を行うことが重要です。これにより、WALファイルが不要なまま残ることを防ぎます。
• 適切なチェックポイント戦略
  
  • 自動チェックポイントの確認
    通常はSQLiteが自動的にチェックポイントを実行しますが、システムによっては自動チェックポイントの挙動が問題となることがあります。デフォルトではWALファイルが1000ページを超えるとチェックポイントが開始されます。
  • 手動チェックポイントの実行
    PRAGMA wal_checkpoint(FULL); または PRAGMA wal_checkpoint(TRUNCATE); を定期的に実行することで、WALファイルの内容をメインデータベースにマージし、WALファイルをリセット（または削除）できます。これは、アプリケーションのアイドル時や、定期的なメンテナンスとして行うのが理想的です。
  • リーダーギャップの確保
    多数の並行リーダーが存在する場合、全てのリーダーが一時的にデータベースへのアクセスを停止する「リーダーギャップ」を意図的に設けることで、チェックポイントが完了できるようになります。

データ損失または不整合（極めて稀だが注意）

原因
WALモードは非常に堅牢であり、通常はクラッシュ時にコミット済みのデータが失われることはありません。しかし、以下の極端な状況下ではデータ損失や不整合が発生する可能性があります。

• 複数のSQLiteインスタンスからの同時アクセス
  複数の異なるSQLiteライブラリのインスタンスが同じデータベースファイルにアクセスすると、データベースが破損する可能性があります。
• WALファイルの手動削除
  WALファイル (.db-wal) は、データベースと一体であり、データベースがオープンされている間に手動で削除すると、データ損失やデータベースの破損につながる可能性があります。SQLiteはWALファイルを自動的に管理します。
• ファイルシステムの問題
  基盤となるファイルシステムやハードウェアの障害が、WALファイルやデータベースファイルの破損を引き起こすことがあります。
• PRAGMA synchronous=OFF; の使用
  synchronous=OFF は最高のパフォーマンスを提供しますが、システムクラッシュ時にデータが失われる可能性を著しく高めます。本番環境での使用は推奨されません。

トラブルシューティング

• 定期的なバックアップ
  最も基本的な対策として、定期的なデータベースのバックアップは常に重要です。
• 単一のSQLiteライブラリの使用
  アプリケーション内で複数のSQLiteライブラリのバージョンやインスタンスを同時に使用しないようにします。
• WALファイルの管理はSQLiteに任せる
  WALファイルやSHMファイル (.db-shm) を手動で操作（削除など）しないようにします。SQLiteが必要に応じてこれらのファイルを自動的に作成、更新、削除します。
• ファイルシステムの健全性確認
  データベースファイルが配置されているファイルシステムに異常がないか確認します。
• PRAGMA synchronous=FULL; (または NORMAL;)
  堅牢性を最優先する場合は FULL を設定します。パフォーマンスとのバランスを重視する場合は NORMAL を設定します。NORMAL でも、トランザクションのコミットはWALファイルへの書き込みが永続ストレージに同期された後に行われるため、クラッシュリカバリの堅牢性は高いです。

原因
WALモードでは、共有メモリファイル（.db-shm）が使用されます。これは、リーダーとライター間でWALファイルの情報を共有するために使用されます。通常、最後の接続が閉じられると、WALファイルとSHMファイルは削除されますが、アプリケーションのクラッシュや予期せぬ終了によって、これらのファイルが残ってしまうことがあります。

• クリーンアップの確認
  アプリケーションが適切に終了し、データベース接続が閉じられていることを確認します。
• これはエラーではない
  db-shm ファイルが残っていても、通常はそれ自体が問題ではありません。次にデータベースが開かれたときに、SQLiteはこれらのファイルの存在を認識し、適切にリカバリプロセスを開始します。

---

SQLite WALモードにおけるプログラミング例とベストプラクティス

WALモードでのクラッシュリカバリはSQLite内部で自動的に処理されますが、アプリケーションコード側でWALモードを適切に設定し、堅牢性を高めるためのプラクティスがあります。

WALモードの有効化とデータベース接続の基本

WALモードを有効にする最も基本的な例です。

import sqlite3
import os
import time

DB_FILE = 'my_database_wal.db'

def setup_database():
    """データベースをセットアップし、WALモードを有効にする"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()

        # WALモードを有効にする
        # これにより、データベースファイルと共に .db-wal と .db-shm ファイルが生成される
        cursor.execute("PRAGMA journal_mode=WAL;")
        print("WALモードを有効にしました。")

        # 同期モードを設定する（推奨: NORMAL または FULL）
        # NORMAL: クラッシュ時にもコミット済みデータが失われにくい
        # FULL: NORMALよりさらに堅牢だが、パフォーマンスは低下
        cursor.execute("PRAGMA synchronous=NORMAL;")
        print("synchronousモードをNORMALに設定しました。")

        # テーブルを作成（もし存在しなければ）
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sensor_data (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT,
                value REAL
            )
        ''')
        conn.commit()
        print("データベースとテーブルのセットアップが完了しました。")

    except sqlite3.Error as e:
        print(f"データベースのセットアップ中にエラーが発生しました: {e}")
    finally:
        if conn:
            conn.close()

def insert_data(num_records=1000):
    """データを挿入する（意図的にクラッシュをシミュレートする可能性のある操作）"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()

        print(f"{num_records}件のデータを挿入中...")
        for i in range(num_records):
            timestamp = time.strftime('%Y-%m-%d %H:%M:%S')
            value = i * 0.1
            cursor.execute("INSERT INTO sensor_data (timestamp, value) VALUES (?, ?)", (timestamp, value))
            if (i + 1) % 100 == 0:
                print(f"{i + 1}件のデータを挿入しました。")
        conn.commit()
        print(f"{num_records}件のデータ挿入が完了しました。")

    except sqlite3.Error as e:
        print(f"データ挿入中にエラーが発生しました: {e}")
    finally:
        if conn:
            conn.close()

def read_data():
    """データを読み取る"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM sensor_data")
        count = cursor.fetchone()[0]
        print(f"現在のレコード数: {count}")
    except sqlite3.Error as e:
        print(f"データ読み取り中にエラーが発生しました: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # 古いデータベースファイルを削除してクリーンな状態から始める
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)
    if os.path.exists(DB_FILE + "-wal"):
        os.remove(DB_FILE + "-wal")
    if os.path.exists(DB_FILE + "-shm"):
        os.remove(DB_FILE + "-shm")

    setup_database()
    
    print("\n--- データを挿入します（途中でプログラムを強制終了する可能性あり）---")
    print("この後、データベースファイルが壊れていないことを確認します。")
    print("Ctrl+Cなどで強制終了してみてください。")
    
    # 意図的に多数のデータを挿入し、途中で終了することでクラッシュをシミュレート
    try:
        insert_data(50000) # 大量のデータを挿入
    except KeyboardInterrupt:
        print("\nプログラムが強制終了されました。WALモードのリカバリをテストします。")
    
    print("\n--- データベースを再度開いてデータを確認します ---")
    read_data()
    print("もしクラッシュ後にデータが回復されていれば、WALモードが機能しています。")
    print(f"データベースファイル: {DB_FILE}")
    print(f"WALファイル: {DB_FILE}-wal")
    print(f"SHMファイル: {DB_FILE}-shm")

説明

1. PRAGMA journal_mode=WAL;: データベース接続を開いた直後にこれを実行することで、WALモードが有効になります。これにより、将来の全てのトランザクションがWALファイルに書き込まれるようになります。
2. PRAGMA synchronous=NORMAL;: WALモードで最も推奨される同期設定です。NORMAL は、トランザクションがコミットされるときにWALファイルへの書き込みがOSレベルで同期されることを保証します。これにより、システムクラッシュが発生してもコミットされたデータが失われることはありません。OFF は最速ですが、データ損失のリスクがあります。FULL は最も堅牢ですが、NORMAL よりも遅いです。
3. クラッシュシミュレーションとリカバリの確認: insert_data 関数で大量のデータを挿入し、途中でプログラムを強制終了 (Ctrl+C など) してみてください。その後、再度スクリプトを実行すると、read_data 関数でデータベースのデータが確認されます。WALモードが正しく機能していれば、クラッシュ発生までにコミットされた全てのデータが回復しているはずです。WALファイルが残っている場合でも、SQLiteはそれを読み込んで整合性を回復します。

SQLITE_BUSY エラーのハンドリングとリトライロジック

WALモードは並行性を高めますが、チェックポイント処理中など、特定の状況でデータベースが一時的にロックされることがあります。

import sqlite3
import time
import threading

DB_FILE_BUSY = 'my_database_busy.db'

def worker_write(thread_id):
    """データを書き込むスレッド"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_BUSY)
        # busy_timeoutを設定してリトライを試みる
        conn.execute("PRAGMA busy_timeout = 5000;") # 5秒待機
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")

        for i in range(5):
            try:
                cursor = conn.cursor()
                cursor.execute(f"INSERT INTO busy_test (data) VALUES (?)", (f"Thread {thread_id} - Data {i}",))
                conn.commit()
                print(f"スレッド {thread_id}: データを挿入しました。")
                time.sleep(0.1) # 少し待機して他のスレッドに機会を与える
            except sqlite3.Error as e:
                if "database is locked" in str(e):
                    print(f"スレッド {thread_id}: SQLITE_BUSY エラー。リトライします...")
                    time.sleep(0.05) # 短く待ってリトライ
                else:
                    print(f"スレッド {thread_id}: その他のエラー発生: {e}")
                    break
    except sqlite3.Error as e:
        print(f"スレッド {thread_id}: 接続エラー: {e}")
    finally:
        if conn:
            conn.close()

def worker_read(thread_id):
    """データを読み込むスレッド"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_BUSY)
        conn.execute("PRAGMA busy_timeout = 5000;") # 5秒待機
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")

        for i in range(5):
            try:
                cursor = conn.cursor()
                cursor.execute("SELECT COUNT(*) FROM busy_test")
                count = cursor.fetchone()[0]
                print(f"スレッド {thread_id}: 現在のレコード数: {count}")
                time.sleep(0.1)
            except sqlite3.Error as e:
                if "database is locked" in str(e):
                    print(f"スレッド {thread_id}: SQLITE_BUSY エラー。リトライします...")
                    time.sleep(0.05)
                else:
                    print(f"スレッド {thread_id}: その他のエラー発生: {e}")
                    break
    except sqlite3.Error as e:
        print(f"スレッド {thread_id}: 接続エラー: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    if os.path.exists(DB_FILE_BUSY):
        os.remove(DB_FILE_BUSY)
    if os.path.exists(DB_FILE_BUSY + "-wal"):
        os.remove(DB_FILE_BUSY + "-wal")
    if os.path.exists(DB_FILE_BUSY + "-shm"):
        os.remove(DB_FILE_BUSY + "-shm")

    # 初期セットアップ
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_BUSY)
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        conn.execute('''
            CREATE TABLE IF NOT EXISTS busy_test (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                data TEXT
            )
        ''')
        conn.commit()
    except sqlite3.Error as e:
        print(f"初期セットアップエラー: {e}")
    finally:
        if conn:
            conn.close()

    print("\n--- 複数のスレッドで読み書きを並行して実行します ---")
    print("SQLITE_BUSY エラー発生時にリトライされることを確認します。")

    threads = []
    # 書き込みスレッドを2つ
    threads.append(threading.Thread(target=worker_write, args=(1,)))
    threads.append(threading.Thread(target=worker_write, args=(2,)))
    # 読み取りスレッドを2つ
    threads.append(threading.Thread(target=worker_read, args=(3,)))
    threads.append(threading.Thread(target=worker_read, args=(4,)))

    for t in threads:
        t.start()

    for t in threads:
        t.join()

    print("\nすべてのスレッドが終了しました。")
    read_conn = None
    try:
        read_conn = sqlite3.connect(DB_FILE_BUSY)
        cursor = read_conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM busy_test")
        final_count = cursor.fetchone()[0]
        print(f"最終的なレコード数: {final_count}")
    except sqlite3.Error as e:
        print(f"最終確認エラー: {e}")
    finally:
        if read_conn:
            read_conn.close()

説明

1. PRAGMA busy_timeout = 5000;: これはSQLiteへの接続ごとに設定する必要があります。sqlite3_busy_timeout() C APIのラッパーで、SQLiteにSQLITE_BUSY エラーが発生した場合、指定されたミリ秒数だけ内部的にリトライするよう指示します。これにより、アプリケーションレベルで独自のリトライロジックを実装する手間を省けます。
2. 手動リトライロジック: try...except sqlite3.Error as e: ブロック内で、エラーメッセージに "database is locked" が含まれているかを確認し、その場合に短時間待機 (time.sleep()) してから操作を再試行するロジックを実装しています。busy_timeout と併用することで、より堅牢なエラーハンドリングが可能です。
3. 複数スレッドでのテスト: 複数のスレッドで同時にデータベースへの書き込みと読み取りを行うことで、SQLITE_BUSY が発生しやすい状況をシミュレートし、リトライロジックが機能することを確認します。

通常、SQLiteは自動的にチェックポイントを実行しますが、特定のアプリケーションシナリオ（例: 大量の書き込み後の閑散期にWALファイルを積極的に縮小したい場合）では、手動でチェックポイントを実行することが有効です。

import sqlite3
import os
import time

DB_FILE_CHECKPOINT = 'my_database_checkpoint.db'

def setup_db_for_checkpoint():
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_CHECKPOINT)
        cursor = conn.cursor()
        cursor.execute("PRAGMA journal_mode=WAL;")
        cursor.execute("PRAGMA synchronous=NORMAL;")
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS large_data (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                text_data TEXT
            )
        ''')
        conn.commit()
    except sqlite3.Error as e:
        print(f"セットアップエラー: {e}")
    finally:
        if conn:
            conn.close()

def insert_large_data(num_records=10000):
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_CHECKPOINT)
        cursor = conn.cursor()
        print(f"{num_records}件のデータを挿入中...")
        for i in range(num_records):
            cursor.execute("INSERT INTO large_data (text_data) VALUES (?)", (f"This is a long string of data for record {i}",))
            if (i + 1) % 1000 == 0:
                conn.commit() # 定期的にコミットしてWALに書き込む
        conn.commit()
        print(f"{num_records}件のデータ挿入完了。")
    except sqlite3.Error as e:
        print(f"データ挿入エラー: {e}")
    finally:
        if conn:
            conn.close()

def perform_checkpoint(mode='FULL'):
    """指定されたモードでチェックポイントを実行する"""
    conn = None
    try:
        conn = sqlite3.connect(DB_FILE_CHECKPOINT)
        # チェックポイント処理中にSQLITE_BUSYを避けるためbusy_timeoutを設定
        conn.execute("PRAGMA busy_timeout = 10000;") # 長めに設定
        
        # PRAGMA wal_checkpoint(mode);
        # modes: PASSIVE, FULL, RESTART, TRUNCATE
        # FULL: WALファイルのすべての内容をDBにマージし、WALをリセットする（リーダーが存在しない場合）
        # TRUNCATE: FULLと同じだが、walファイルを切り詰める
        # PASSIVE: DBにマージできるものをマージする（リーダーをブロックしない）
        # RESTART: PASSIVEと同様だが、リーダーのWAL読み取り位置をリセットする
        
        cursor = conn.cursor()
        print(f"チェックポイント ({mode}) を実行中...")
        cursor.execute(f"PRAGMA wal_checkpoint({mode});")
        # wal_checkpointの結果はタプルで返される
        # (log_size, checkpointed_frame_count, log_bytes_still_to_go_after_checkpoint, num_busy_retries, error_code)
        result = cursor.fetchone() 
        print(f"チェックポイント結果: {result}")
        if result[4] == 0: # 5番目の要素がエラーコード、0は成功
            print("チェックポイントが成功しました。")
        else:
            print(f"チェックポイント中にエラーが発生しました: SQLiteエラーコード {result[4]}")
            
    except sqlite3.Error as e:
        print(f"チェックポイント中にエラーが発生しました: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    if os.path.exists(DB_FILE_CHECKPOINT):
        os.remove(DB_FILE_CHECKPOINT)
    if os.path.exists(DB_FILE_CHECKPOINT + "-wal"):
        os.remove(DB_FILE_CHECKPOINT + "-wal")
    if os.path.exists(DB_FILE_CHECKPOINT + "-shm"):
        os.remove(DB_FILE_CHECKPOINT + "-shm")

    setup_db_for_checkpoint()

    print("\n--- 大量のデータを挿入し、WALファイルを成長させます ---")
    insert_large_data(50000) # WALファイルが大きくなるように

    print(f"現在のWALファイルサイズ: {os.path.getsize(DB_FILE_CHECKPOINT + '-wal') / (1024*1024):.2f} MB")
    
    # ここでWALファイルが大きくなっていることを確認できるはずです
    # メインのデータベースファイルはまだ大きくなっていない可能性があります

    print("\n--- 手動でフルチェックポイントを実行します ---")
    perform_checkpoint('FULL')

    # チェックポイント後、WALファイルが小さくなるか、削除されていることを確認
    if os.path.exists(DB_FILE_CHECKPOINT + "-wal"):
        print(f"チェックポイント後のWALファイルサイズ: {os.path.getsize(DB_FILE_CHECKPOINT + '-wal') / (1024*1024):.2f} MB")
    else:
        print("WALファイルは削除されました。")

    print("\n--- TRUNCATEモードで再度チェックポイントを実行します（WALファイルを切り詰める） ---")
    # さらにデータを挿入してWALを再成長させる
    insert_large_data(10000)
    print(f"現在のWALファイルサイズ: {os.path.getsize(DB_FILE_CHECKPOINT + '-wal') / (1024*1024):.2f} MB")
    
    perform_checkpoint('TRUNCATE')
    if os.path.exists(DB_FILE_CHECKPOINT + "-wal"):
        print(f"TRUNCATEチェックポイント後のWALファイルサイズ: {os.path.getsize(DB_FILE_CHECKPOINT + '-wal') / (1024*1024):.2f} MB")
    else:
        print("TRUNCATEチェックポイント後、WALファイルは削除されました。")

1. PRAGMA wal_checkpoint(mode);: これが手動チェックポイントを実行するためのSQLコマンドです。
   • FULL: WALファイル内の全てのコミット済みトランザクションをメインのデータベースファイルにマージします。もし読み取り接続がアクティブでマージを妨げる場合、完了するまで待機します。完了後、WALファイルはリセット（通常は削除）されます。
   • TRUNCATE: FULL と同様ですが、チェックポイント後にWALファイルを可能な限り切り詰めます。実質的に FULL と同じ効果が期待できます。
   • PASSIVE: リーダーをブロックすることなく、可能な限り多くのWALフレームをメインのデータベースファイルにマージします。WALファイルは削除されません。
   • RESTART: PASSIVE と似ていますが、全てのリーダーのWAL読み取り位置をリセットします。これは、リーダーが古いWALフレームを解放するのを待つ場合に役立ちます。
2. busy_timeout の設定: チェックポイント処理は、特に FULL や TRUNCATE モードでは、全てのリーダーがデータベースへのアクセスを停止するまで待機する必要があります。この間に SQLITE_BUSY エラーが発生する可能性があるため、busy_timeout を設定して、チェックポイントが完了するまでリトライさせるのが良いプラクティスです。

---

しかし、「代替手法」という観点から、より広範な意味でWALモードの運用や堅牢性、可用性を高めるためのアプローチや考慮事項について説明します。これらは直接的なリカバリコードではありませんが、システム全体の回復力向上に寄与します。

データベースのバックアップと復元

これは最も基本的な代替手法であり、WALモードのリカバリ能力を補完するものです。WALモードはコミットされたデータの損失を防ぎますが、ファイルシステムの破損やユーザーによる誤操作（テーブルのドロップなど）からは保護しません。

プログラミング例

SQLiteには、稼働中のデータベースを非同期にバックアップできるAPIが用意されています。これにより、データベースへのアクセスをブロックすることなくバックアップを取得できます。

import sqlite3
import time
import os

SOURCE_DB = 'my_wal_database.db'
BACKUP_DB = 'my_wal_database_backup.db'

def create_and_populate_db():
    """テスト用にデータベースを作成しデータを挿入する"""
    conn = None
    try:
        conn = sqlite3.connect(SOURCE_DB)
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        conn.execute('''
            CREATE TABLE IF NOT EXISTS data (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                value TEXT,
                timestamp TEXT DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        for i in range(100):
            conn.execute("INSERT INTO data (value) VALUES (?)", (f"Original data {i}",))
        conn.commit()
        print(f"データベース '{SOURCE_DB}' を作成し、データを挿入しました。")
    except sqlite3.Error as e:
        print(f"データベース作成エラー: {e}")
    finally:
        if conn:
            conn.close()

def backup_database():
    """稼働中のデータベースをバックアップする"""
    source_conn = None
    backup_conn = None
    try:
        source_conn = sqlite3.connect(SOURCE_DB)
        # WALモードが有効であることを確認 (必須ではないが推奨)
        source_conn.execute("PRAGMA journal_mode=WAL;") 
        
        backup_conn = sqlite3.connect(BACKUP_DB)

        # sqlite3_backup_init および sqlite3_backup_step を使用する
        # Pythonのsqlite3モジュールでは、Connectionオブジェクトのbackup()メソッドとして提供されている
        print(f"'{SOURCE_DB}' を '{BACKUP_DB}' へバックアップ中...")
        source_conn.backup(backup_conn)
        print("バックアップが完了しました。")

    except sqlite3.Error as e:
        print(f"データベースのバックアップ中にエラーが発生しました: {e}")
    finally:
        if source_conn:
            source_conn.close()
        if backup_conn:
            backup_conn.close()

def restore_database():
    """バックアップからデータベースを復元する（既存ファイルを上書き）"""
    if os.path.exists(SOURCE_DB):
        os.remove(SOURCE_DB)
    if os.path.exists(SOURCE_DB + "-wal"):
        os.remove(SOURCE_DB + "-wal")
    if os.path.exists(SOURCE_DB + "-shm"):
        os.remove(SOURCE_DB + "-shm")
        
    source_conn = None
    backup_conn = None
    try:
        backup_conn = sqlite3.connect(BACKUP_DB)
        source_conn = sqlite3.connect(SOURCE_DB) # 新しい空のDBファイルが作成される
        
        print(f"'{BACKUP_DB}' から '{SOURCE_DB}' へ復元中...")
        backup_conn.backup(source_conn) # バックアップ元のDBからバックアップ先のDBへ
        print("復元が完了しました。")

    except sqlite3.Error as e:
        print(f"データベースの復元中にエラーが発生しました: {e}")
    finally:
        if source_conn:
            source_conn.close()
        if backup_conn:
            backup_conn.close()

def verify_data(db_path):
    """データベースのデータを確認する"""
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM data")
        count = cursor.fetchone()[0]
        print(f"'{db_path}' のレコード数: {count}")
    except sqlite3.Error as e:
        print(f"データ検証エラー for {db_path}: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # クリーンアップ
    if os.path.exists(SOURCE_DB): os.remove(SOURCE_DB)
    if os.path.exists(SOURCE_DB + "-wal"): os.remove(SOURCE_DB + "-wal")
    if os.path.exists(SOURCE_DB + "-shm"): os.remove(SOURCE_DB + "-shm")
    if os.path.exists(BACKUP_DB): os.remove(BACKUP_DB)

    create_and_populate_db()
    verify_data(SOURCE_DB)

    print("\n--- データベースのバックアップ ---")
    backup_database()
    verify_data(BACKUP_DB)

    # 意図的にソースDBを破損させるか、一部データを変更
    print("\n--- ソースDBに新しいデータを挿入し、バックアップとの差異を作る ---")
    conn = sqlite3.connect(SOURCE_DB)
    conn.execute("INSERT INTO data (value) VALUES ('New data after backup')")
    conn.commit()
    conn.close()
    verify_data(SOURCE_DB)

    print("\n--- バックアップからデータベースを復元 ---")
    restore_database()
    verify_data(SOURCE_DB) # 復元されたデータがバックアップ時点のものになることを確認

• 用途: WALモードのリカバリがカバーしない、より広範な障害（例: ソフトウェアバグによるデータ破壊、ディスク障害、誤操作）からの復旧に不可欠です。

シャドーイング / ファイルシステムのスナップショット

ファイルシステムレベルの機能を利用して、データベースファイルの安全性を高める方法です。

• クラウドストレージの機能: AWS EBSスナップショット、Azure Disk Snapshotsなどのクラウドプロバイダーの機能も同様の目的で使用できます。
• ZFS / Btrfs スナップショット: これらの高度なファイルシステムも、スナップショット機能を提供し、非常に効率的なポイントインタイムリカバリを可能にします。
• LVM (Logical Volume Manager) スナップショット: Linux環境では、LVMを使用してデータベースファイルが保存されている論理ボリュームのスナップショットを撮ることができます。これにより、特定の時点のファイルシステムの状態をキャプチャし、必要に応じてその状態にロールバックできます。データベースがWALモードであれば、スナップショット取得中に書き込みが発生していても整合性は保たれます。

プログラミングとの関連性

これらの方法は直接プログラミングコードを書くものではありませんが、スクリプト（Bashスクリプトなど）からこれらのスナップショットコマンドを呼び出し、自動化することは可能です。

# LVMスナップショットの例 (擬似コード)
# vgcreate myvg /dev/sda
# lvcreate -L 10G -n mylv myvg /dev/sda
# mkfs.ext4 /dev/myvg/mylv
# mount /dev/myvg/mylv /path/to/sqlite/db

# スナップショットの作成 (SQLiteプロセスは稼働中でもOK)
lvcreate --snapshot -L 1G -n mylv_snap /dev/myvg/mylv

# スナップショットのマウントとバックアップ
mkdir /mnt/snap
mount /dev/myvg/mylv_snap /mnt/snap
cp /mnt/snap/my_wal_database.db /mnt/backup_destination/
umount /mnt/snap
lvremove /dev/myvg/mylv_snap

# 復元 (データベースを停止し、ファイルをコピー)
# umount /path/to/sqlite/db
# cp /mnt/backup_destination/my_wal_database.db /path/to/sqlite/db/
# mount /path/to/sqlite/db

• WALモードのおかげで、スナップショットが取得された瞬間に書き込み操作が行われていても、データベースの整合性は保たれます（WALファイルの内容から復元されます）。
• ファイルシステムスナップショットは非常に高速で、大規模なデータベースのバックアップに適しています。

SQLite自体はクライアント/サーバー型のデータベースではないため、組み込み用途が主です。しかし、高可用性が求められるシナリオでは、WALモードと組み合わせて外部のレプリケーション戦略が用いられることがあります。

• アプリケーションレベルのレプリケーション: 独自のアプリケーションロジックで、複数のSQLiteデータベースに同じ変更を書き込む（またはイベントソーシングのような手法でログを共有する）方法です。
  • 利点: アプリケーションの要件に合わせて柔軟に制御できる。
  • 欠点: 複雑性が増し、競合解決のロジックが必要になる場合がある。

プログラミングとの関連性

アプリケーションレベルのレプリケーションでは、データの変更を記録するロギング層や、リモートノードへのデータ送信ロジックをプログラミングする必要があります。

# アプリケーションレベルのレプリケーションの概念的な例
# (これは完全な実装ではなく、アイデアを示すものです)

def replicate_transaction(transaction_data):
    """トランザクションデータを複数のSQLiteインスタンスに適用する"""
    # この関数は、ローカルDBへの書き込み後、
    # リモートDBへの書き込み（またはメッセージキューへの送信）を行う
    
    # ローカルDBへの書き込み (WALモード)
    conn_local = sqlite3.connect('local_db.db')
    conn_local.execute("PRAGMA journal_mode=WAL;")
    # ... ローカルDBにデータを書き込む ...
    conn_local.commit()
    conn_local.close()

    # リモートDBへの送信 (例: HTTP POST, Kafka, Redis Pub/Sub など)
    # response = send_to_remote_api(transaction_data)
    # if response.status_code != 200:
    #     log_error("リモートへの書き込みに失敗")

# メインアプリケーションロジック
# def perform_db_operation(...):
#     # ... データベース操作 ...
#     replicate_transaction(data_to_replicate)

• 可用性: WALモードは単一インスタンスのクラッシュリカバリに優れますが、サーバー全体の障害（ハードウェア故障など）に対する可用性には限界があります。レプリケーションは、このような状況でシステム全体の可用性を高めるための「代替手法」となります。

SQLiteのWALモードにおける「クラッシュリカバリのプログラミング」は、直接的なリカバリコードを書くことよりも、WALモードの特性を理解し、それを最大限に活かすための予防策と運用戦略に重点が置かれます。

• 外部ツール/戦略: 必要に応じてファイルシステムのスナップショットや、アプリケーションレベルのレプリケーションなどの高可用性戦略を組み合わせる。
• バックアップ: 定期的なオンラインバックアップの実施。
• エラーハンドリング: SQLITE_BUSY エラーに対するリトライロジックの実装。
• 基本的な設定: PRAGMA journal_mode=WAL; と PRAGMA synchronous=NORMAL; を正しく設定すること。