第189回 重複防止だけでは更新順を守れない:PythonとSQLiteの商品別連番ゲート

同じevent_idの再送を拒否するだけでは、異なるイベントの到着順までは守れません。たとえば、絶対数量を表す更新が「連番11、在庫24」「連番12、在庫30」の順に作られても、受信順が逆なら最後に在庫24へ戻る可能性があります。

ここでは、商品ごとのsequenceを照合し、期待する次の連番だけを適用するCLIを作ります。欠番では更新せず、適用済みの同じイベントの再送には初回適用時の結果を返します。

連番は送信側が商品ごとに1から発行する前提です。発行元の認証、欠けたイベントの自動取得、外部システムとの分散トランザクションは扱いません。

判定規則

受信内容 判定 在庫更新
適用済みのevent_idと同じ内容 duplicate 更新せず初回適用時の結果を返す
適用済みのevent_idと異なる内容 event_id_conflict 更新しない
期待する次の連番 applied 絶対数量で置き換える
期待値より大きい連番 sequence_gap 更新しない
使用済み連番の別イベント sequence_conflict 更新しない
状態より古く、受信履歴にない連番 stale_sequence 更新しない。通常操作では到達せず、inboxの履歴が外部操作で削除された場合に備えた防御分岐

通常のCLI操作では、適用済みの全連番がinboxに残ります。そのため古い連番を別のイベントIDで送ると、先に使用済み連番の検査へ一致し、sequence_conflictになります。stale_sequenceは通常の運用結果ではなく、状態を残したまま履歴だけを削除した場合に古い更新を拒否するためのものです。

連番の範囲は商品ごとです。異なるSKUの同じ連番は独立して扱われます。

実行契約

項目 仕様
動作環境 Python 3.11以上、標準ライブラリのsqlite3
データベース このCLI専用のローカルSQLiteファイル。スキーマ版は1。接続ごとに消える:memory:は拒否する
標準入力 8,192バイト以下のUTF-8 JSONを1件。重複したJSONキーは拒否する
必須キー event_id、sku、sequence、stock_quantityの4件だけ
ID 英数字で始まり、英数字、ピリオド、アンダースコア、コロン、ハイフンで構成する1~64文字
連番 商品ごとに1から始め、1ずつ増やす。上限は9,000,000,000,000,000
在庫数量 0~1,000,000,000の整数。差分ではなく絶対数量
成功時 標準出力へ1行JSON
エラー時 標準エラー出力へ1行JSON。出力先そのものが故障した場合はJSON出力を保証しない
終了コード 意味
0 初期化、適用、適用済みイベントの再送、状態照会
64 引数または入力JSONの誤り
65 イベントIDまたは連番の競合、古い連番
70 未初期化DB、非対応または不一致のスキーマ、DB整合性エラー、SQLite由来のファイル・パスエラー、その他の内部エラー
74 標準入力の読み取り、または標準出力・標準エラー出力への書き込みで発生したOSError。出力先自体の故障時はJSONを出せない場合があるが、外側のガードが終了コード74を返す
75 欠番またはSQLiteの一時的なロック競合

DBファイルを開けない場合など、sqlite3が報告するファイル関連エラーはsqlite3.Errorとして処理するため終了コード70です。終了コード74はSQLiteのファイル操作全般を表すものではありません。標準出力または標準エラー出力そのものへの書き込みが失敗した場合は、外側のガードが74を返します。その場合、同じ故障した出力先へエラーJSONを書くことは保証しません。

1ファイルの連番ゲート

次のコードをordered_snapshot_gate.pyとして保存します。外部ライブラリは使いません。

#!/usr/bin/env python3
import argparse
import hashlib
import json
import re
import sqlite3
import sys

SCHEMA_VERSION = 1
MAX_INPUT_BYTES = 8192
MAX_SEQUENCE = 9_000_000_000_000_000
MAX_STOCK = 1_000_000_000
TOKEN_RE = re.compile(r"[A-Za-z0-9][A-Za-z0-9._:-]{0,63}")

