SQLiteの並行性と、それを考慮すべき理由

 (jellyfin.org)
11 ポイント 投稿者 GN⁺ 2025-11-02 | 2件のコメント | WhatsAppで共有
  • SQLiteのファイルベース構造はシンプルだが、同時に複数の書き込みを行うとロック競合が発生する可能性がある
  • Jellyfinは長年SQLiteを使用してきたが、一部のシステムでトランザクション中のデータベースロックエラーによりアプリケーションが停止する問題が発生した
  • EF Coreの**インターセプター(interceptor)機能を活用し、3つのロック戦略（No-Lock, Optimistic, Pessimistic）**を実装して問題を緩和
  • Optimistic方式はリトライベースで性能低下を最小限に抑え、Pessimistic方式は安定性を高める代わりに速度低下を受け入れる
  • このアプローチは他のEF Coreアプリケーションにも容易に適用できる構造で、SQLiteの並行性問題に対する実用的な代替策を提供する

SQLiteの基本構造と制約

  • SQLiteはアプリケーション内部で実行されるファイルベースのリレーショナルデータベースエンジン
    • 1つのファイルにすべてのデータを保存し、別個のサーバーアプリケーションを必要としない
  • 単一ファイルをアプリケーションが完全に管理するため、複数のプロセスが同時にアクセスすると競合リスクがある
  • そのためSQLiteを使うアプリケーションは、同時に1つの書き込み処理だけを実行すべき

Write-Ahead-Log(WAL)モード

  • SQLiteは**WAL(Write-Ahead-Log)**機能によって並行性の制約を緩和する
    • WALファイルはデータベース変更を記録するジャーナルファイルとして機能する
    • 複数の書き込み処理を並列にキューイングし、読み取り時にWALの変更内容を適用する
  • しかしWALも完全ではなく、特定の状況では依然としてロック競合が発生する

SQLiteトランザクションの問題

  • トランザクションは変更処理の原子性保証と読み取りブロックの制御を担う
  • Jellyfinの一部システムでは、トランザクション中にSQLiteが“database is locked”エラーを返して即座に中断する現象が発生した
    • この問題はOS、ディスク速度、仮想化の有無に関係なく報告されている
    • 再現が難しく不規則に発生するため、原因究明が難しい

JellyfinにおけるSQLiteの使い方と問題点

  • 推奨環境（ネットワークストレージではないこと、SSD）では問題発生はまれだが、10.11以前の並列処理制限バグにより
    • ライブラリスキャン処理が過剰に並列実行され、数千件の同時書き込み要求が発生
    • SQLiteエンジンのリトライおよびタイムアウトの限界を超え、データベース過負荷とエラーを引き起こした
  • 長いトランザクションと非効率なクエリも問題を悪化させる

EF Coreベースの解決策

  • JellyfinはEF Coreへコードベースを移行したことで、構造的な制御が可能になった
  • EF Coreのインターセプター(Interceptors)を活用し、すべてのコマンドとトランザクション実行を横取りして透過的なロック制御を実装
  • 3つのロック戦略を導入
    1. No-Lock: 基本モードで、追加のロックなし。多くの場合、性能低下を防ぐために使用
    2. Optimistic Locking: 失敗時にPollyライブラリを使ってリトライを実行
    3. Pessimistic Locking: すべての書き込み処理の前にReaderWriterLockSlimでデータベース全体をロック

Optimistic Lockingの動作方式

  • 処理の成功を前提とし、失敗時にリトライする方式
    • 2つの書き込み処理が衝突すると、一方が失敗し、一定時間待機した後に再試行する
  • Pollyライブラリを使い、ロックによる失敗だけをリトライ対象として扱う
  • Pessimistic方式よりオーバーヘッドが少なく、性能低下も小さい

Pessimistic Lockingの動作方式

  • すべての書き込み処理時にデータベース全体をロックする
    • 書き込み中はすべての読み取り・書き込み処理がブロックされる
  • この方式は最も安定しているが、最も遅い方式でもある
    • たとえば「Alice」テーブルを読んでいる間に「Bob」テーブルへ書き込める場合でも、それを許可しない
  • ReaderWriterLockSlimを使い、複数の読み取りは許可しつつ、単一の書き込みだけを許可する

今後の計画: Smart Locking

  • Optimistic方式とPessimistic方式を組み合わせたSmart Lockingの導入を検討中
    • 両方式の長所を統合し、性能と安定性のバランスを追求する

結果と適用可能性

  • 初期テストの結果、両ロックモードとも問題解決に有効だった
  • 問題の根本原因は依然として不明だが、ユーザーがJellyfinを安定して使える選択肢は得られた
  • インターネット上でも同様のエラー報告は多かったが、完全な解決策は存在しなかった
  • Jellyfinの実装はEF Coreインターセプターベースで容易にコピー・適用できる構造
    • 呼び出し側は内部のロック動作を認識する必要がない
  • 同じSQLite並行性問題を抱える他のEF Coreアプリケーションでもすぐに活用可能

関連記事

