research/sqlite-upsert-returning-counter-semantics

SQLite UPSERT with RETURNING: Building an Atomic Counter You Can Trust

Engineering Note · October 22, 2026 · 7 minute read · SQLite · Cloudflare D1 · Rate limiting

An atomic counter in SQLite is a single INSERT ... ON CONFLICT DO UPDATE ... RETURNING statement: it creates the row if needed, increments it, and hands back the new value without letting another writer slip in between. We use it for a daily AI budget, and this note shows what it does, what the naive alternative does under contention, and how we built the two-level check on top.

Everything here ran on SQLite 3.45.1 through Python 3.11’s sqlite3 module in a two-core Linux sandbox. RETURNING needs SQLite 3.35 or later, so that version is sufficient with no extra install. D1 is built on SQLite, so the SQL semantics carry over. We make no claim about D1’s internal concurrency model or performance, and concurrency behavior on D1 itself should be tested on D1.

What does the upsert return on each call?

import sqlite3
db = sqlite3.connect(":memory:")
print("sqlite", sqlite3.sqlite_version)
db.execute("""CREATE TABLE ai_usage (
  day TEXT NOT NULL, key TEXT NOT NULL, calls INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (day, key))""")
SQL = """INSERT INTO ai_usage (day, key, calls) VALUES (?, ?, 1)
ON CONFLICT(day, key) DO UPDATE SET calls = calls + 1
RETURNING calls"""
for i in range(5):
    print("call", i + 1, "->", db.execute(SQL, ("2000-01-01", "abc")).fetchone()[0])
print("other key ->", db.execute(SQL, ("2000-01-01", "xyz")).fetchone()[0])
print("next day  ->", db.execute(SQL, ("2000-01-02", "abc")).fetchone()[0])
sqlite 3.45.1
call 1 -> 1
call 2 -> 2
call 3 -> 3
call 4 -> 4
call 5 -> 5
other key -> 1
next day  -> 1

The first call inserts and returns 1. Later calls hit the primary key conflict, run the update branch, and return the incremented value. A new key or a new day starts at 1. The caller never needs to know whether the row existed.

What does read-then-write lose under contention?

The obvious alternative is two statements: SELECT calls, then UPDATE ... SET calls = n + 1. We ran three variants with 8 threads, each on its own connection to the same on-disk file, 500 increments per thread, so the correct final value is 4,000. Each variant ran 10 times, with a 30-second busy timeout and a barrier so threads start together. We set isolation_level=None so Python does not open transactions for us.

import sqlite3, sys, threading, os, tempfile

N_THREADS, N_ITERS = 8, 500
EXPECTED = N_THREADS * N_ITERS
UPSERT = """INSERT INTO c (k, calls) VALUES ('k', 1)
ON CONFLICT(k) DO UPDATE SET calls = calls + 1 RETURNING calls"""

def connect(path):
    return sqlite3.connect(path, timeout=30, isolation_level=None)

def naive(db):  # read-then-write, each statement autocommitted
    n = db.execute("SELECT calls FROM c WHERE k='k'").fetchone()[0]
    db.execute("UPDATE c SET calls = ? WHERE k='k'", (n + 1,))

def naive_txn(db):  # same logic inside a deferred transaction
    db.execute("BEGIN")
    try:
        n = db.execute("SELECT calls FROM c WHERE k='k'").fetchone()[0]
        db.execute("UPDATE c SET calls = ? WHERE k='k'", (n + 1,))
        db.execute("COMMIT")
    except sqlite3.OperationalError:
        db.execute("ROLLBACK")
        raise

def atomic(db):
    db.execute(UPSERT).fetchone()

