How to check a background job is idempotent

Give the job a key, store that key in a column declared UNIQUE, and write it before the side effect. Run two workers at one key in the same instant with node queue.mjs race claim: one charges, the other stops on errcode 2067, and the charges table still holds a single row.

Why check this

Queues deliver at least once. A worker that times out mid job, a lost ack, a deploy that restarts a consumer: each hands the same job to a worker again. Run this check on any job that moves money, mail or stock, and after any concurrency change. It prevents one order charged twice because two workers took the same retry.

What the check settles is narrow. It shows whether the key stops a second delivery, and whether it still stops one when both arrive together. It says nothing about how the producer builds the key: one derived from the clock passes every step below.

Prerequisites

• Node 22 or later. The database ships with the runtime. See the node:sqlite documentation.
• A shell that runs two processes at once. The output came from Git Bash on Windows 11.
• Save the queue as queue.mjs in an empty directory. It holds a job table, a charges table for the side effect, and two key tables that differ only in the word UNIQUE.

// queue.mjs: a job table, a side effect and two key tables in one SQLite file.
import { DatabaseSync } from 'node:sqlite';
import { spawn } from 'node:child_process';

const db = new DatabaseSync('queue.db', { timeout: 5000 });
db.exec('PRAGMA journal_mode = WAL');
db.exec(`
  CREATE TABLE IF NOT EXISTS jobs (id INTEGER PRIMARY KEY, key TEXT NOT NULL);
  CREATE TABLE IF NOT EXISTS charges (id INTEGER PRIMARY KEY, key TEXT, worker TEXT);
  CREATE TABLE IF NOT EXISTS keys_plain (key TEXT);
  CREATE TABLE IF NOT EXISTS keys_unique (key TEXT UNIQUE, at INTEGER);`);

const [cmd, ...rest] = process.argv.slice(2);
const arg = (n, d = null) => { const i = rest.indexOf(n); return i < 0 ? d : rest[i + 1]; };
const charge = (k, w) => db.prepare('INSERT INTO charges (key, worker) VALUES (?, ?)').run(k, w);
const claim = (k) => db.prepare('INSERT INTO keys_unique (key, at) VALUES (?, ?)').run(k, Date.now());

if (cmd === 'add') db.prepare('INSERT INTO jobs (key) VALUES (?)').run(rest[0]);
if (cmd === 'reset') for (const t of ['charges', 'keys_plain', 'keys_unique']) db.exec(`DELETE FROM ${t}`);
if (cmd === 'purge') {
  const gone = db.prepare('DELETE FROM keys_unique WHERE at < ?').run(Date.now() - rest[0] * 1000);
  console.log(`ttl ${rest[0]} s: dropped ${gone.changes} key(s)`);
}
if (cmd === 'count') for (const t of ['jobs', 'charges', 'keys_plain', 'keys_unique'])
  console.log(t.padEnd(12), JSON.stringify(db.prepare(`SELECT * FROM ${t}`).all()));

if (cmd === 'race') {                           // two OS processes, one start instant
  const at = Date.now() + 800;
  const run = (job, w) => new Promise((done) => spawn(process.execPath,
    [import.meta.filename, 'work', '--job', job, '--mode', rest[0], '--worker', w, '--at', at],
    { stdio: 'inherit' }).on('exit', done));
  await Promise.all([run('1', 'W1'), run('2', 'W2')]);
}