SCHEMA = (
    """
    CREATE TABLE stream_state (
        sku TEXT PRIMARY KEY,
        last_sequence INTEGER NOT NULL
            CHECK (last_sequence BETWEEN 1 AND 9000000000000000)
    )
    """,
    """
    CREATE TABLE inventory (
        sku TEXT PRIMARY KEY,
        stock_quantity INTEGER NOT NULL
            CHECK (stock_quantity BETWEEN 0 AND 1000000000),
        FOREIGN KEY (sku) REFERENCES stream_state(sku)
    )
    """,
    """
    CREATE TABLE inbox (
        event_id TEXT PRIMARY KEY,
        payload_json TEXT NOT NULL,
        payload_sha256 TEXT NOT NULL
            CHECK (length(payload_sha256) = 64),
        sku TEXT NOT NULL,
        sequence INTEGER NOT NULL
            CHECK (sequence BETWEEN 1 AND 9000000000000000),
        previous_stock_quantity INTEGER
            CHECK (
                previous_stock_quantity IS NULL
                OR previous_stock_quantity BETWEEN 0 AND 1000000000
            ),
        stock_quantity INTEGER NOT NULL
            CHECK (stock_quantity BETWEEN 0 AND 1000000000),
        UNIQUE (sku, sequence),
        FOREIGN KEY (sku) REFERENCES stream_state(sku)
    )
    """,
)


class AppError(Exception):
    def __init__(self, error, exit_code, **details):
        super().__init__(error)
        self.error = error
        self.exit_code = exit_code
        self.details = details


class JsonArgumentParser(argparse.ArgumentParser):
    def error(self, message):
        raise AppError("usage_error", 64, message=message)


def emit(stream, payload):
    print(
        json.dumps(
            payload,
            ensure_ascii=True,
            sort_keys=True,
            separators=(",", ":"),
        ),
        file=stream,
        flush=True,
    )


def reject_constant(value):
    raise AppError("invalid_json_number", 64, value=value)


def parse_json_integer(value):
    try:
        return int(value)
    except ValueError:
        raise AppError("invalid_json_integer", 64) from None


def reject_duplicate_keys(pairs):
    result = {}
    for key, value in pairs:
        if key in result:
            raise AppError("duplicate_json_key", 64, key=key)
        result[key] = value
    return result


def validate_token(field, value):
    if not isinstance(value, str) or TOKEN_RE.fullmatch(value) is None:
        raise AppError("invalid_identifier", 64, field=field)
    return value


def validate_integer(field, value, minimum, maximum):
    if isinstance(value, bool) or not isinstance(value, int):
        raise AppError("invalid_integer", 64, field=field)
    if value < minimum or value > maximum:
        raise AppError(
            "integer_out_of_range",
            64,
            field=field,
            minimum=minimum,
            maximum=maximum,
        )
    return value


def read_event():
    raw = sys.stdin.buffer.read(MAX_INPUT_BYTES + 1)
    if not raw:
        raise AppError("empty_input", 64)
    if len(raw) > MAX_INPUT_BYTES:
        raise AppError(
            "input_too_large", 64, maximum_bytes=MAX_INPUT_BYTES
        )

    try:
        text = raw.decode("utf-8")
    except UnicodeDecodeError as exc:
        raise AppError(
            "invalid_utf8", 64, byte_offset=exc.start
        ) from None

    try:
        value = json.loads(
            text,
            object_pairs_hook=reject_duplicate_keys,
            parse_constant=reject_constant,
            parse_int=parse_json_integer,
        )
    except AppError:
        raise
    except json.JSONDecodeError as exc:
        raise AppError(
            "invalid_json", 64, line=exc.lineno, column=exc.colno
        ) from None
    except RecursionError:
        raise AppError("json_too_deep", 64) from None

    if not isinstance(value, dict):
        raise AppError("json_object_required", 64)

    required = {"event_id", "sku", "sequence", "stock_quantity"}
    actual = set(value)
    if actual != required:
        raise AppError(
            "invalid_fields",
            64,
            missing=sorted(required - actual),
            unexpected=sorted(actual - required),
        )

    return {
        "event_id": validate_token("event_id", value["event_id"]),
        "sku": validate_token("sku", value["sku"]),
        "sequence": validate_integer(
            "sequence", value["sequence"], 1, MAX_SEQUENCE
        ),
        "stock_quantity": validate_integer(
            "stock_quantity", value["stock_quantity"], 0, MAX_STOCK
        ),
    }


