Automatic transaction retries vs manual retry loops for preventing duplicate writes in AdonisJS

Question

The goal is to guarantee exactly‑once writes when a PostgreSQL deadlock forces a transaction to abort in an AdonisJS service.

AdonisJS provides a retries option on Database.transaction that automatically re‑executes the callback, but the documentation notes that writes flushed before the error may already be committed, risking duplicate rows unless the operations are idempotent. A manual retry loop can explicitly roll back and restart the transaction, yet it requires re‑fetching any data read before the retry to avoid unique‑key violations on the second attempt.

Does the built‑in retries setting roll back the entire transaction before each retry, or can a successful INSERT persist across a deadlock‑induced retry? When using a manual retry loop, what is the safest way to re‑read dependent rows to avoid unique‑key violations on the second attempt? Are there documented isolation‑level settings that change this behavior?

Tasadduq Burney · Answer

Does the built‑in retries option roll back the whole transaction?
In AdonisJS, Database.transaction({ retries }) simply re‑executes the callback from the beginning when an error bubbles out of the transaction block. PostgreSQL aborts the current transaction as soon as it detects a deadlock (error code 40P01). No part of that aborted transaction is committed, so a blind INSERT that failed because of a deadlock will never be persisted. The retry therefore starts with a clean slate.
Can a successful INSERT survive a deadlock‑induced retry?
Only if the application mis‑interprets the error. If the driver throws a network‑level timeout after the database has already committed the transaction, the retry will re‑run the callback and a second INSERT will occur, producing a duplicate. In normal PostgreSQL deadlock handling, the transaction never reaches COMMIT, so duplicates cannot arise from the retry itself.
Manual retry loop – safest way to re‑read dependent rows

Wrap the entire operation in a Database.transaction() call, but do not use the built‑in retries option.
Catch the DatabaseError and inspect error.code === '40P01' to confirm a deadlock.
Before restarting the transaction, re‑query any rows you previously read to refresh their state. Example:
const user = await User.query().where('id', userId).first();
// After retry, fetch again:
const freshUser = await User.query().where('id', userId).first();

Use ON CONFLICT (UPSERT) or check existence inside the new transaction to avoid unique‑key violations. Example:
await User.query().insert({ id: userId, name }).onConflict('id').merge();

Repeat until the transaction succeeds or a non‑retryable error occurs.

Isolation‑level settings that influence this behavior

READ COMMITTED (default) – only locks the rows being modified; deadlocks can still happen but are less frequent.
REPEATABLE READ – adds a snapshot of the database at transaction start; can increase deadlock chances if many concurrent updates target the same rows.
SERIALIZABLE – the strictest level; PostgreSQL may raise a serialization failure (40001) instead of a deadlock. Treat both error codes as retryable if you want robust handling.

What to ask next?
To tailor the recommendation further, could you confirm whether you see the PostgreSQL deadlock error code 40P01 or a driver‑level timeout after the transaction has already committed?

Automatic transaction retries vs manual retry loops for preventing duplicate writes in AdonisJS

1 answer

Does the built‑in `retries` option roll back the whole transaction?

Can a successful INSERT survive a deadlock‑induced retry?

Manual retry loop – safest way to re‑read dependent rows

Isolation‑level settings that influence this behavior

What to ask next?

Your perspective belongs here.

0 question comments