Cosmos DB Stored Procedures for Transactions

1. The "Why" of Stored Procedures in Cosmos DB

In the previous lesson, if you wanted to update two different documents, you would make two separate calls from your C# application (e.g., two calls to ReplaceItemAsync). If the second call failed, the first update would remain, potentially leaving your data in an inconsistent state.

Stored procedures solve this by wrapping multiple operations into a single transaction. All the operations within a stored procedure either succeed together, or they all fail and are rolled back, guaranteeing atomicity.

To understand the core use cases and limitations, please read the introductory section of the following article.

The key takeaways are:

• Atomicity: They provide ACID transactions for operations across multiple documents, but only within the same logical partition.
• Performance: They reduce network round-trips by executing logic on the server, which is ideal for batch operations.
• Constraints: They are written in JavaScript, have a bounded execution time (typically a few seconds), and cannot access documents outside their designated logical partition.

2. Writing a Stored Procedure in JavaScript

Cosmos DB stored procedures are written as JavaScript functions. You can create them directly in the Azure Portal or the Cosmos DB Emulator, or register them programmatically using the SDK.

The function receives a context object, which provides access to the container (getCollection()) and the response object (getResponse()).

Let's watch a demonstration of how a simple stored procedure to create a new item is authored in the Azure Portal.

Here's the essence of the JavaScript code from the video for creating an item. It accepts an array of items and creates the first one.

function addItem(items) {
    var collection = getContext().getCollection();
    var response = getContext().getResponse();

    if (!items || !items.length) {
        response.setBody('No items provided');
        return;
    }

    var itemToCreate = items[0];

    // Create a document.
    var isAccepted = collection.createDocument(
        collection.getSelfLink(),
        itemToCreate,
        (err, createdItem) => {
            if (err) throw new Error('Error' + err.message);
            response.setBody(JSON.stringify(createdItem));
        }
    );

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

3. Executing a Stored Procedure from .NET

With a stored procedure defined in our container, we can now execute it from our C# application. This is where your .NET skills come directly into play. The Microsoft.Azure.Cosmos SDK provides a straightforward way to do this via the Container.Scripts object.

The most critical aspect of executing a stored procedure is providing the partition key. The partition key value determines which logical (and physical) partition the JavaScript code will run on. The stored procedure can only see and interact with documents that share that exact partition key.

The official Microsoft documentation provides the clearest C# example for this.

To summarize the C# code, you will use:
await container.Scripts.ExecuteStoredProcedureAsync<T>(string storedProcedureId, PartitionKey partitionKey, dynamic[] parameters)

• T: The expected return type from the stored procedure.
• storedProcedureId: The ID of the stored procedure (e.g., "addItem").
• partitionKey: The PartitionKey object for the partition you are targeting.
• parameters: A dynamic array containing the parameters to pass to the JavaScript function.

Now, let's see this C# code in action, continuing with the video from the previous step.

4. A Multi-Document Transaction Example

So far, we've created a single document. The real power of stored procedures, however, lies in performing atomic operations on multiple documents.

Imagine a financial transaction where you need to debit one account and credit another. This must happen atomically. The following article provides a JavaScript stored procedure that does exactly this: it reads two account documents, checks for sufficient funds, updates both balances, and replaces both documents. If any step fails, the entire transaction is automatically rolled back by Cosmos DB.

You don't need to implement this, but reading through the example will solidify your understanding of how to achieve transactional guarantees.

This "balance transfer" pattern is a classic use case for stored procedures and perfectly illustrates the "transactional operations" capability required by the AZ-204 exam.

Conclusion

In this lesson, we moved beyond simple client-side CRUD to server-side transactional logic with Azure Cosmos DB stored procedures. You've seen how they provide atomic guarantees similar to transactions in a relational database, but with the important caveat of being scoped to a single logical partition.

Here are the key takeaways:

• Purpose: Stored procedures are used for atomic, multi-document transactions and for reducing network latency by running logic on the server.
• Language: They are written in JavaScript and have access to a context object to interact with the container.
• Execution Scope: A stored procedure runs within a single logical partition, which is specified by the PartitionKey value provided during execution.
• .NET SDK Execution: You use the container.Scripts.ExecuteStoredProcedureAsync method, passing the stored procedure ID, a PartitionKey, and an array of parameters.

In our next lesson, we will explore another powerful server-side feature of Cosmos DB: the change feed. We will learn how to build a processor that can react to creations and updates in a container in near real-time, enabling event-driven architectures.