def canonical_payload(event):
    return json.dumps(
        event,
        ensure_ascii=True,
        sort_keys=True,
        separators=(",", ":"),
    )


def normalize_sql(sql):
    return " ".join(sql.lower().split())


def open_database(path):
    conn = sqlite3.connect(path, timeout=5.0, isolation_level=None)
    try:
        conn.execute("PRAGMA foreign_keys = ON")
    except Exception:
        conn.close()
        raise
    return conn


def database_version(conn):
    return int(conn.execute("PRAGMA user_version").fetchone()[0])


def validate_schema(conn):
    expected = {}
    for statement in SCHEMA:
        name = re.search(
            r"create\s+table\s+([a-z_][a-z0-9_]*)",
            statement,
            re.IGNORECASE,
        ).group(1)
        expected[name] = normalize_sql(statement)

    rows = conn.execute(
        "SELECT type, name, sql FROM sqlite_master "
        "WHERE name NOT LIKE 'sqlite_%'"
    ).fetchall()
    actual = {
        name: normalize_sql(sql)
        for object_type, name, sql in rows
        if object_type == "table" and sql is not None
    }
    unexpected_objects = sorted(
        f"{object_type}:{name}"
        for object_type, name, _ in rows
        if object_type != "table"
    )

    missing = sorted(set(expected) - set(actual))
    unexpected = sorted(set(actual) - set(expected))
    changed = sorted(
        name
        for name in set(expected) & set(actual)
        if expected[name] != actual[name]
    )
    if missing or unexpected or changed or unexpected_objects:
        raise AppError(
            "database_schema_mismatch",
            70,
            missing_tables=missing,
            unexpected_tables=unexpected,
            changed_tables=changed,
            unexpected_objects=unexpected_objects,
        )


def require_schema(conn):
    version = database_version(conn)
    if version == 0:
        raise AppError(
            "database_not_initialized",
            70,
            expected_schema_version=SCHEMA_VERSION,
        )
    if version != SCHEMA_VERSION:
        raise AppError(
            "unsupported_schema_version",
            70,
            expected_schema_version=SCHEMA_VERSION,
            found_schema_version=version,
        )
    validate_schema(conn)


def initialize_database(path):
    conn = open_database(path)
    try:
        conn.execute("BEGIN IMMEDIATE")
        version = database_version(conn)

        if version == SCHEMA_VERSION:
            validate_schema(conn)
            conn.commit()
            return {
                "status": "already_initialized",
                "database": path,
                "schema_version": SCHEMA_VERSION,
            }

        if version != 0:
            raise AppError(
                "unsupported_schema_version",
                70,
                expected_schema_version=SCHEMA_VERSION,
                found_schema_version=version,
            )

        existing_objects = [
            row[0]
            for row in conn.execute(
                "SELECT name FROM sqlite_master "
                "WHERE name NOT LIKE 'sqlite_%'"
            )
        ]
        if existing_objects:
            raise AppError(
                "unversioned_database_not_empty",
                70,
                objects=sorted(existing_objects),
            )

        for statement in SCHEMA:
            conn.execute(statement)
        conn.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
        validate_schema(conn)
        conn.commit()
        return {
            "status": "initialized",
            "database": path,
            "schema_version": SCHEMA_VERSION,
        }
    except BaseException:
        if conn.in_transaction:
            conn.rollback()
        raise
    finally:
        conn.close()


