SQLiteの同時更新で何が壊れるのか
SQLiteは小規模なWebツール、デスクトップアプリ、計測端末、CAD補助ツールなどで扱いやすいデータベースです。ただし、接続が複数になると「同時に読める」と「同時に書ける」を混同しやすくなります。
代表的な事故は、二つの処理が同じ部品レコードを読み、どちらも在庫数を計算してから更新するケースです。後から確定した更新が先の結果を上書きすると、SQL自体は成功しても業務上の変更が消えます。また、書き込み競合をすべて再試行すると、同じ要求を二重適用する危険もあります。
SQLiteの同時更新設計では、次の四つを分けて考えることが重要です。
- transactionをいつ開始し、どこまでを一単位にするか
- lock待ちを何秒許し、どのエラーだけを再試行するか
- 古い版を更新しようとしていないか
- 同じ要求が再送されても結果を一度だけ適用できるか
SQLiteのtransactionとlockを理解する
同時に書ける接続は一つ
SQLiteでは複数のread transactionを同時に開けますが、保留中の変更を書き込めるwrite transactionは一度に一つです。したがって、WALモードにすれば「何接続でも同時更新できる」わけではありません。
WALモードでは変更をWALファイルへ追記するため、readerとwriterは並行して動作しやすくなります。一方、WALファイルは一つなのでwriterは一つです。長時間のread transactionはcheckpointの進行を止め、WALを大きくすることもあります。
DEFERRED・IMMEDIATE・EXCLUSIVEの違い
BEGINだけを実行した場合はBEGIN DEFERREDです。最初のアクセスがSELECTならread transactionとして始まり、後の更新時にwrite transactionへ昇格します。その時点で別のwriterが存在するとSQLITE_BUSYになり得ます。
BEGIN IMMEDIATEは、transaction開始時にwrite transactionを確保しようとします。競合していれば入口でSQLITE_BUSYとなるため、「読んだ後の更新で突然競合する」範囲を小さくできます。読み取り、検証、更新を一続きに行う短い処理に向いています。
BEGIN EXCLUSIVEはrollback journalモードでは他接続の読み取りも妨げます。WALモードではIMMEDIATEと同じ挙動になるため、通常の業務更新で無条件に選ぶ理由はありません。
更新データの契約を先に決める
例として、部品マスターの数量と名称を更新します。APIや画面から受け取る最小入力は次の形です。
{
"operation_id": "01JB2Z8Q4J7N9A6K3M5P2R8T0V",
"part_id": "P-1001",
"expected_revision": 7,
"name": "取付ブラケット",
"quantity": 24.5,
"unit": "pcs",
"updated_at": "2026-10-25T04:30:00Z"
}part_idは更新対象を示すID、expected_revisionは利用者が読んだ版、operation_idは一回の要求を識別するIDです。quantityは数値、unitは単位コード、updated_atはUTCのRFC 3339文字列として契約します。
成功時の出力には、更新後のrevisionと、要求が新規適用されたか再送だったかを含めます。版不一致はlock競合ではなく業務競合なので、待って再試行するのではなく、最新データを再取得して利用者に差分を判断してもらいます。
tableで整合性を支える
レコード本体と処理済み要求を同じデータベースに保存します。
CREATE TABLE parts (
part_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
quantity REAL NOT NULL CHECK (quantity >= 0),
unit TEXT NOT NULL CHECK (unit IN ('pcs', 'kg', 'm')),
revision INTEGER NOT NULL DEFAULT 1 CHECK (revision >= 1),
updated_at TEXT NOT NULL
) STRICT;
CREATE TABLE mutation_log (
operation_id TEXT PRIMARY KEY,
part_id TEXT NOT NULL REFERENCES parts(part_id),
payload_hash TEXT NOT NULL,
result_revision INTEGER NOT NULL,
created_at TEXT NOT NULL
) STRICT;partsの一行が一つのrecordで、各columnがfieldとtypeを定義します。mutation_logではoperation_idをPRIMARY KEYにし、同じ要求の二重記録をデータベース側でも拒否します。
payload_hashを保存するのは、同じoperation_idで異なる内容が届いたときに、成功済みとして誤認しないためです。同一ID・同一hashなら保存済み結果を返し、同一ID・異なるhashなら要求の衝突として拒否します。
NULL・数値・日時の落とし穴
NULLと空文字は別の値です。名称を消去できない仕様なら、空文字も入力検証で拒否します。REALは二進浮動小数点なので、金額や厳密な寸法には不向きです。小数桁が固定なら最小単位の整数、または正規化した10進文字列を検討します。
日時はTEXTへ無秩序に保存せず、UTCのRFC 3339形式に統一します。画面表示時だけ利用者のtimezoneへ変換します。IDは表示名から作らず、変更されない識別子として扱います。
入力から確定までの流れ
flowchart TD
A[入力と型を検証] --> B[BEGIN IMMEDIATE]
B --> C{operation_idは記録済みか}
C -->|はい| D[保存済み結果を返す]
C -->|いいえ| E[版を確認して更新]
E --> F[要求記録とCOMMIT]transactionを開く前に、JSONの必須key、type、単位、文字列長、日時形式など、データベースを必要としない検証を終えます。transaction内で外部APIを呼んだり、大きなファイルを解析したりしてはいけません。write transactionが長くなり、他のwriterを待たせるからです。
transaction内では、処理済み要求の確認、現在版の確認、条件付き更新、処理履歴の追加を行います。これらを一つのCOMMITで確定すれば、部品だけ更新されて履歴が残らない中間状態を避けられます。
Pythonで最小実装する
次の例は、暗黙transactionを無効にしてSQLでBEGIN IMMEDIATEを明示します。timeoutとPRAGMA busy_timeoutはlock待ち時間であり、処理全体の再試行回数ではありません。
from __future__ import annotations
import hashlib
import json
import random
import sqlite3
import time
from pathlib import Path
class StaleRevision(Exception):
pass
def payload_hash(payload: dict) -> str:
canonical = json.dumps(
payload,
ensure_ascii=False,
sort_keys=True,
separators=(",", ":"),
).encode("utf-8")
return hashlib.sha256(canonical).hexdigest()
def open_connection(db_path: Path) -> sqlite3.Connection:
conn = sqlite3.connect(db_path, timeout=2.0, isolation_level=None)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON")
conn.execute("PRAGMA busy_timeout = 2000")
return conn
def is_busy(exc: sqlite3.OperationalError) -> bool:
code = getattr(exc, "sqlite_errorcode", None)
return code is not None and (code & 0xFF) == sqlite3.SQLITE_BUSY
def update_part(db_path: Path, payload: dict, max_attempts: int = 4) -> dict:
digest = payload_hash(payload)
for attempt in range(max_attempts):
conn = open_connection(db_path)
try:
conn.execute("BEGIN IMMEDIATE")
done = conn.execute(
"""
SELECT payload_hash, result_revision
FROM mutation_log
WHERE operation_id = ?
""",
(payload["operation_id"],),
).fetchone()
if done is not None:
if done["payload_hash"] != digest:
raise ValueError("operation_id is already used")
conn.execute("COMMIT")
return {"revision": done["result_revision"], "replayed": True}
cursor = conn.execute(
"""
UPDATE parts
SET name = ?, quantity = ?, unit = ?,
revision = revision + 1, updated_at = ?
WHERE part_id = ? AND revision = ?
""",
(
payload["name"],
payload["quantity"],
payload["unit"],
payload["updated_at"],
payload["part_id"],
payload["expected_revision"],
),
)
if cursor.rowcount != 1:
raise StaleRevision("part does not exist or revision is stale")
new_revision = payload["expected_revision"] + 1
conn.execute(
"""
INSERT INTO mutation_log (
operation_id, part_id, payload_hash,
result_revision, created_at
) VALUES (?, ?, ?, ?, ?)
""",
(
payload["operation_id"],
payload["part_id"],
digest,
new_revision,
payload["updated_at"],
),
)
conn.execute("COMMIT")
return {"revision": new_revision, "replayed": False}
except sqlite3.OperationalError as exc:
if conn.in_transaction:
conn.execute("ROLLBACK")
if not is_busy(exc) or attempt == max_attempts - 1:
raise
delay = 0.05 * (2 ** attempt) + random.uniform(0, 0.03)
time.sleep(delay)
except Exception:
if conn.in_transaction:
conn.execute("ROLLBACK")
raise
finally:
conn.close()
raise RuntimeError("unreachable")sqlite_errorcodeはPython 3.11以降でSQLite由来の例外に付与されます。extended error codeを受け取る可能性があるため、下位8bitをSQLITE_BUSYと比較しています。文字列"database is locked"だけに依存すると、実装やlocaleの差に弱くなります。
Python 3.12以降ではConnection.autocommitによるtransaction制御が推奨されています。ただし、BEGIN IMMEDIATEをSQLで明示する例では、isolation_level=Noneにより暗黙開始を止め、開始・確定・取消の責任をコード上で一か所に集めています。
busy timeoutと再試行を混同しない
busy timeoutは、lockを取得できない接続が一定時間待つ仕組みです。永久待機ではなく、時間内に空かなければSQLITE_BUSYを返します。再試行を実装する場合も、回数と総待ち時間に上限を設けます。
再試行してよいのは、一時的なlock競合で、かつ要求が冪等に設計されている場合です。次のエラーを同じ入力のまま再試行してはいけません。
NOT NULL、CHECK、FOREIGN KEYなどの制約違反- 版番号の不一致
- 同じ
operation_idに異なるpayloadを割り当てた衝突 - 構文エラー、権限不足、ディスク容量不足、破損
指数backoffへ小さな乱数を加えると、複数workerが同じ間隔で再衝突する可能性を下げられます。それでも恒常的に競合するなら、待ち時間を増やす前にtransactionを短くし、書き込みを一つのqueueへ集約する構成を検討します。
WALを有効にすれば終わりではない
WALモードはreaderとwriterの並行性を改善しますが、writer同士を並列化しません。PRAGMA journal_mode = WALは導入時に設定し、実際の戻り値を確認します。また、WALのshared memoryを利用するため、ネットワークファイルシステムへ安易に置く構成には向きません。
checkpointはWALの内容を本体へ戻す処理です。長いread transactionがあると途中で進めなくなるため、接続やcursorを必要以上に保持しない設計が必要です。WALファイルの増加、checkpoint結果、transaction時間、SQLITE_BUSY回数を運用指標にします。
正常系と異常系を試験する
同時更新は単体試験だけでは見落としやすいため、別々の接続を使って競合を再現します。
- 接続Aが
BEGIN IMMEDIATE中に、接続Bが所定のtimeout後にSQLITE_BUSYになる - AのCOMMIT後、Bの再試行が成功する
- 同じ
operation_idとpayloadの再送で数量が二重更新されない - 同じ
operation_idと異なるpayloadが拒否される - 古い
expected_revisionが業務競合として拒否される - 更新途中の例外で
partsとmutation_logが両方rollbackされる - 長時間readerがある状況でWALとcheckpointを監視できる
失敗時には秘密情報やpayload全文をlogへ残さず、operation_id、対象ID、試行回数、SQLite error code、経過時間など、調査に必要な項目だけを構造化して記録します。
DB・API・CAD連携へ再利用する
この設計は部品マスターだけでなく、BOMの版更新、図面属性の同期、計測値の取込、ローカルqueueの処理にも使えます。外部APIとSQLiteを一つのtransactionにはできないため、外部送信が必要ならoutbox tableへ送信予定を同時記録し、別workerが冪等に配送します。
CADデータでは、表示名ではなく不変の部品IDや図面IDを参照keyにします。数量や寸法には単位を持たせ、改訂番号と更新時刻を混同しません。revisionは順序ある競合検知、updated_atは監査用時刻として役割を分けます。
まとめ
SQLiteの同時更新で重要なのは、lockを消すことではなく、競合する場所と失敗時の契約を明確にすることです。短いBEGIN IMMEDIATEで更新権を入口で確保し、busy_timeoutと上限付き再試行で一時競合へ対処します。
さらに、revisionによる条件付き更新で上書きを検知し、operation_idと処理履歴で再送を一度だけ適用します。WALはreaderとwriterの並行性を高めますが、writerは一つであり、checkpointと長時間readerの監視も必要です。transaction境界、型、ID、単位、日時、再試行条件までをデータ契約に含めることで、SQLiteをDB・API・自動化・CAD連携へ安全に再利用できます。
