MQTT delivered the message. Did the database commit?

The worker changes the database. Then it dies before acknowledging the message.

On restart, the message comes back. The worker changes the database again.

The broker did its job. The counter is still wrong. 😅

That is the failure I wanted to reproduce after reading S M Tahosin’s MQTT explainer and its discussion about persistent sessions and application processing. The thread’s useful question was what happens between committing a change and acknowledging delivery. [1]

I ran that crash against a local Mosquitto broker and a Python consumer. One published event produced a counter of two in the naive consumer. With a durable deduplication record in the same transaction, it stayed at one.

Original diagram of the executed QoS 1 crash/reconnect example. The broker remains running throughout.

Put the crash in the uncomfortable place

The event is intentionally boring:

{"producer":"sensor-demo","event_id":"batch-42","delta":1}

It means “add one,” not “set the value to one.” Repeating the operation has a visible effect.

The consumer uses MQTT 3.1.1, a stable client ID, clean_session=False, a QoS 1 subscription and Paho’s manual_ack=True. The publisher also publishes at QoS 1. Paho then lets the application call ack() after processing rather than automatically acknowledging when the callback returns. [2]

For the first delivery, the worker commits its SQLite transaction and immediately exits with code 73. It never calls ack(). This is an actual child-process exit, not a printed “pretend crash.”

The restarted consumer reconnects using the same client ID and session. In both runs, the broker reported the session present and redelivered the message with the MQTT DUP flag set.

The naive callback increments the counter on both deliveries. Nothing in its database says that sensor-demo / batch-42 has already had its effect.

Run the complete exercise

The complete executable script starts a local broker, runs both consumers, checks the results and shuts the broker down. Save it as mqtt_commit.py in an empty working directory.

Use Python 3.10 or newer, uv, and Mosquitto available as mosquitto on your PATH. My recorded run used Mosquitto 2.1.2 and Paho MQTT 2.1.0. The broker binds only to 127.0.0.1:18884; the exercise sends synthetic data and does not enable a system service.

uv run --with paho-mqtt==2.1.0 python mqtt_commit.py

The script writes mqtt-results.json and broker.log in that working directory. Its SQLite databases live in temporary directories; it reads the final values before removing them.

The checked result was:

blind:  counter=2, ledger_rows=0, reconnect_session=True, redelivery_DUP=True
dedupe: counter=1, ledger_rows=1, reconnect_session=True, redelivery_DUP=True

An earlier run with a different Python broker did not complete the redelivery check. I kept that failed attempt and used Mosquitto for the completed example. Broker behavior and library versions belong in the reproduction record; a callback that runs twice in a for-loop would not establish that reconnect delivery worked.

Make “already applied” part of the change

The repair is a small ledger with a unique key:

CREATE TABLE applied (
    producer TEXT,
    event_id TEXT,
    PRIMARY KEY (producer, event_id)
);

Within the same SQLite transaction, the consumer inserts the event key and applies the business change only if that insert created a row. The executable script uses this logic:

with db:
    inserted = db.execute(
        'INSERT OR IGNORE INTO applied VALUES (?, ?)',
        (event['producer'], event['event_id'])
    ).rowcount == 1
    if inserted:
        db.execute('UPDATE counter SET value=value+?', (event['delta'],))
client.ack(message.mid, message.qos)

That snippet assumes the schema and connection already exist; the linked script includes the setup and restart orchestration.

On redelivery, the unique key already exists. The insert changes no rows, so the increment is skipped. The worker can acknowledge the delivered message because the earlier transaction already committed its effect.

The transaction is the important part. If I commit the increment, then separately record the ID, a crash between those writes recreates the bug. If I record the ID first and separately commit the increment later, a crash can make the retry skip work that never happened.

The ledger and the effect need to succeed or fail together.

Two acknowledgements that mean different things

MQTT QoS describes protocol delivery. A publisher receiving its acknowledgement does not establish that a downstream application’s database transaction committed. Persistent sessions retain relevant delivery state; they do not turn an arbitrary database update into part of the protocol handshake. [2][3]

This run exercises QoS 1, not a failure of QoS 2. Calling QoS 2 “exactly once” does not make an external SQLite transaction atomic with the protocol state. That separate application boundary still needs a design.

The DUP flag is useful evidence in this test, but it is not my deduplication key. Packet IDs are protocol identifiers that can be reused. The application key comes from the event payload and includes the producer namespace.

There is a contract behind that key: the producer must not reuse an ID for a different operation. A production consumer should also validate payloads, handle a reused key with conflicting content, and keep ledger entries long enough for its replay policy. This demo does not implement a retention service.

Where the example stops

The broker stays up; I kill and restart only the consumer. This checks one precise crash window. It does not establish broker disk durability, replication, MQTT 5 behavior or recovery after every possible failure.

The ledger covers a SQLite change. Sending an email or moving a motor is a different side effect; inserting a database row does not roll either one back. Those workflows need a further mechanism, such as a transactional outbox and an idempotent downstream operation.

For a counter update, though, the lesson is pleasantly concrete. Keep the acknowledgement after the commit, and make a retry able to recognize the commit that already happened.

References and assets

[1] S M Tahosin’s MQTT explainer and discussion. This prompted the crash-window exercise.

[2] Paho Python client documentation, including persistent sessions, manual_ack and ack().

[3] OASIS MQTT 3.1.1 specification, especially session state, QoS 1 and redelivery.

Diagram and demonstration code are original. Only synthetic events were sent.

AI disclosure

AI was used to draft the article.