EnableRetryOnFailure and Ambiguous Commits: The Duplicate-Write Gap EF Core Leaves Open

Direct Answer

verifySucceeded is trustworthy only when the failure is a pure timeout, the connection stays open, and the isolation level lets you read committed data. It cannot distinguish a timeout-after-commit from a genuine rollback, deadlock, or connection break. A unique index on a client-generated idempotency key is a stronger primary defense because it turns duplicate inserts into constraint violations that the retry strategy treats as failures. Non-database side effects (HTTP calls, message publishes, file writes) must live outside the retry scope—after ExecuteInTransaction returns successfully, or in an outbox table written inside the same transaction and processed later.

When verifySucceeded Can Be Trusted

The predicate runs on the same connection after the exception. It sees the committed rows iff:

• The error was a transient timeout (e.g., SqlException with number -2 or 1205) after the database committed.
• The connection was not severed; the session and transaction context remain valid.
• The isolation level permits reading the just-committed data (READ COMMITTED or snapshot isolation).

If any of those conditions fails—deadlock victim, connection pool eviction, network partition—the delegate finds no rows and may incorrectly allow a retry, producing duplicates. Always inspect the exception type before trusting the predicate.

Why a Unique Idempotency Key Is Safer

Add a column OperationId (or IdempotencyKey) with a unique index to the target table. Generate a GUID per logical operation in the application layer and include it in every insert. On a retry after an ambiguous commit:

1. The re-executed insert hits the unique constraint.
2. EF Core surfaces a DbUpdateException wrapping a unique-violation error.
3. The execution strategy treats it as a retryable failure, retries up to maxRetryCount, then aborts.

Result: at most one row survives, regardless of whether verifySucceeded misbehaved. The key must be unique per logical operation (not per retry attempt), so generate it before the first SaveChanges and pass it down through the call chain.

Where Non-Database Side Effects Belong

Any code that must run exactly once—HTTP requests, message broker publishes, file writes—must not execute inside the retry delegate or the transaction callback. Two safe patterns:

1. Post-Commit Execution

await strategy.ExecuteInTransaction(async (ctx, token) => {
    await DoDatabaseWork(ctx, token);
    return true; // verifySucceeded reads the written rows
}, verifySucceeded: async (ctx, token) => {
    return await ctx.Set<Entity>().AnyAsync(e => e.Id == expectedId, token);
});

// Side effects HERE, after the strategy returns successfully
await PublishEventAsync(eventData);
await CallExternalApiAsync(payload);

2. Outbox Table (Transactional + Reliable)

Write the side-effect payload to an OutboxMessage table in the same transaction as the business data. A separate background process (or CDC pipeline) reads the outbox and performs the external calls. Retries only replay the database write; the outbox row is inserted once, and the processor handles idempotency on its side.

Minimal Verification Checklist

1. Create a test table with Id (PK) and OperationId (unique).
2. Configure EnableRetryOnFailure(maxRetryCount: 3, maxDelay: TimeSpan.FromSeconds(5)).
3. Simulate a timeout-after-commit (e.g., WAITFOR DELAY in a trigger or close connection after ExecuteNonQuery). Confirm verifySucceeded sees the row and suppresses retry.
4. Simulate a pre-commit failure (throw before commit). Confirm the unique index catches the duplicate on retry and the strategy exhausts retries.
5. Place a counter inside verifySucceeded and an HTTP call after ExecuteInTransaction. Verify the HTTP call runs exactly once per successful attempt.

Assumptions & Open Questions

• Assumes SQL Server / Azure SQL with READ COMMITTED SNAPSHOT or snapshot isolation. Behavior may differ on PostgreSQL (serializable) or MySQL (repeatable read).
• Assumes the idempotency key is generated before the first attempt and flowed through the entire call chain. If you generate it inside the retry delegate, each retry gets a new key and the unique index won't help.
• One diagnostic detail that changes the recommendation: what isolation level does your connection use? If you run serializable or repeatable read without snapshot, verifySucceeded may block or see stale data, making it unreliable even for timeouts.