{"record":{"id":"76a3746039556fd1","repo":"nautechsystems/nautilus_trader","slug":"failed-to-assign-verified-execution-nonce-e","errorCode":null,"errorMessage":"Failed to assign verified execution nonce: {e}","messagePattern":"Failed to assign verified execution nonce: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":6029,"sourceCode":"            .await\n            .map_err(|e| anyhow::anyhow!(\"Failed to persist pre-sign verification: {e}\"))?;\n        }\n\n        let result = sqlx::query(\n            \"\n            UPDATE execution_intent\n            SET nonce = $2, updated_at = NOW()\n            WHERE id = $1\n              AND status = 'prepared'\n              AND active\n              AND (nonce IS NULL OR nonce = $2)\n            \",\n        )\n        .bind(assignment.intent_id)\n        .bind(nonce)\n        .execute(&mut *transaction)\n        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to assign verified execution nonce: {e}\"))?;\n        anyhow::ensure!(\n            result.rows_affected() == 1,\n            \"Execution intent {} is not prepared for canonical nonce {}\",\n            assignment.intent_id,\n            assignment.nonce\n        );\n        transaction\n            .commit()\n            .await\n            .map_err(|e| anyhow::anyhow!(\"Failed to commit verified nonce assignment: {e}\"))?;\n        Ok(())\n    }\n\n    /// Appends one verified decision batch before an action on an existing active intent.\n    pub(crate) async fn record_execution_verification_batch(\n        &self,\n        batch: &ExecutionVerificationBatch<'_>,\n    ) -> anyhow::Result<()> {","sourceCodeStart":6011,"sourceCodeEnd":6047,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L6011-L6047","documentation":"The final `UPDATE execution_intent SET nonce = $2 ... WHERE id = $1 AND status = 'prepared' AND active AND (nonce IS NULL OR nonce = $2)` matched zero rows (or the execute itself failed). If `.execute` errors, this message wraps the SQLx error; the code also throws it via the following `rows_affected() == 1` ensure when the WHERE predicate filtered the row out. The library throws it because the nonce write must land on a prepared, active intent that owns no conflicting nonce.","triggerScenarios":"The intent's status changed from \"prepared\" between the earlier SELECT and this UPDATE (another transaction assigned/executed/cancelled it); the intent was deactivated; the intent already holds a different nonce; or the UPDATE fails outright (connection loss, permission denied on UPDATE, trigger abort).","commonSituations":"A concurrent nonce-assignment or cancellation racing the FOR UPDATE window (e.g. from a different connection outside the lock, or trigger-side effects); admin manually flipping status/active in the DB; running with a role lacking UPDATE privilege on execution_intent; long transactions causing the DBA to kill the session.","solutions":["Treat it as a lost race: re-read the intent's current status/nonce, and if the nonce was already assigned to the same value, treat the operation as idempotent success; otherwise restart the assignment flow.","If the SQLx error is a permissions error (42501), grant UPDATE on execution_intent to the application role.","Keep all mutation on the intent inside this transaction (hold the FOR UPDATE lock) so status cannot change mid-assignment; avoid external writers bypassing the API.","Inspect the inner `{e}` when present for the concrete driver-level cause (connection, permission, trigger)."],"exampleFix":"// before: treating every failure as fatal\nmatch db.assign_execution_intent_nonce_verified(&assignment).await {\n    Err(e) => return Err(e),\n    Ok(()) => {}\n}\n// after: idempotent handling of concurrent assignment\nmatch db.assign_execution_intent_nonce_verified(&assignment).await {\n    Ok(()) => {}\n    Err(e) if intent_already_has_nonce(&db, assignment.intent_id, nonce).await => { /* already assigned */ }\n    Err(e) => return Err(e),\n}","handlingStrategy":"try-catch","validationCode":"let (status, active, nonce): (String, bool, Option<i64>) = sqlx::query_as(\n    \"SELECT status, active, nonce FROM execution_intent WHERE id = $1\")\n    .bind(&assignment.intent_id).fetch_optional(pool).await?\n    .ok_or_else(|| anyhow!(\"intent missing\"))?;\nanyhow::ensure!(status == \"prepared\" && active && (nonce.is_none() || nonce == Some(assignment.nonce)), \"intent not assignable\");","typeGuard":null,"tryCatchPattern":"match db.assign_execution_intent_nonce_verified(&assignment).await {\n    Err(e) if e.to_string().contains(\"Failed to assign verified execution nonce\")\n        || e.to_string().contains(\"is not prepared for canonical nonce\") => {\n        // lost race or lost update: re-read intent and either treat as idempotent success or restart the flow\n    }\n    other => other?,\n}","preventionTips":["Never mutate execution_intent status/nonce outside the provided database API.","Do not manually flip status/active in the database while assignment flows are running.","Grant the application role UPDATE privilege on execution_intent.","Design callers to be idempotent: a re-assignment with the same nonce should be a no-op, not a retry storm."],"tags":["database","postgres","update-failed","race-condition","nonce-assignment"],"backgroundTag":"database-write-failed","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}