SQLite 预留状态实战：最后一份菜与迟到付款怎样处理

import sqlite3
from contextlib import closing


def reserve(db, hold_id, qty, deadline):
    if type(qty) is not int or qty <= 0:
        raise ValueError("positive integer quantity required")
    with db:
        changed = db.execute(
            "UPDATE stock SET available=available-? "
            "WHERE sku=1 AND available>=?", (qty, qty)
        ).rowcount
        if changed != 1:
            return False
        db.execute("INSERT INTO hold VALUES (?, ?, 'held', ?)",
                   (hold_id, qty, deadline))
    return True


def release_due(db, hold_id, now):
    changed = db.execute(
        "UPDATE hold SET state='released' "
        "WHERE id=? AND state='held' AND deadline<=?",
        (hold_id, now)
    ).rowcount
    if changed:
        qty = db.execute("SELECT qty FROM hold WHERE id=?",
                         (hold_id,)).fetchone()[0]
        db.execute("UPDATE stock SET available=available+? WHERE sku=1",
                   (qty,))
    return bool(changed)


def expire(db, hold_id, now):
    with db:
        return release_due(db, hold_id, now)


def pay(db, payment_id, hold_id, amount_fen, now):
    # Assume signature, merchant and payment status were verified upstream.
    with db:
        previous = db.execute(
            "SELECT hold_id, amount_fen, result FROM payment WHERE id=?",
            (payment_id,)
        ).fetchone()
        if previous:
            if previous[:2] != (hold_id, amount_fen):
                raise ValueError("payment ID payload conflict")
            return previous[2]
        row = db.execute("SELECT qty, state FROM hold WHERE id=?",
                         (hold_id,)).fetchone()
        if row is None or type(amount_fen) is not int or amount_fen != row[0] * 100:
            raise ValueError("unknown hold or amount mismatch")
        if db.execute("SELECT 1 FROM payment WHERE hold_id=?",
                      (hold_id,)).fetchone():
            raise ValueError("another payment needs reconciliation")
        release_due(db, hold_id, now)
        changed = db.execute(
            "UPDATE hold SET state='confirmed' "
            "WHERE id=? AND state='held' AND deadline>?",
            (hold_id, now)
        ).rowcount
        result = "confirmed" if changed else "refund_pending"
        db.execute("INSERT INTO payment VALUES (?, ?, ?, ?)",
                   (payment_id, hold_id, amount_fen, result))
        return result


def main():
    with closing(sqlite3.connect(":memory:")) as db:
        db.executescript("""
        CREATE TABLE stock (sku INTEGER PRIMARY KEY, available INTEGER CHECK(available>=0));
        CREATE TABLE hold (id TEXT PRIMARY KEY, qty INTEGER CHECK(qty>0),
          state TEXT CHECK(state IN ('held','released','confirmed')), deadline INTEGER);
        CREATE TABLE payment (id TEXT PRIMARY KEY, hold_id TEXT UNIQUE,
          amount_fen INTEGER, result TEXT);
        INSERT INTO stock VALUES (1, 1);
        """)
        assert reserve(db, "dine-in", 1, 100)
        assert not reserve(db, "takeout", 1, 100)
        assert expire(db, "dine-in", 100)
        assert not expire(db, "dine-in", 101)
        assert pay(db, "p1", "dine-in", 100, 102) == "refund_pending"
        assert pay(db, "p1", "dine-in", 100, 103) == "refund_pending"
        assert db.execute("SELECT available FROM stock").fetchone()[0] == 1
        assert reserve(db, "takeout-2", 1, 200)
        assert pay(db, "p2", "takeout-2", 100, 150) == "confirmed"
        assert not expire(db, "takeout-2", 201)
        assert db.execute("SELECT available FROM stock").fetchone()[0] == 0
        assert db.execute("SELECT COUNT(*) FROM payment").fetchone()[0] == 2
        print("last portion: second reservation rejected")
        print("expiry release: exactly once")
        print("late payment: refund_pending, stock unchanged")
        print("timely payment: confirmed")
        print("payment records: 2")


if __name__ == "__main__":
    main()

python demo.py

last portion: second reservation rejected
expiry release: exactly once
late payment: refund_pending, stock unchanged
timely payment: confirmed
payment records: 2