The Ops Community ⚙️

Cover image for A Video Job Finished Twice. Record It Once with SQLite
fluxpro net
fluxpro net

Posted on

A Video Job Finished Twice. Record It Once with SQLite

A worker finishes rendering a clip and emits a video-ready event. The consumer commits its database write, then loses the connection before the queue gets the acknowledgement. The next delivery names the same finished clip. If every delivery inserts a fresh record, the job now looks as if it completed twice.

This is one way to get duplicates. A worker can also retry and publish a second event for the same output. A queue that delivers at least once can redeliver a single event. Amazon SQS standard queues document that behavior. Amazon SQS at-least-once delivery

Two paper dispatch tags with matching teal film symbols sit beside a blank ledger.

Two deliveries can carry the same completion. The database key belongs to the job and output version.

Give the completion a stable key

Decide what should appear once. Here it’s the completion of one output version of one logical job. The producer assigns the job ID before work begins. Retries preserve both the job ID and the output version. A deliberate new render that should have a separate completion receives a new version. A new delivery attempt can have a new transport ID while still carrying the same logical key.

In the fixture, ("job-a", 1) is that key. Both deliveries should carry the same asset_ref, an inert string standing for the output. In a system that issues expiring URLs, compare a stable asset identifier instead of the changing URL. If two messages share a key but disagree about the output, surface the mismatch so it can be investigated.

Two teal film cards overlap at left, with a separate amber film card at right.

A retry keeps its logical key. A different job or deliberate new output version gets a separate key.

Run the fixture locally

Save the following as video_ready_fixture.py and run python3 video_ready_fixture.py. It uses only the Python standard library and a temporary file-backed SQLite database. The clip paths are example strings. This run uses SQLite’s UPSERT syntax, available since SQLite 3.24.0. SQLite UPSERT documentation

"""Synthetic duplicate video-ready events against a temporary SQLite file."""
import sqlite3
import sys
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory


class PayloadConflict(ValueError):
    """An existing logical completion names a different output."""


def record_ready(db, job_id, output_version, asset_ref):
    """Insert or validate a completion in the caller's transaction."""
    key = (job_id, output_version)
    inserted = db.execute(
        """
        INSERT INTO completions (job_id, output_version, asset_ref)
        VALUES (?, ?, ?)
        ON CONFLICT (job_id, output_version) DO NOTHING
        """,
        (*key, asset_ref),
    ).rowcount
    if inserted == 1:
        return "inserted"

    row = db.execute(
        """
        SELECT asset_ref FROM completions
        WHERE job_id = ? AND output_version = ?
        """,
        key,
    ).fetchone()
    if row != (asset_ref,):
        raise PayloadConflict(f"Different asset for {key!r}")
    return "replay"


def row_count(db):
    return db.execute("SELECT COUNT(*) FROM completions").fetchone()[0]


def demo(path):
    with closing(sqlite3.connect(path, isolation_level="IMMEDIATE")) as db:
        db.execute(
            """
            CREATE TABLE completions (
                job_id TEXT NOT NULL,
                output_version INTEGER NOT NULL,
                asset_ref TEXT NOT NULL,
                UNIQUE (job_id, output_version)
            )
            """
        )

        cases = [
            ("first delivery", "job-a", 1, "clips/a-v1.mp4", "inserted", 1),
            ("repeat delivery", "job-a", 1, "clips/a-v1.mp4", "replay", 1),
            ("different job", "job-b", 1, "clips/b-v1.mp4", "inserted", 2),
            ("new version", "job-a", 2, "clips/a-v2.mp4", "inserted", 3),
        ]
        for label, job, version, asset, expected, count in cases:
            with db:
                status = record_ready(db, job, version, asset)
            assert status == expected and row_count(db) == count
            print(f"{label}: {status}; rows={count}")

        try:
            with db:
                record_ready(db, "job-c", 1, "clips/c-v1.mp4")
                record_ready(db, "job-a", 1, "clips/wrong.mp4")
        except PayloadConflict:
            assert row_count(db) == 3
            print("different payload: conflict; batch rolled back")
        else:
            raise AssertionError("expected a payload conflict")

    with closing(sqlite3.connect(path)) as db:
        rows = db.execute(
            """
            SELECT job_id, output_version, asset_ref
            FROM completions ORDER BY job_id, output_version
            """
        ).fetchall()
        assert rows == [
            ("job-a", 1, "clips/a-v1.mp4"),
            ("job-a", 2, "clips/a-v2.mp4"),
            ("job-b", 1, "clips/b-v1.mp4"),
        ]
        print(f"reopen: {len(rows)} rows; original asset preserved")


if __name__ == "__main__":
    print(f"Python {sys.version.split()[0]}; SQLite {sqlite3.sqlite_version}")
    with TemporaryDirectory() as temp:
        demo(Path(temp) / "completions.sqlite3")
Enter fullscreen mode Exit fullscreen mode

The table’s UNIQUE (job_id, output_version) constraint decides whether an insert can happen. The targeted ON CONFLICT clause leaves the existing row alone when that pair already exists. The function then reads the saved output within the transaction. An identical value is a replay. A different value raises PayloadConflict before the caller commits. Other insert errors still surface.

The run below used Python 3.9.6 and SQLite 3.51.0. It verifies first delivery, repeat delivery, a second job, and a separate output version. In the conflict case, the transaction first inserts job-c, then encounters a different asset for ("job-a", 1). The exception exits the with db: block and rolls back job-c. Reopening the file verifies the three committed rows and the original asset reference. The connection context manager handles the open transaction, and closing() closes the connection. Python sqlite3 connection context manager

Python 3.9.6; SQLite 3.51.0
first delivery: inserted; rows=1
repeat delivery: replay; rows=1
different job: inserted; rows=2
new version: inserted; rows=3
different payload: conflict; batch rolled back
reopen: 3 rows; original asset preserved
Enter fullscreen mode Exit fullscreen mode

Decide what happens after the commit

On a real queue, acknowledge a successful insert or a validated replay after the database commit. If the commit succeeds and acknowledgement fails, the same key and payload can be checked again. A payload conflict needs a separate decision. Send it to the incident or dead-letter path your system provides, along with the logical key and enough evidence to resolve the mismatch.

A ledger with one film symbol sits apart from a sealed envelope and a parcel.

A committed record and an external action are two separate events. The artwork illustrates the boundary.

This fixture keeps one local completion row per key. An upload, notification or new generation job can still happen twice if a process performs it and fails before recording success. A SQLite transaction cannot roll back a request that has already succeeded in another service.

  • Give the external action its own stable identity. For example, use (job_id, output_version, "notify") for a single notification of that output.
  • If the action needs reliable handoff, save its pending outbox record in the same transaction as the completion. Have a separate worker deliver it and record the result.
  • Expect that worker to retry after an ambiguous response. If the destination supports idempotency, pass it the stable action key. Otherwise, plan for reconciliation or choose an operation that tolerates repeats.

The test exercises a single local database file with synthetic events. It gives you a place to check the insert, replay and rollback paths before wiring the consumer into a queue.

Top comments (0)