SurrealDB retry semantics: Avoiding duplicate writes during optimistic concurrency

Guaranteeing Exactly-Once Writes in SurrealDB

To guarantee that a write operation is not duplicated during a transaction retry in SurrealDB, you must use deterministic record IDs combined with CREATE or UPSERT logic. Because SurrealDB uses optimistic concurrency control, a transient failure during the commit phase leaves the client uncertain if the write succeeded. By defining the record ID on the client side rather than relying on auto-generation, you transform the operation into an idempotent action.

Implementation Patterns for Idempotency

Depending on your desired behavior when a retry occurs, use one of the following two patterns:

• Strict Creation (Error-Based Detection): Use the CREATE statement with a deterministic ID. If a retry is triggered but the previous attempt actually succeeded, SurrealDB will return a Duplicate Record error. This error serves as a confirmation that the write is already committed.
• State-Based Upsert: Use the UPSERT statement. This ensures that if the record already exists, it is updated with the same data, resulting in a consistent final state regardless of how many times the transaction is retried.

Handling Transient Failures and Retries

Since SDKs may not consistently expose a granular commit/abort flag for every transient network failure, the following workflow is recommended for manual retry loops:

1. Generate a Request ID: Create a unique idempotency key (UUID) for the specific business action.
2. Execute Transaction: Perform the write using a deterministic ID (e.g., record:request_id) or include the request ID as a field in the record.
3. Catch Conflict/Duplicate Errors: If the SDK returns a duplicate key error during a retry, treat this as a successful commit.
4. Re-read State: If a generic transient error occurs, perform a SELECT on the deterministic ID to verify if the record exists before re-executing the write.

Verification Command

You can verify this behavior using the SurrealQL CLI by attempting to create the same record twice:

-- First attempt
CREATE user:test_id SET name = 'ReadMeFeed';

-- Second attempt (simulating a retry)
CREATE user:test_id SET name = 'ReadMeFeed';
-- Result: Error: Record already exists

Assumptions and Constraints

This approach assumes you are using a version of SurrealDB that supports deterministic IDs (standard in most stable releases). It is uncertain whether specific SDK wrappers automatically handle request identifiers at the protocol level; therefore, application-level idempotency keys are the only reliable method for ensuring exactly-once semantics.

Missing Diagnostic: Please specify which SDK (Rust, JavaScript, Python, etc.) you are using, as the method for catching specific error codes varies significantly between language bindings.