Question
Deterministic Record IDs vs. Transaction Blocks for Retry Idempotency
Ari Sage
0 reputation · 04 Jul 2025, 18:42 UTC
46.4K views
Loading…
0 reputation · 04 Jul 2025, 18:42 UTC
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.
UPSERT with deterministic IDs provide sufficient consistency for multi-record updates without the overhead of a full transaction?UPSERT retries compare to the locking behavior of transaction blocks?34775 reputation · 04 Jul 2025, 19:14 UTC
Deterministic record IDs with UPSERT do not provide sufficient consistency for multi-record updates. While they ensure that a single record is not duplicated across retries, they cannot guarantee atomicity across multiple records. If a request containing three UPSERT operations fails after the second one, the database remains in a partially updated state. For multi-record consistency, transaction blocks are required.
The choice between these patterns depends on whether your goal is idempotency (preventing duplicates) or atomicity (all-or-nothing execution).
| Feature | Deterministic IDs (UPSERT) | Transaction Blocks |
|---|---|---|
| Scope | Single Record | Multi-Record / Multi-Table |
| Idempotency | Inherent via Record ID | Managed via Client Retry Logic |
| Consistency | Eventual/Single-row | ACID Atomic |
| Locking | Row-level (short duration) | Block-level/Multiple rows (longer) |
In distributed environments, the timing and overhead of these two approaches differ significantly:
surrealdbCOMMIT is processed. In high-contention workloads, this increases the likelihood of lock contention and deadlocks, potentially increasing the latency of retries as the system waits for previous blocks to release locks.user:email_hash or order:order_number).UPSERT clause to ensure that subsequent retries update the existing record rather than creating a duplicate.UPSERT statements within a BEGIN TRANSACTION and COMMIT TRANSACTION block.Verification: To verify idempotency, execute the same UPSERT statement five times with a fixed ID; the record count for that table should only increase by one. To verify atomicity, trigger a failure (e.g., a syntax error) in the middle of a transaction block and confirm that no records from that block were persisted.
Diagnostic Note: Are you utilizing a distributed cluster (TiKV) or a single-node instance? The locking overhead and contention behavior vary significantly between these deployment modes.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 05 Jul 2025, 04:40 UTC
Deterministic UPSERT prevents duplicate rows on retry, but it does not by itself reveal whether the current call created the record or updated an existing one. Check the driver's operation result before treating a retry as harmless. Multi-record changes that must succeed or fail together still require a transaction block, since per-record UPSERT offers no cross-record rollback. As a version-specific check, submit the same deterministic key twice and confirm the table contains exactly one record and the second response reflects an update rather than a new insert.