A queue’s delivery promise describes a bounded part of a workflow. Getting a message to a consumer is different from committing its business effect. At-least-once delivery allows redelivery; at-most-once delivery permits loss rather than retrying uncertain delivery. Neither label alone guarantees that an order, notification, or database update completes exactly once under every failure.

Locate publication, acknowledgment, and commitment

First establish when the producer knows that the broker accepted a message, how long the broker retains it, and which failures its durability policy covers. A message never successfully published, expired by retention, or deliberately deleted is not rescued by calling the consumer at-least-once. Recovery assumptions belong beside the delivery label.

On the consumer side, acknowledging before the effect commits creates a loss window: the broker can stop retrying work the application has not finished. Committing first leaves another window: the effect succeeds, but acknowledgment is lost and the work returns. Redelivery is then compatible with the delivery contract. It does not mean the original operation failed.

Visibility timeouts and acknowledgment deadlines govern availability for another attempt. They are not proof that the first worker stopped. A slow worker and a redelivery can overlap, so extending a deadline and protecting the effect from duplicates solve different problems. Record both delivery attempts and committed business outcomes.

Make repeated effects harmless within a real boundary

For effects inside one transactional database, store a unique operation identity and its effect in the same transaction. The uniqueness check must arbitrate concurrent attempts; a separate read followed by an unprotected write is insufficient. Persist enough payload information to reject reuse of the same identity for a different operation rather than silently accepting conflicting instructions.

A database transaction cannot automatically include an external email, payment API, or another independent store. Those destinations need their own idempotency or coordination contract. A local marker written before the remote call can lose the effect; written afterward, it can leave a duplicate window. Do not present a local deduplication table as an end-to-end exactly-once guarantee.

Some services provide explicitly scoped exactly-once delivery features. Google Pub/Sub, for example, documents regional and subscription-type restrictions and distinguishes delivery duplicates from publish-side duplicates. Read that contract instead of claiming the feature is impossible or universally solves downstream effects. Stable business identities remain useful when multiple published messages represent the same intended operation.

When it breaks

A repeatedly failing message can block ordered progress, while a short deadline can cause overlapping attempts for otherwise valid work. Redelivery counts suggest where to investigate; they do not uniquely distinguish poison data, slow dependencies, lost acknowledgments, or stalled workers. Correlate identity, payload, exception, processing duration, and acknowledgment evidence before changing retry policy.

Bound retries and define how isolated failures are reviewed or replayed. Dead-lettering moves unresolved work; it does not complete it. Deduplication retention must cover the replay horizon you intend to tolerate. Removing the identity record can allow an old request to take effect again, while retaining identifiers indefinitely has storage and privacy costs.

ACK-first loss and commit-first redelivery windows lead to a transactional identity-plus-effect sink, a narrowly attributed historical SQLite total and separate external-effect boundaries.
Historical exact SQLite sink evidence covers rollback, reopen and duplicate handling. The total is a synthetic fixture value; broker behavior and external effects remain separate.

Check it quickly

Save as delivery_guarantee_probe.py and use Python 3 with assertions enabled, without -O or PYTHONOPTIMIZE: assertions contain required calls. Retained exact-fence records report Python 3.12.14 and SQLite 3.53.1 passing; this review did not rerun them. A temporary SQLite database stores identities and a harmless numeric total. This exercises a transactional sink; no account is charged or external message sent.

from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory
import sqlite3


def apply(db, message_id, amount, interrupt=False):
    db.execute('BEGIN IMMEDIATE')
    try:
        prior = db.execute('SELECT amount FROM inbox WHERE id=?', (message_id,)).fetchone()
        if prior is not None:
            if prior[0] != amount:
                raise ValueError('same identity, different payload')
            db.commit()
            return False
        db.execute('INSERT INTO inbox VALUES (?, ?)', (message_id, amount))
        db.execute('UPDATE totals SET amount=amount+? WHERE id=1', (amount,))
        if interrupt:
            raise RuntimeError('controlled failure before commit')
        db.commit()
        return True
    except BaseException:
        db.rollback()
        raise


with TemporaryDirectory(prefix='queue-sink-') as directory:
    path = Path(directory) / 'sink.db'
    with closing(sqlite3.connect(path, isolation_level=None)) as db:
        db.execute('CREATE TABLE inbox(id TEXT PRIMARY KEY, amount INTEGER NOT NULL)')
        db.execute('CREATE TABLE totals(id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)')
        db.execute('INSERT INTO totals VALUES (1, 0)')
        try:
            apply(db, 'work-1', 100, interrupt=True)
        except RuntimeError:
            pass
        else:
            raise AssertionError('controlled failure did not occur')
        assert db.execute('SELECT count(*) FROM inbox').fetchone() == (0,)
        assert db.execute('SELECT amount FROM totals').fetchone() == (0,)
        assert apply(db, 'work-1', 100)
        assert apply(db, 'work-2', 50)
    # Close and reopen the real database rather than retaining an in-memory set.
    with closing(sqlite3.connect(path, isolation_level=None)) as db:
        assert not apply(db, 'work-1', 100)
        try:
            apply(db, 'work-1', 999)
        except ValueError:
            pass
        else:
            raise AssertionError('conflicting payload reused the same identity')
        assert db.execute('SELECT count(*) FROM inbox').fetchone() == (2,)
        assert db.execute('SELECT amount FROM totals').fetchone() == (150,)
        assert not db.in_transaction
    print('rollback kept marker and effect together; reopened retry changed nothing; total=150')

A controlled exception after both writes rolls back both the marker and total. Retrying then commits two operations. After closing and reopening the database, replaying the first identity changes nothing, while reusing it with a different amount raises an error. The final state contains two identities and a total of 150, outside any open transaction.

The experiment verifies rollback, committed-state persistence across connection reopening, and duplicate handling for this SQLite sink. It does not kill the process, simulate power failure, test concurrent consumers, or exercise broker publication and acknowledgment. Test those boundaries separately with the intended service before making a broader delivery claim.

Further reading

Sources