Transactional batch cosmos - write IF certain value read in partition

Question

Using transactional batch in cosmos, is it possible to query a partition within a collection (Select * from partition where openOrder=1) and then IF the query returns that there are no open orders, add an item to the collection.

Or you could have the write first, and then have the conditional read (Select * from partition where openOrder=1 and id={unique GUID just written}) and then if the 2nd read fails, the write would be reversed (?).

Would want this to be done in one atomic operation because I don't want to think there are no open orders and then another process writes an open order before this process can.

If not, is it possible to do this a different way?

**Edit 3:30 PM PT 9/16 with attempted solution that wrote a document when I tested it **

function checkOpenOrder(inputDocString, query){
    console.log("Stored Procedure Starting")
    var context = getContext(); 
    var container = context.getCollection();
    var containerLink = container.getSelfLink();
    var response = context.getResponse(); 

    var isAccepted = container.queryDocuments(
        container.getSelfLink(), 
        query,
        function (err, items, options) {
            if (err) throw err;
            // Query would be fed in such that if there is no open order, no items would return in the collection 

            if (items.length == 0){ 
                var docCreated = container.createDocument(containerLink, inputDocString, 
                    function (err2, itemWritten) {
                        if (err2) throw err2;
                        // else was successfully able to write document?
                        response.setBody("Wrote document");
                    });

            }
            else { 
               
                response.setBody("Order currently open");
            }
            

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

    
}

Edit: Final question 9/17 Would I want to set max degree of parallelism to 1 to ensure that the stored procedure cannot run 2x in the same partition at the same time? (which could create a race condition if it sees there's no open order -> creates a document and then we have 2 open orders). I think that I want cross partition query disabled (plus I will not need it).

Answer 1

I am afraid the answer is no. TransactionalBatch only supports write operations. If you want a cross-document read-write transaction as in your case, you only have two options:

1. Do the transaction in a stored procedure. This only works if you are working within a single partition, and you do not need to perform any non-Cosmos-related operations within your transaction. From your description, this should be ok.

2. Implement the transaction on the client side with some kind of locking.

Answer 2

As mentioned in one of the answers, the TransactionBatch only supports write operations. We had faced a similar challenge with Cosmos DB. Here's how you could approach the problem:

• You can introduce a concept of "Session" where Commit/Save operation happens at the end of request/ session.
• Create a local identity map/ cache for all the Get operations. That is, first check if the Cosmos document is present in the local cache, if yes, then return it from local cache. Else, fetch it from Cosmos.
• The Create, Upsert or Delete operation only saves to the local cache and updates the identity map.
• The Commit operation is called at the end of the request. It uses the items in the local cache to create a transaction batch and save it to Cosmos.

The advantage of this approach is that you can do all this in C# without any complex locking code.

A couple of limitations of this approach are:

• It can only work within the single partition (which I think is fine for you)
• Since you are working with ids of Cosmos Document, it can tricky to pass a custom QueryDefinition which does not work with ids.