if (cmd === 'work') {
  const w = arg('--worker'), mode = arg('--mode'), crash = rest.includes('--crash');
  const { key } = db.prepare('SELECT key FROM jobs WHERE id = ?').get(Number(arg('--job')));
  while (Date.now() < Number(arg('--at', 0))) { /* both workers leave the gate together */ }
  const refused = (e) => console.log(`${w} refused ${key}: errcode ${e.errcode}, ${e.message}`);
  if (mode === 'loose') {                       // check, then write the key
    if (db.prepare('SELECT 1 FROM keys_plain WHERE key = ?').get(key)) { console.log(`${w} skipped ${key}`); process.exit(0); }
    charge(key, w);
    db.prepare('INSERT INTO keys_plain (key) VALUES (?)').run(key);
  } else if (mode === 'claim') {                // write the key first, let SQLite judge
    try { claim(key); } catch (e) { refused(e); process.exit(0); }
    if (crash) { console.log(`${w} died after the key, before the charge`); process.exit(1); }
    charge(key, w);
  } else if (mode === 'after') {                // do the work, record the key last
    charge(key, w);
    if (crash) { console.log(`${w} died after the charge, before the key`); process.exit(1); }
    try { claim(key); } catch (e) { refused(e); process.exit(0); }
  }
  console.log(`${w} charged ${key}`);
}

• Every command below is wrapped in ( ... ) 2>/dev/null, because node:sqlite prints an ExperimentalWarning on each start and the wrapper also hides the shell's job notices.
• One SQLite file is not a production queue: the ordering and the constraint carry over, the timings do not. Delete queue.db afterwards.

Steps

1. 1

   Step 1.

   Enqueue the same job twice. Two rows, one key, which is what a redelivery looks like.

   ( for i in 1 2; do node queue.mjs add charge-77; done && node queue.mjs count ) 2>/dev/null
   

   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      []
   keys_plain   []
   keys_unique  []

   The ids differ, the key does not: the key names the intent, the ids the deliveries.

2. 2

   Step 2.

   Work the two deliveries in sequence with the handler that reads the key table before writing to it.

   ( node queue.mjs reset && for j in 1 2; do node queue.mjs work --job $j --mode loose --worker W$j; done && node queue.mjs count ) 2>/dev/null
   

   W1 charged charge-77
   W2 skipped charge-77
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      [{"id":1,"key":"charge-77","worker":"W1"}]
   keys_plain   [{"key":"charge-77"}]
   keys_unique  []

   One charge from two deliveries. A suite that stops here reports an idempotent worker.

3. 3

   Step 3.

   Hand the same two deliveries to two workers started on one clock. race spawns both and spins each to a shared timestamp.

   ( node queue.mjs reset && node queue.mjs race loose && node queue.mjs count ) 2>/dev/null
   

   W1 charged charge-77
   W2 charged charge-77
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      [{"id":1,"key":"charge-77","worker":"W1"},{"id":2,"key":"charge-77","worker":"W2"}]
   keys_plain   [{"key":"charge-77"},{"key":"charge-77"}]
   keys_unique  []

   Two charges, and the key sits in keys_plain twice: both workers ran the SELECT before either ran the INSERT. Twenty repeats produced two charges 20 times out of 20, with no added delay in the handler.

4. 4

   Step 4.

   Run the same race against the table whose key column carries UNIQUE, the only difference between the key tables.

   ( node queue.mjs reset && node queue.mjs race claim && node queue.mjs count ) 2>/dev/null
   

   W1 charged charge-77
   W2 refused charge-77: errcode 2067, UNIQUE constraint failed: keys_unique.key
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      [{"id":1,"key":"charge-77","worker":"W1"}]
   keys_plain   []
   keys_unique  [{"key":"charge-77","at":1789236681983}]

   One charge. The loser never reached the side effect: the database refused its key first. The same 20 races produced a second charge zero times.

5. 5

   Step 5.

   Move the key write after the side effect, kill the worker in the gap, then let the retry arrive.

   ( node queue.mjs reset && node queue.mjs work --job 1 --mode after --worker W1 --crash; node queue.mjs work --job 2 --mode after --worker W2 && node queue.mjs count ) 2>/dev/null
   

   W1 died after the charge, before the key
   W2 charged charge-77
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      [{"id":1,"key":"charge-77","worker":"W1"},{"id":2,"key":"charge-77","worker":"W2"}]
   keys_plain   []
   keys_unique  [{"key":"charge-77","at":1789236682288}]

   Two charges under the UNIQUE column that held in step 4. The work succeeded and its key was never recorded.