def run(fn):
    path = os.path.join(tempfile.mkdtemp(), "t.db")
    setup = connect(path)
    setup.execute("CREATE TABLE c (k TEXT PRIMARY KEY, calls INTEGER NOT NULL)")
    setup.execute("INSERT INTO c VALUES ('k', 0)")
    errors = [0] * N_THREADS
    barrier = threading.Barrier(N_THREADS)
    def worker(i):
        db = connect(path)
        barrier.wait()
        for _ in range(N_ITERS):
            try:
                fn(db)
            except sqlite3.OperationalError:
                errors[i] += 1
        db.close()
    ts = [threading.Thread(target=worker, args=(i,)) for i in range(N_THREADS)]
    [t.start() for t in ts]; [t.join() for t in ts]
    return setup.execute("SELECT calls FROM c").fetchone()[0], sum(errors)

if __name__ == "__main__":
    print(f"sqlite {sqlite3.sqlite_version}, threads={N_THREADS}, iters/thread={N_ITERS}, expected={EXPECTED}")
    for name, fn in (("naive_autocommit", naive), ("naive_deferred_txn", naive_txn), ("atomic_upsert", atomic)):
        rows = [run(fn) for _ in range(10)]
        finals = [r[0] for r in rows]; errs = [r[1] for r in rows]
        print(f"{name:20s} finals={finals}")
        print(f"{'':20s} lost(min..max)={EXPECTED-max(finals)}..{EXPECTED-min(finals)} errors(min..max)={min(errs)}..{max(errs)}")
sqlite 3.45.1, threads=8, iters/thread=500, expected=4000
naive_autocommit     finals=[1195, 1000, 500, 1000, 1980, 501, 1000, 500, 500, 500]
                     lost(min..max)=2020..3500 errors(min..max)=0..0
naive_deferred_txn   finals=[3975, 3994, 2709, 2871, 3312, 3678, 2762, 2061, 844, 3222]
                     lost(min..max)=6..3156 errors(min..max)=6..3156
atomic_upsert        finals=[4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000]
                     lost(min..max)=0..0 errors(min..max)=0..0
VariantFinal counter (10 runs)Lost incrementsErrors raised
Read-then-write, autocommit500 to 1,9802,020 to 3,5000
Read-then-write, deferred transaction844 to 3,9946 to 3,1566 to 3,156
Atomic upsert with RETURNING4,000 every run00

Two different failures appear. The autocommit version loses updates silently: it raised no error, and the counter simply came out wrong. Final values cluster at multiples of 500 because the barrier keeps threads in near lockstep, so whole rounds of increments overwrite each other. The deferred-transaction version loses nothing silently, since lost equals errors in every run, but it fails loudly and often. Two connections that both read and then try to write collide, and SQLite returns database is locked immediately, ignoring the busy timeout:

OperationalError: database is locked after 0.000s (busy timeout is 30s)

That is by design, because waiting could never resolve the deadlock. The upsert avoids both problems because there is no gap between read and write to fight over.

We reran the whole script across separate invocations. Across 30 runs of the autocommit variant, finals ranged from 500 to 2,212. Across 20 runs of the deferred transaction, they ranged from 844 to 3,994. Across 20 runs of the upsert, every final was 4,000. Treat the exact losses as scheduler-dependent. The zero for the upsert is the result that matters.

How does the two-level budget check work?

Our design counts per visitor first, and only increments the global counter if the visitor is within limit. When the global counter returns 1, it is the first call of the day, so we prune old rows. Any database error denies the request.

import sqlite3

UPSERT = """INSERT INTO ai_usage (day, key, calls) VALUES (?, ?, 1)
ON CONFLICT(day, key) DO UPDATE SET calls = calls + 1 RETURNING calls"""

def check_budget(db, day, visitor, visitor_limit, global_limit):
    """Return (allowed, reason). Fails closed: any DB error means deny."""
    try:
        v = db.execute(UPSERT, (day, visitor)).fetchone()[0]
        if v > visitor_limit:
            return False, "visitor_limit"
        g = db.execute(UPSERT, (day, "*")).fetchone()[0]
        if g == 1:  # first call of the day: prune rows older than 7 days
            db.execute("DELETE FROM ai_usage WHERE day < date(?, '-7 days')", (day,))
        if g > global_limit:
            return False, "global_limit"
        return True, "ok"
    except sqlite3.Error:
        return False, "meter_error"