2件のコメント

 
GN⁺ 2025-11-02
Hacker Newsの意見

  • 以前 SQLiteのブロッキング問題 に遭遇したことがあるが、原因はディスクの 断片化(fragmentation) だった
    古いAndroidタブレットで、アプリを何年も毎日8時間ずつ使っていたユーザーたちが、動作の低下とロックエラーを訴えていた
    データをコピーして受け取っても再現しなかったが、最終的に端末を直接受け取って確認したところ、DBファイルを新しい場所にコピーしてから元の名前に戻す形で「デフラグ」すると、問題が完全に消えた
    JellyfinのDBでも同じ方法で性能向上を経験した

    • それは断片化というより フラッシュメモリの劣化 の可能性が高いのでは。eMMCストレージ搭載の低価格タブレットだったのか気になる
    • SQLiteの VACUUM関数 でも同じ効果が得られるのか気になる
    • 興味深い事例だ。とはいえ、ユーザーに直接デフラグさせるわけにはいかないので、現実的な解決策が必要だ

  • SQLiteのトランザクションは基本的に 「deferred」モード で開始される
    つまり、実際に書き込みを試みるまではwrite lockを取得しない
    SQLITE_BUSY エラーは、読み取りトランザクションが書き込みへ移行しようとしたときに、すでに別のトランザクションがwrite lockを保持している場合に発生する
    解決策は busy_timeout を設定し、書き込みを含むトランザクションは「immediate」モードで開始することだ
    関連説明は このブログ記事 によく整理されている

    • 私も最初はこれが SQLITE_BUSY の問題だと思っていた。関連事例は ここ にまとめている
    • SQLITE_BUSY は一種の アーキテクチャ臭 だと思う。WALモードでは読み取り専用コネクションプールと単一書き込みコネクションプールを分離して設計する。こうすればロック保持状態を明確に把握でき、競合状況 を事前に設計できる
    • このケースでは busy_timeout は適用されない。WALモードではページが単一のログファイルに追記されるため、読み取り中に書き込みへ移行しようとすると、直列化保証 のためにSQLiteが即座に失敗させる。「immediate」モードがこれを防ぐ
    • 結局SQLiteを使う人なら、いつか一度はこの問題で 痛い目を見て、原因追跡に時間を使うことになる
    • ブログ記事では SQLITE_BUSY への言及がなかったが、おそらく設定が抜けていたのだろう

  • 記事の一部説明は誤っているようだ
    SQLiteは自前で ロック管理 を行うため、アプリケーションが直接ファイルアクセスを制御する必要はない
    またWALは複数の 並列書き込み を許可しない。単に読み取りと単一書き込みを同時に実行できるようにするだけだ

    • 私もSQLiteは本当に好きだが、この記事は基本的な 並行性の概念 から間違っているのでおすすめできない

  • SQLiteは素晴らしいデータベースだが、デフォルト設定(defaults) があまりに保守的なのが惜しい
    実運用で使うには複数の PRAGMA設定 を調整する必要がある

    • どのPRAGMAをデフォルトで有効にしておくとよいのか知りたい
    • こういう状況なら、いっそ フォーク(fork) して新しいデフォルト値を作るほうがよいのではと思う

  • SQLiteの新しい hctree 機能が安定したら、その時点からはSQLiteだけを使うつもりだ
    名前の hc はたぶん High Concurrency の略だと思う
    公式ドキュメントへのリンク

  • こういう記事を見ると、問題の 根本原因分析 よりも、その場しのぎの解決策にとどまっている印象を受ける
    もっと深いデバッグと調査で正確な原因を突き止めることこそ、本当に価値ある共有だと思う

    • おそらく筆者は一部だけ調べて 不完全な解決策 を共有したのだろう。HNでよりよい答えを引き出す意図だったのかもしれない。「間違った答えを投稿すると、より早く正解が得られる」という話のように

  • WALモードも結局は 単一書き込み・複数読み取り の構造であることを理解していないようだ
    並列書き込みは不可能で、単に読み取りトランザクションが書き込みによってブロックされないようにするだけだ
    完全な MVCC があればよいが、現在の構造でも原理を理解していれば十分うまく動く

  • 私もJellyfinで似た問題を経験した
    普段は問題なく動くが、特定の状況でDBが ロックされたまま停止する現象 が起きる
    ログには "database is locked" だけが残り、結局Dockerコンテナを再起動しないと解決しない
    主にTV UIで複数のボタンを素早く操作したときに発生する

  • 少し別の話だが、SQLiteの in-memory DB を大量のinsert/delete処理に使うと、メモリ使用量が徐々に増えていく
    たとえば5分ごとに10万行を挿入して削除する処理を数日間繰り返すと、macOSでメモリが1GBまで増える
    こういう場合に調整できる設定があるのか気になる

    • VACUUM を定期的に実行しているか、auto_vacuum が有効になっているか確認してみることを勧める
      VACUUMドキュメント
    • バッファが使用パターンに合わせて 動的に調整 されている正常動作かもしれない
    • すべての行を削除するのであれば、単に テーブルをdropして再作成 するほうが効率的だ

  • SQLiteは素晴らしいが、こうした問題を見ると、いっそ Postgres を使ったほうがよいのではと思うこともある
    単一ファイルの可搬性や組み込み用途でないなら、Postgresのほうが並行性の問題をより簡単に解決できる

    • ただしJellyfinは セルフホスト型メディアサーバー なので、Postgresを要求すると導入と保守が複雑になる。SQLiteのほうが適している
    • Jellyfinは大半が 家庭用の単一ユーザー環境 なので、SQLiteで十分だ。ただし現在の設定は最適ではないようだ
    • SQLiteの利点を無視してPostgresへ行こうと言うのは、「キャンプしたいのに小屋を建てろ」と言うようなものだ
    • Postgresを使うと、導入だけでなく バージョンアップ時のマイグレーション にも気を配る必要がある。SQLiteにはこうした負担がない
    • Jellyfinは最近DBコードを Entity Framework で書き直しており、今後はDBの選択肢を柔軟にできるよう準備を進めている
 
ndrgrd 2025-11-03

えっ？と思う部分があって、すぐにコメントから確認してみたら、やっぱり……