Conflicting behavior when creating stored procedure using Cosmos DB .NET SDK v3

Question

In the following code, an attempt is made to return a stored procedure (SP) whether it exists or not:

private async Task<StoredProcedureProperties> GetOrCreateStoreProcedure(string embeddedResourcePath, string spId, CosmosClient documentClient, Container collection)
{
    string body;
    using (StreamReader sr = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(embeddedResourcePath), Encoding.UTF8))
    {
        body = sr.ReadToEnd();
    }
    try
    {
        return await collection.Scripts.ReadStoredProcedureAsync(spId);
    }
    catch (CosmosException ex) when (ex.StatusCode == HttpStatusCode.NotFound)
    {
        try
        {
            return await collection.Scripts.CreateStoredProcedureAsync(new StoredProcedureProperties { Id = spId, Body = body });
        }
        catch (CosmosException ex2) when (ex2.StatusCode == HttpStatusCode.Conflict)
        {
            return await collection.Scripts.ReplaceStoredProcedureAsync(new StoredProcedureProperties { Id = spId, Body = body });
        }
    }
}

The issue arises when trying to read the stored procedure; it returns null. This leads to an attempt to create it, which results in a conflict error (409), indicating that the stored procedure might already exist with the same ID. Following this, the code enters the catch block for replacement but encounters another exception stating that the resource with the specific ID is not found (404).

Currently, the stored procedure is confirmed to not exist in the container within the database. Any assistance with this issue would be appreciated.

Answer 1

Hi Kirankumar Bharsadiya,

Thank you for reaching out to Microsoft Q & A forum

This behavior is likely due to eventual consistency or replication latency within Azure Cosmos DB. When the stored procedure is first read and returns a 404 (Not Found), it might already exist but hasn't yet propagated across all nodes. Attempting to create it then results in a 409 (Conflict), and trying to immediately replace it may again lead to a 404, as the system hasn’t fully registered it yet. 

A practical approach is to introduce a short delay before attempting to replace the stored procedure: 

await Task.Delay(1000); // wait 1 second
return await container.Scripts.ReplaceStoredProcedureAsync(...);

This gives Cosmos DB enough time to fully propagate the stored procedure metadata. Also, ensure that no other process is creating or modifying the same stored procedure simultaneously.  

If you have found the answer provided to be helpful, please click on the "Accept answer/Upvote" button so that it is useful for other members in the Microsoft Q&A community.