clockworklabs/SpacetimeDB · error

Unique constraint violation during merge: {violation:?}

Error message

Unique constraint violation during merge: {violation:?}

What it means

Thrown at commit time by the locking datastore when a mutable transaction's unique-index changes cannot be merged into committed state. Before folding the transaction in, each unique index in the tx table is checked against the committed index via can_merge, ignoring only rows this transaction deletes; a surviving key collision aborts the merge, the tables are reverted, and the error carries the offending row projected onto the indexed columns.

Source

Thrown at crates/datastore/src/locking_tx_datastore/mut_tx.rs:2463

        }

        // Check that each pair of unique indices can be merged.
        for &index_id in &index_ids {
            let can_merge_result = {
                let commit_idx = &commit_table.indexes[&index_id];
                let tx_idx = &tx_table.indexes[&index_id];
                let is_deleted = |ptr: &RowPointer| tx_delete_table.contains(*ptr);
                commit_idx.can_merge(tx_idx, is_deleted)
            };
            if let Err(violation) = can_merge_result {
                let cols = commit_table.indexes[&index_id].indexed_columns().clone();
                let violation = commit_table
                    .get_row_ref(commit_blob_store, violation)
                    .expect("row came from scanning the table")
                    .project(&cols)
                    .expect("cols should be valid for this table");
                revert(commit_table, tx_table, index_ids.len());
                return Err(anyhow::anyhow!("Unique constraint violation during merge: {violation:?}").into());
            }
        }

        // Take the pointer map if this is the first unique index.
        let pointer_map = if !had_unique {
            tx_table.take_pointer_map();
            commit_table.take_pointer_map()
        } else {
            None
        };

        // Update the pending schema change with index info.
        // The last pushed change is our ConstraintAdded from create_st_constraint.
        // Replace it with the enriched version.
        if let Some(last) = self.tx_state.pending_schema_changes.last_mut() {
            *last = PendingSchemaChange::ConstraintAdded(table_id, constraint_id, index_ids, pointer_map);
        }

View on GitHub (pinned to 9e0d92412f)

Solutions

1. In the reducer, probe the unique index before writing (e.g. ctx.db.table.col().find(&value)) and update or delete the existing row instead of inserting
2. Design upsert reducers to delete-then-insert the colliding row inside the same transaction so can_merge ignores the deleted row
3. When adding a unique index to existing data, deduplicate colliding rows before applying the schema change
4. Retry the reducer with a different key when the conflict comes from a concurrent writer

Example fix

// before: insert blindly; concurrent tx commits the same key first, merge fails
fn create_user(ctx: &ReducerContext, email: String, name: String) {
    ctx.db.user().insert(User { email, name });
}

// after: probe the unique index and update the colliding committed row
fn create_user(ctx: &ReducerContext, email: String, name: String) {
    match ctx.db.user().email().find(&email) {
        Some(mut row) => {
            row.name = name;
            ctx.db.user().email().update(row);
        }
        None => {
            ctx.db.user().insert(User { email, name });
        }
    }
}

// In a reducer, probe the unique index before writing
fn insert_if_free(ctx: &ReducerContext, email: &str, row: User) -> Result<(), String> {
    if ctx.db.user().email().find(&email).is_some() {
        return Err(format!("duplicate key on user.email: {email}"));
    }
    ctx.db.user().insert(row);
    Ok(())
}

Match the commit error, extract the projected row from the message to identify the colliding index and columns, and surface it to the caller as a duplicate-key error; do not blindly retry — the transaction was already reverted and the committed row is still there.

When it happens

Trigger: A reducer inserts or updates a row whose unique-indexed columns match a row already committed by a concurrent transaction (write-write conflict caught at merge, not at insert). Also fires when the transaction deletes a different row than the one it collides with, or when a unique index was created over pre-existing duplicate data.

Common situations: Concurrent reducers racing to create the same entity (sessions, usernames, leaderboard entries); client retries re-running a create reducer; adding a #[unique] column or unique index to a table that already contains duplicates; randomized/fuzz workloads (dst) inserting colliding rows.

Related errors

• TableId `{table_id}` does not exist
• IndexId `{index_id}` does not exist
• begin mutable transaction while one is already active
• insert without active mutable transaction
• delete without active mutable transaction

AI-assisted analysis of clockworklabs/SpacetimeDB@9e0d92412f (2026-08-20). Data as JSON: /api/errors/0245e285ef3f5ec8. Report an issue: GitHub.