Two scripts shared one JSON file and a write silently vanished. Here is the SQLite fix

#python #programming #tutorial #beginners

I had two Python scripts updating the same state.json file: a running total of API spend. Every so often the total came out lower than the real spend. No error, no crash, just a number that was quietly wrong.

The cause is a classic lost update, and it takes about 15 lines to reproduce and fix.

The broken version

import json, os

PATH = "state.json"

def load():
    if not os.path.exists(PATH):
        return {"spent": 0.0}
    with open(PATH) as f:
        return json.load(f)

def save(state):
    with open(PATH, "w") as f:
        json.dump(state, f)

def charge(amount, cap=25.0):
    state = load()                      # 1. read
    if state["spent"] + amount > cap:   # 2. check
        return False
    state["spent"] += amount            # 3. modify
    save(state)                         # 4. write
    return True

Run charge(0.10) from two processes at once. Both read spent = 5.00, both decide it is fine, both write 5.10. Two charges happened, one was recorded. The gap between step 1 and step 4 is where the data gets lost.

A second problem: if the process crashes between the read and the write, or the write is cut off halfway, you can end up with a half-written, unparseable file.

The fix: make check-and-update one atomic statement

SQLite ships with Python (import sqlite3), needs no server, and does file locking for you. The trick is to put the check inside the UPDATE so the database does it in one step:

import sqlite3

con = sqlite3.connect("budget.db", timeout=30, isolation_level=None)
con.execute("""
    CREATE TABLE IF NOT EXISTS budget (
        id    INTEGER PRIMARY KEY CHECK (id = 1),
        spent REAL NOT NULL,
        cap   REAL NOT NULL
    )
""")
con.execute("INSERT OR IGNORE INTO budget VALUES (1, 0, 25.0)")

def reserve(amount):
    cur = con.execute(
        "UPDATE budget SET spent = spent + ? "
        "WHERE id = 1 AND spent + ? <= cap",
        (amount, amount),
    )
    return cur.rowcount == 1   # 1 row changed = reserved, 0 = over the cap

No read-then-write gap. The WHERE spent + ? <= cap clause is the check, and rowcount tells you whether it passed. Two processes calling reserve(0.10) at the same time will each get their own correct answer, because SQLite serializes writers.

Reserve before the call, not after

If you only record spend after an API call returns, failing calls that the provider still bills are never counted. Reserve the estimate first, and give it back only when you know the request never left:

def refund(amount):
    con.execute("UPDATE budget SET spent = MAX(spent - ?, 0) WHERE id = 1", (amount,))

def guarded_call(fn, estimate):
    if not reserve(estimate):
        raise RuntimeError("budget cap reached")
    try:
        return fn()
    except ConnectionError:
        refund(estimate)   # request never reached the server
        raise

Anything else (timeouts, 5xx, parse errors) stays charged, which is the safe default.

Quick test

from concurrent.futures import ThreadPoolExecutor

def worker(_):
    c = sqlite3.connect("budget.db", timeout=30, isolation_level=None)
    c.execute("UPDATE budget SET spent = spent + 0.1 WHERE id = 1 AND spent + 0.1 <= cap")

with ThreadPoolExecutor(8) as ex:
    list(ex.map(worker, range(100)))

print(con.execute("SELECT spent FROM budget").fetchone())  # ~10.0, every charge counted

Swap the same loop onto the JSON version and the total comes up short on most runs.

When a JSON file is still fine

If exactly one process ever writes the file, JSON is fine. Write to a temp file and os.replace() it so a crash cannot leave a half-written file. The moment a second writer appears, move to SQLite.

Sources

SQLite transactions and locking: https://www.sqlite.org/lockingv3.html
Python sqlite3 module: https://docs.python.org/3/library/sqlite3.html
os.replace (atomic rename): https://docs.python.org/3/library/os.html#os.replace

Have you been bitten by a lost update in a place you did not expect (config files, counters, caches)? What did you end up using to fix it?

DEV Community