def receive_event(path, event):
    payload_json = canonical_payload(event)
    payload_sha256 = hashlib.sha256(
        payload_json.encode("ascii")
    ).hexdigest()

    conn = open_database(path)
    try:
        require_schema(conn)
        conn.execute("BEGIN IMMEDIATE")

        existing = conn.execute(
            """
            SELECT payload_json, payload_sha256, sku, sequence,
                   previous_stock_quantity, stock_quantity
            FROM inbox
            WHERE event_id = ?
            """,
            (event["event_id"],),
        ).fetchone()

        if existing is not None:
            if existing[0] != payload_json:
                raise AppError(
                    "event_id_conflict",
                    65,
                    event_id=event["event_id"],
                    stored_payload_sha256=existing[1],
                    received_payload_sha256=payload_sha256,
                )
            conn.commit()
            return {
                "status": "duplicate",
                "event_id": event["event_id"],
                "sku": existing[2],
                "sequence": existing[3],
                "previous_stock_quantity": existing[4],
                "stock_quantity": existing[5],
            }

        used_sequence = conn.execute(
            """
            SELECT event_id, payload_sha256
            FROM inbox
            WHERE sku = ? AND sequence = ?
            """,
            (event["sku"], event["sequence"]),
        ).fetchone()
        if used_sequence is not None:
            raise AppError(
                "sequence_conflict",
                65,
                sku=event["sku"],
                sequence=event["sequence"],
                accepted_event_id=used_sequence[0],
                accepted_payload_sha256=used_sequence[1],
            )

        state = conn.execute(
            "SELECT last_sequence FROM stream_state WHERE sku = ?",
            (event["sku"],),
        ).fetchone()

        if state is None:
            last_sequence = 0
            previous_quantity = None
        else:
            last_sequence = int(state[0])
            inventory = conn.execute(
                "SELECT stock_quantity FROM inventory WHERE sku = ?",
                (event["sku"],),
            ).fetchone()
            if inventory is None:
                raise AppError(
                    "database_inconsistent",
                    70,
                    sku=event["sku"],
                    missing="inventory",
                )
            previous_quantity = int(inventory[0])

        expected_sequence = last_sequence + 1
        if event["sequence"] > expected_sequence:
            raise AppError(
                "sequence_gap",
                75,
                event_id=event["event_id"],
                sku=event["sku"],
                expected_sequence=expected_sequence,
                received_sequence=event["sequence"],
            )
        if event["sequence"] < expected_sequence:
            raise AppError(
                "stale_sequence",
                65,
                event_id=event["event_id"],
                sku=event["sku"],
                last_sequence=last_sequence,
                received_sequence=event["sequence"],
            )

        if state is None:
            conn.execute(
                "INSERT INTO stream_state(sku, last_sequence) VALUES (?, ?)",
                (event["sku"], event["sequence"]),
            )
            conn.execute(
                "INSERT INTO inventory(sku, stock_quantity) VALUES (?, ?)",
                (event["sku"], event["stock_quantity"]),
            )
        else:
            conn.execute(
                "UPDATE stream_state SET last_sequence = ? WHERE sku = ?",
                (event["sequence"], event["sku"]),
            )
            conn.execute(
                "UPDATE inventory SET stock_quantity = ? WHERE sku = ?",
                (event["stock_quantity"], event["sku"]),
            )

        conn.execute(
            """
            INSERT INTO inbox(
                event_id, payload_json, payload_sha256, sku, sequence,
                previous_stock_quantity, stock_quantity
            )
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                event["event_id"],
                payload_json,
                payload_sha256,
                event["sku"],
                event["sequence"],
                previous_quantity,
                event["stock_quantity"],
            ),
        )
        conn.commit()
        return {
            "status": "applied",
            "event_id": event["event_id"],
            "sku": event["sku"],
            "sequence": event["sequence"],
            "previous_stock_quantity": previous_quantity,
            "stock_quantity": event["stock_quantity"],
        }
    except BaseException:
        if conn.in_transaction:
            conn.rollback()
        raise
    finally:
        conn.close()


def show_state(path, sku):
    conn = open_database(path)
    try:
        require_schema(conn)
        state = conn.execute(
            """
            SELECT s.last_sequence, i.stock_quantity
            FROM stream_state AS s
            LEFT JOIN inventory AS i ON i.sku = s.sku
            WHERE s.sku = ?
            """,
            (sku,),
        ).fetchone()
        if state is None:
            return {"status": "not_found", "sku": sku}
        if state[1] is None:
            raise AppError(
                "database_inconsistent",
                70,
                sku=sku,
                missing="inventory",
            )
        return {
            "status": "found",
            "sku": sku,
            "last_sequence": int(state[0]),
            "stock_quantity": int(state[1]),
        }
    finally:
        conn.close()


def build_parser():
    parser = JsonArgumentParser()
    commands = parser.add_subparsers(dest="command", required=True)

    init_command = commands.add_parser("init")
    init_command.add_argument("--db", required=True)

    receive_command = commands.add_parser("receive")
    receive_command.add_argument("--db", required=True)

    show_command = commands.add_parser("show")
    show_command.add_argument("--db", required=True)
    show_command.add_argument("--sku", required=True)
    return parser


def run_main():
    try:
        args = build_parser().parse_args()
        if args.db == "":
            raise AppError("empty_database_path", 64)
        if args.db == ":memory:":
            raise AppError("in_memory_database_not_supported", 64)

        if args.command == "init":
            result = initialize_database(args.db)
        elif args.command == "receive":
            result = receive_event(args.db, read_event())
        else:
            result = show_state(
                args.db,
                validate_token("sku", args.sku),
            )

        emit(sys.stdout, result)
        return 0
    except AppError as exc:
        payload = {"error": exc.error}
        payload.update(exc.details)
        emit(sys.stderr, payload)
        return exc.exit_code
    except sqlite3.Error as exc:
        code = getattr(exc, "sqlite_errorcode", None)
        primary_code = code & 0xFF if isinstance(code, int) else None
        if primary_code in {5, 6}:
            emit(sys.stderr, {"error": "database_busy"})
            return 75
        emit(
            sys.stderr,
            {"error": "database_error", "message": str(exc)},
        )
        return 70
    except OSError as exc:
        emit(
            sys.stderr,
            {"error": "io_error", "message": str(exc)},
        )
        return 74
    except Exception as exc:
        emit(
            sys.stderr,
            {
                "error": "internal_error",
                "exception_type": type(exc).__name__,
            },
        )
        return 70


def main():
    try:
        return run_main()
    except OSError:
        return 74


if __name__ == "__main__":
    sys.exit(main())

user_versionだけでは初期化済みと判定しません。validate_schemaは、必要な3テーブルが存在し、列、主キー、外部キー、UNIQUE、CHECKを含む作成SQLがこのCLIのスキーマと一致することを確認します。版が1でもテーブルが欠けていたり定義が変更されていたりすれば、database_schema_mismatchとして終了コード70を返します。

この検査は、このCLIが専有するDBを前提にした厳密一致です。同じ意味でも手作業で定義を書き換えたDBは受け入れません。必要な3テーブル以外のユーザー定義テーブル、ビュー、トリガー、インデックスも不一致として拒否します。

isolation_level=Noneで暗黙のトランザクション開始を無効にし、書き込み処理を明示的なBEGIN IMMEDIATE、COMMIT、ROLLBACKで囲んでいます。SQLiteのトランザクション説明にあるとおり、BEGIN IMMEDIATEは書き込みトランザクションを直ちに開始します。別の書き込みトランザクションがある場合、このコードはsqlite3.connectのtimeoutで指定した5秒までロックの解消を待ちます。

イベントの同一性は、キー順と空白を固定したJSON文字列で比較します。SHA-256は競合内容を照合するための値であり、同一性をハッシュだけでは判定しません。

欠番時に更新されないことを確認する

新しい作業ディレクトリで、ordered.dbが存在しない状態から順に実行します。

1. 初期化

python3 ordered_snapshot_gate.py init --db ordered.db
{"database":"ordered.db","schema_version":1,"status":"initialized"}

2. 連番1を適用

printf '%s' '{"event_id":"evt-001","sku":"SKU-001","sequence":1,"stock_quantity":10}' | python3 ordered_snapshot_gate.py receive --db ordered.db
{"event_id":"evt-001","previous_stock_quantity":null,"sequence":1,"sku":"SKU-001","status":"applied","stock_quantity":10}

3. 連番2より先に連番3を送信

printf '%s' '{"event_id":"evt-003","sku":"SKU-001","sequence":3,"stock_quantity":12}' | python3 ordered_snapshot_gate.py receive --db ordered.db
echo $?

エラーJSONは標準エラー出力へ出て、終了コードは75になります。

{"error":"sequence_gap","event_id":"evt-003","expected_sequence":2,"received_sequence":3,"sku":"SKU-001"}
75

欠番時はinbox、連番状態、在庫のどれも更新されません。

python3 ordered_snapshot_gate.py show --db ordered.db --sku SKU-001
{"last_sequence":1,"sku":"SKU-001","status":"found","stock_quantity":10}

4. 連番2を適用してから連番3を再送

printf '%s' '{"event_id":"evt-002","sku":"SKU-001","sequence":2,"stock_quantity":7}' | python3 ordered_snapshot_gate.py receive --db ordered.db
printf '%s' '{"event_id":"evt-003","sku":"SKU-001","sequence":3,"stock_quantity":12}' | python3 ordered_snapshot_gate.py receive --db ordered.db
{"event_id":"evt-002","previous_stock_quantity":10,"sequence":2,"sku":"SKU-001","status":"applied","stock_quantity":7}
{"event_id":"evt-003","previous_stock_quantity":7,"sequence":3,"sku":"SKU-001","status":"applied","stock_quantity":12}

適用済みの同一イベントには初回結果を返す

連番3まで進んだ後で、連番1のイベントを同じ内容で再送します。

printf '%s' '{"event_id":"evt-001","sku":"SKU-001","sequence":1,"stock_quantity":10}' | python3 ordered_snapshot_gate.py receive --db ordered.db
{"event_id":"evt-001","previous_stock_quantity":null,"sequence":1,"sku":"SKU-001","status":"duplicate","stock_quantity":10}

ここで返る在庫10は、イベントを初回適用した時点の結果であり、現在値ではありません。現在の状態はshowで確認します。

python3 ordered_snapshot_gate.py show --db ordered.db --sku SKU-001
{"last_sequence":3,"sku":"SKU-001","status":"found","stock_quantity":12}

重複判定を連番判定より先に行うため、コミット後に応答だけが失われた場合も、在庫を再更新せず初回結果を返せます。

導入時の制約

  • 連番の発行は送信側の責任です。 複数の送信元が同じ商品へ独立に採番すると競合します。
  • 欠番イベントは保存しません。 配送側がイベントを保持し、欠けた連番の適用後に後続を再送する必要があります。
  • 永久欠番は自動解消しません。 送信側が番号だけを消費してイベントを失うと、その商品の後続更新は適用できません。
  • inbox履歴を削除しないでください。 削除すると正確な再送結果を返せず、通常は使われないstale_sequence分岐へ到達する状態を作ります。
  • 保証は単一SQLite DB内に限られます。 外部APIや別DBの更新を同じコミットには含められません。
  • :memory:は使用できません。 コマンドをまたぐと同じDBを開けないため、引数エラーとして拒否します。
  • 書き込みはSKUをまたいで直列化されます。 SQLiteで同時に実行できる書き込みトランザクションは1つです。
  • 認証や署名検証は行いません。 入力元の認証と権限制御は別途必要です。

導入前に確認すべき具体的な一点は、送信側が商品単位の連番をどこで確定し、欠番時にどのイベントを再送できるかです。これが決まっていなければ、受信側の連番検査を追加しても永久欠番で処理が止まります。