We drove it with a table of cases. Each row gives the limits, a sequence of visitors, and the expected outcome per call, where . is allowed, V is denied by the visitor limit, and G by the global limit. The script asserts every row.

case                        v   g  seq     got     want      *  pass
at visitor limit            3 100  AAAA    ...V    ...V      3  True
over visitor limit x2       3 100  AAAAA   ...VV   ...VV     3  True
other visitor unaffected    3 100  AAAAB   ...V.   ...V.     4  True
at global limit            10   4  ABCDE   ....G   ....G     5  True
denied still counted       10   4  ABCDEF  ....GG  ....GG    6  True
V denial spares global      1   3  AAAABC  .VVV..  .VVV..    3  True
both limits bite            2   3  AAABC   ..V.G   ..V.G     4  True
limit of zero               0 100  A       V       V         0  True

The * column is the global counter after the sequence. Look at the sixth row: visitor A is denied three times, and the global counter stays at 3, so B and C are still served. That is the reason for checking the visitor first. We also ran the pruning and failure paths. With rows seeded for 2000-03-01, 03-02, 03-09 and 03-10, the first call of 2000-03-10 left only 03-09 and 03-10. With the table missing, as when a migration was never applied, the function returned (False, 'meter_error').

Why should the counter fail closed?

A broken meter that allows everything is the condition in which a bill gets large, and meters tend to break at the worst moment: a missed migration, a missing binding, a brief outage. If the check cannot be made, the feature declines and the product falls back to non-AI behavior. The cost is a degraded feature for a short time. The alternative is unbounded spend.

Failure modes and what this does not prove

  • Denied calls still count. Row five shows the global counter reaching 6 against a limit of 4. Over-limit attempts keep incrementing. That is harmless for a daily budget, but do not read the counter as “calls served.”
  • Two statements are not one transaction. If the process dies between the visitor increment and the global increment, the visitor is charged and the global budget is not. We accept a small undercount of the global total over an overcount.
  • Threads are not Workers. We used threads and separate connections to one local file. That demonstrates SQL-level atomicity. It says nothing about D1’s execution model, replication, or latency. Test concurrent behavior on D1 itself.
  • Counts depend on the scheduler. The naive loss figures are ranges from our runs on two cores, not constants.
  • Day boundaries are yours to define. The day key is whatever string you pass, and we use UTC.

Frequently asked questions

Is SQLite's INSERT ... ON CONFLICT DO UPDATE ... RETURNING atomic?

Yes. It is one statement, and SQLite executes a statement as a single write transaction, so the increment and the returned value cannot interleave with another connection's write. In our test, eight connections ran 500 upserts each against one file, and the counter ended at exactly 4,000 in all 20 runs, with no errors.

Why does SELECT then UPDATE lose counts in SQLite?

Because the read and the write are separate statements. Two connections can both read 41, and both write 42, so one increment vanishes. A busy timeout does not help, since nothing is locked between the statements. In our run, eight threads doing 4,000 total increments ended with a counter between 500 and 2,212.

What SQLite version do I need for RETURNING?

RETURNING arrived in SQLite 3.35. UPSERT (ON CONFLICT DO UPDATE) arrived earlier, in 3.24. We ran every experiment on SQLite 3.45.1 through Python's sqlite3 module. D1 is built on SQLite, so the SQL semantics carry over, but check your D1 database's behavior on D1 itself.

Should a rate-limit counter fail open or closed?

Closed. If the counter errors because the table is missing, the binding is absent, or the database is unavailable, deny the request. A meter that waves traffic through when broken fails exactly when you need it. Our budget function returns a denial with the reason meter_error on any database error.

Does this prove the counter is safe on Cloudflare D1?

No. We tested SQLite semantics with multiple connections to a local file. D1 has its own execution model, and we make no claim about it here. The SQL is portable, but test concurrent behavior on D1 itself before relying on a limit.

← All research