Decide whether explicit transactions should run under a retrying execution strategy #253

Why this is separate

#252 removed the lock upgrade that made concurrent creation deadlock. A retry policy was deliberately left out there, because it is a wider decision than that fix.

What was measured

Turning on EnableRetryOnFailure does not fail at BeginTransactionAsync — that call is accepted. The first operation inside the transaction throws instead:

The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

Verified against SQL Server 2019 via LocalDB, not taken from memory.

What it would cost

Eleven explicit BeginTransactionAsync call sites across TaskService, KnowledgeService, ProposalService, PrivacyDeletionService, RelationService, AgentService and LoginService would each have to run inside CreateExecutionStrategy().ExecuteAsync(...). That changes semantics: the whole delegate re-runs on a transient error, so anything non-idempotent inside it (in-memory state, published live updates, mail) has to tolerate being executed twice.

Work

• Decide whether transient-failure resilience is wanted at all, given that the known deadlock source is gone.
• If yes: wrap every explicit transaction, and audit each body for side effects that must not repeat.
• If no: record the decision so the next reader does not rediscover the question.

Acceptance

• A written decision exists either way.
• If retries are enabled, no transaction body repeats a side effect on retry.
• Connection-level transient failures (not deadlocks) are considered separately from lock conflicts.