Explain why SQLite transactions cannot make an HTTP call atomic

By Batu · English technical notes

Also published in our Blogger archive.

Direct answer

A SQLite transaction can atomically commit database statements handled by that connection. It cannot include an HTTP service in the same commit protocol. The example commits an outbox row first, then simulates a network failure. The durable row remains pending, which is exactly the information a later delivery worker needs instead of pretending both actions succeeded or failed together.

The edge case is failure after the database commit but before remote acknowledgement. Retrying then requires an idempotency design owned by the remote endpoint or a stable request key; it is not supplied by SQLite’s COMMIT. Conversely, sending the request before storing a durable intent can leave no local evidence when the process stops.

This demonstration intentionally does not make an HTTP request, implement an outbox dispatcher, or prove delivery exactly once. It shows the boundary that must be designed around. Use transactions to protect the local state transition, and test remote retry semantics with a controlled service or adapter rather than describing the two systems as one atomic operation.

Complete example

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE outbox (id INTEGER PRIMARY KEY, state TEXT)")
with connection:
    connection.execute("INSERT INTO outbox VALUES (1, 'pending')")
try:
    raise ConnectionError("synthetic HTTP failure")
except ConnectionError:
    external = "failed"
state = connection.execute("SELECT state FROM outbox WHERE id = 1").fetchone()[0]
assert state == "pending"
assert external == "failed"
print("outbox=pending http=failed")

Expected stdout:

outbox=pending http=failed

Sources

- sqlite3 documentation

- SQLite transaction documentation

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.