Can NHibernate retries avoid duplicate inserts when using database-generated identifiers?

Question

When a transient database failure occurs after NHibernate has flushed an insert but before the transaction commits, the session is left in an inconsistent state and must be discarded. Reopening a session and retrying the same unit of work risks inserting a duplicate row if the original insert actually persisted. This risk depends heavily on the identifier generation strategy: database-generated identifiers (identity, sequence) may produce a new key on retry, while assigned or hi/lo strategies might repeat the same key and trigger a primary-key violation instead.

Optimistic concurrency via a version column prevents silent overwrites on updates but does not protect against duplicate inserts. Application-level idempotency typically relies on natural keys, unique constraints, or explicit deduplication logic outside the ORM. Connection-level retry features in the ADO.NET provider can further obscure the boundary between a failed flush and a committed write.

Given these behaviors, what is the recommended pattern for structuring a retry loop that guarantees no duplicate inserts when using identity or sequence generators? Does discarding the session and re-fetching the entity by natural key before retry eliminate the duplication risk? Which NHibernate version introduced any built-in safeguards for this scenario, if any?

Tasadduq Burney · Answer

AnswerNo. A simple retry loop that discards the session and re‑opens a new one does not guarantee avoidance of duplicate inserts when NHibernate uses database‑generated identifiers (identity or sequence).Confirmed factsAfter a flush failure the session must be discarded; NHibernate cannot reliably know whether the insert was committed.Identity and sequence generators produce a new key on each insert, so a retry will generate a different identifier even if the first insert succeeded.No NHibernate version up to the current release (as of mid‑2024) provides built‑in safeguards against duplicate inserts for these generator types.The only reliable protection is a database‑level unique constraint (or index) on a natural key or on the identifier column itself.Likely explanation / recommended patternMake the retry idempotent by checking for the entity’s natural key before attempting the insert, and rely on a unique constraint to catch any race that still produces a duplicate.Catch a transient ADO.NET exception during session.Flush() or session.Transaction.Commit().Dispose the faulty session (session.Dispose()).Open a new session.Query for an existing row using the natural key (e.g., session.QueryOver<Entity>().Where(e => e.NaturalKey == value).SingleOrDefault()).If the query returns null, attempt to insert the entity; otherwise treat the operation as already completed.If the insert throws a unique‑constraint violation, swallow it as a sign that the original insert had committed.This pattern guarantees that at most one row with the given natural key ends up in the table, regardless of whether the first flush succeeded.Missing diagnostic detailDo you have a unique index or constraint on the natural key column(s) you plan to use for the deduplication check? If not, adding one is required for the pattern to be safe.

Can NHibernate retries avoid duplicate inserts when using database-generated identifiers?

1 answer

Answer

Confirmed facts

Likely explanation / recommended pattern

Missing diagnostic detail

Your perspective belongs here.

0 question comments