6. 6

   Step 6.

   Put the key write first and kill the worker in the same gap.

   ( node queue.mjs reset && node queue.mjs work --job 1 --mode claim --worker W1 --crash; node queue.mjs work --job 2 --mode claim --worker W2 && node queue.mjs count ) 2>/dev/null
   

   W1 died after the key, before the charge
   W2 refused charge-77: errcode 2067, UNIQUE constraint failed: keys_unique.key
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      []
   keys_plain   []
   keys_unique  [{"key":"charge-77","at":1789236682530}]

   Zero charges. Claiming first turns the duplicate into a silent loss, so the key row needs a status and a release path.

7. 7

   Step 7.

   Expire the key between the two deliveries and watch the guarantee disappear.

   ( node queue.mjs reset && node queue.mjs work --job 1 --mode claim --worker W1 && node queue.mjs purge 60 && sleep 2 && node queue.mjs purge 1 && node queue.mjs work --job 2 --mode claim --worker W2 && node queue.mjs count ) 2>/dev/null
   

   W1 charged charge-77
   ttl 60 s: dropped 0 key(s)
   ttl 1 s: dropped 1 key(s)
   W2 charged charge-77
   jobs         [{"id":1,"key":"charge-77"},{"id":2,"key":"charge-77"}]
   charges      [{"id":1,"key":"charge-77","worker":"W1"},{"id":2,"key":"charge-77","worker":"W2"}]
   keys_plain   []
   keys_unique  [{"key":"charge-77","at":1789236692398}]

   A 60 s expiry keeps the key. A 1 s expiry drops it and the redelivery charges again.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | One charge row after a sequential pair | The handler deduplicates when nothing overlaps | Keep going. A sequential pass says nothing about two workers. | | Two charge rows after a race, and the key twice in keys_plain | Check then insert: both workers read before either wrote | Move the uniqueness into the schema, as step 4 does. | | errcode 2067, UNIQUE constraint failed from the loser | The database refused the second key | Nothing. Treat that error as "already handled", not as a job failure. | | Two charge rows and one key row | The key was written after the work and a crash landed in between | Write the key before the side effect. | | No charge rows and one key row | The key was claimed and the work never ran | Give the key row a status and release it when the job fails. | | ttl 1 s: dropped 1 key(s) followed by a second charge | The key expired inside the retry window | Set the expiry longer than the producer's longest retry schedule. |

Common mistakes

What to check next

• How to test duplicate job execution: the observation side, after a worker dies mid job.
• How to test API idempotency: the same question at the HTTP endpoint.
• Webhook idempotency: the same race in one process, key held in memory.
• How to test duplicate payment prevention: what a duplicate costs when the side effect is money.
• How to test webhook retries: the schedule that decides how long the key must live.

FAQ

What is an idempotency key?

A string naming the intent behind a job rather than the delivery of it. Two deliveries of one order carry the same key. The worker stores it and refuses any job whose key is already stored.

How to implement idempotency?

Three parts, in this order. Derive the key from the business intent. Store it in a column declared UNIQUE, so the database decides who wins the race. Write it before the side effect, and read the constraint error as work already done.

Should the key live in the job row or in its own table?

Its own table when several job types share one rule, or when the key outlives the job row. A column on the job row works when the queue keeps completed rows. Either shape needs the UNIQUE index from step 4.

Does a database transaction remove the need for the key?

Only when the side effect writes to the same database: the key insert and the work then commit or roll back together. A payment API call, an email or a file upload cannot join that transaction, which is what steps 5 and 6 measure.

How long should a key be kept?

Longer than the producer's longest retry schedule. Step 7 dropped the key after 1 s and the next delivery charged again. A key that is never dropped stays correct and grows without bound.

Verified