Deterministic Record IDs vs. Transaction Blocks for Retry Idempotency

When designing a system to handle network timeouts or request retries in SurrealDB, ensuring that writes do not result in duplicate records is critical. There are two primary architectural patterns for achieving this: utilizing deterministic record IDs with UPSERT or wrapping operations in transaction blocks.

Deterministic IDs provide inherent idempotency by mapping a specific identity to a record, preventing the creation of duplicates regardless of how many times a request is re-sent. Conversely, transaction blocks ensure atomicity across multiple records but require the client to manage the retry logic for the entire block if a failure occurs.

The trade-off involves balancing the simplicity of idempotent record IDs against the consistency guarantees of transactions, particularly when dealing with high-contention workloads where lock contention may increase during transaction retries.

• Does the use of UPSERT with deterministic IDs provide sufficient consistency for multi-record updates without the overhead of a full transaction?
• In a distributed environment, how does the conflict resolution timing of concurrent UPSERT retries compare to the locking behavior of transaction blocks?